You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Camera Dude Author

Level 1

4 points

Linking a row of cells from one sheet to another by typing in a value

Hi all,

I'm working on a TV series, and have sheets for each episode breaking down the requirements for the art department across a number of columns.

These scenes can be shot in any order.

What I'm looking to do is to have a shooting order sheet where I can type "7.01" into the scene number column, and the data will be linked from my episode 7 sheet.

I've been manually linking cells using "=", but hoping to improve on this process.

Episode breakdown:

Schedule order breakdown:

So ideally I'd type in "1.04" and all the data from the epsiode 1, scene 4, columns would be linked.

Posted on Nov 22, 2020 12:43 AM

Top-ranking reply

SGIII

Level 8

36,152 points

Posted on Nov 22, 2020 1:30 AM

I would get rid of all merged cells (which can cause mysterious errors in formulas) and combine all the separate Episode tables on different sheets into one Scenes table.

Then you can do something like this:

In B2, filled or copied right and down:

=IFERROR(INDEX(Scenes::C,MATCH($A2,Scenes::$B,0)),"")

The IFERROR is cosmetic, to suppress the red warning triangles when the cell in column A is blank (i.e., you haven't entered a scene number).

The INDEX MATCH combination "looks up" the corresponding values in the Scenes table, depending on what you typed in column A.

When you want to see all the scenes in an episode all you need to do is filter on column A in the Scenes table.

View in context

Linking a row of cells from one sheet to another by typing in a value

Similar questions