Find cell reference and return sheet name from another workbook

**Aimee Blackford** · 05-15-2019, 05:59 AM

I have Workbook 1 & Workbook 2
Workbook 1 contains dates 01/01/2019 to 01/05/2019. I have a dropdown to select the date which populates data into the sheet from various workbooks
Workbook 2 has one tab per month (Jan, Feb, Mar, April & May) and within each month has the dates

I want to lookup the date from workbook 1 and return the sheet name from workbook 2 (I have another formula to then find data within the specific worksheet from workbook 2)

my other workbooks are simply to link as they are single sheets but this one I am struggling with as I need to pull the sheet name for the rest of my formula to find the data I need within that sheet

**Special-K** · 05-15-2019, 06:47 AM

So you have a date from Workbook 1 and want to return the 3 letter month name which will be the tab name of the sheet you want to search in Workbook 2 ?

Just use

=TEXT(A1,"mmm")

where A1 is the date

and then use it in an INDIRECT() formula

**Aimee Blackford** · 05-15-2019, 08:46 AM

How would I then use this in Indirect to continue my search into that specific sheet of workbook 2?

thanks

**Special-K** · 05-15-2019, 09:48 AM

You have a date, 01/01/2019
So it must relate to January.

=TEXT(A1,"mmm")

returns Jan
so to return the data in Jan!C3
you'd put

=INDIRECT(TEXT(A1,"mmm")&"!C3")

to get the value of cell C3 in workbook 1 sheet "Jan"

Depends what you're doing, specific data examples would help.

Post a sample spreadsheet with expected results, remove any sensitive data, create a mockup example if necessary.
Use the Go Advanced option at the bottom of the page then scroll down to Manage Attachments as the "paperclip" method does not work on this forum.