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!

Find more posts tagged with

Accepted answers

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.