Copying columns to additional sheets

Rachael Stammers

Good Afternoon,

I have a sheet where information is submitted using a form. When a row is added, a unique identification number is attached to that row. I have separate sheets that are used for the investigation into the incidents/hazards reported in the form/rows. I would like some of the columns to be automatically copied across into those additional sheets (please don't suggest ctrl C!!!!!!). I could use the option of 'link cell from another sheet', but would really like it to be automated. Does anyone know if this is possible? if it is, what formula can I use?

Kimberly Loveless

Hi @Rachael Stammers

The only thing I could think of that might work is to use an index/match column formula, but some information would need to be manually updated. If you are already putting in that unique ID for example you could pull in the other column data that way and you would only have to set up the formula once for each column. It would look something like...

=INDEX({Column 1 to be pulled in},MATCH([Unique ID]@row,{Unique ID on source sheet},0))

Rachael Stammers

Hi @Kimberly Loveless

Thank you for your help. I have tried to write the formula but it comes back as UNPARSEABLE. This is what I wrote

=INDEX({Aviation Safety Reports_NEW Range 1}, MATCH("ASR", {Aviation Safety Reports_NEW Range 1}, 1}

Where:

Aviation Safety Reports_NEW is the sheet with the ID number in that I want to copy across to teh other sheet.

ASR is the prefix for the Unique ID/ASR ID No.

Kimberly Loveless

Ok so 3 things with your formula. First is the last bracket should be a parenthesis. Second you will want to use the column heading instead of "ASR" as you want to match the value in that cell. Lastly it looks like you are using the same range for the match and the return value, these would need to be seperate ranges.

=INDEX({Aviation Safety Reports_NEW Range 1}, MATCH([ASR ID No.]@row, {Aviation Safety Reports_NEW Range 2}, 1)

Where...

Aviation Safety Reports_NEW Range 1 = the column of the value you would like to pull into the sheet (based on above this would be the summary column in the other sheet to pull it to the summary in this sheet)

Aviation Safety Reports_NEW Range 2 = The ASR ID No. in the other sheet

Rachael Stammers

Hi @Kimberly Loveless , Thank you again for your help.

I'm still struggling with the formula. I have a primary sheet where information is submitted via a form. Then I have three additional sheets where some of the information is repeated in columns in those additional sheets.

Using your last formula example I have this =INDEX({Aviation Safety Reports_NEW Range 1}, MATCH("ASR ID No.", {Aviation Safety Reports_NEW Range 2}, 1)

Where:

Aviation Safety Reports_NEW = the primary sheet

Range 1 = the column in which the information I want to copy to the other sheets is contained

ASR ID No. = the info I want copied across

Aviation Safety Reports_NEW Range 2 = the name of the column in the parent sheet.

However, I'm getting the #No MATCH error code, but there are some reports submitted in the parent form. Which makes me think it's finding something but not reporting back to copy it across...?