How do I create a column formula to pull values from a particular row in other sheets?
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!
Answers
-
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.
-
@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.
-
Nevermind. I found a different workaround.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!