How do I create a column formula to pull values from a particular row in other sheets?

malden

I've been playing with MATCH and VLOOKUP formulas, but I can't figure it out.

My target sheet contains (among other things) 3 columns:

• The SHEET NAMES of the sheets that I want to pull from
• START DATE (blank, want to auto-fill) for a particular task within the source sheet
• FINISH DATE (blank, want to auto-fill) for a particular task within the source sheet

For example:

• Sheet1Name | Start Date | Finish Date
• Sheet2Name | Start Date | Finish Date

How do I make column formulas for these date columns to reference the sheet that is named, search for the task name within that source sheet, and auto-fill the start and finish date for that task?

The task has the same name in all the source sheets.

Thank you!

James Keuning

You want to use MATCH and INDEX. The MATCH has to work first. Can you get the MATCH to work? You will know it is working because it will return a row number. Once that is working you use INDEX to grab the Start from that row number and the Finish from that row.

malden

@James Keuning Thanks for your response.

EDIT: MATCH is working, but INDEX still isn't working. Within a source sheet (just for testing), I have:

=INDEX([Start Date]:[Start Date], MATCH("Task A", [Task Column]:[Task Column], 0))

Result is #INVALID COLUMN VALUE

The formula works for other columns, but not [Start Date] or [Finish Date].

And, once I can get this INDEX formula working, I still need to know how to modify it as a column formula on the target sheet so that it will look up these values from sheets in the [Sheet Name] column there - rather than one sheet individually at a time.

malden

Nevermind. I found a different workaround.