CODEWITHSUNDEEP
excel-formula
Kristen D
Kristen D

Reputation: 11

Use cell text to reference sheet name in formula

I am creating a summary page for daily production reports. All sheets are in one workbook. Daily sheets are named 17-Oct, 16-Oct, 15-Oct, etc with a Monthly Summary sheet. The summary sheet contains a table with the first column being dates in the same date format as the tabs. I want to use the date in the first column of the summary worksheet to reference the tab with the same name and pull data from it. I have been trying to figure out a way to use the Indirect function to do this but nothing I've tried works. I don't want to have to manually edit the formula in each row to reference the corresponding sheet. I've seen similar questions but the solutions are not working for me.

Upvotes: 1

Views: 898

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

Dates are numbers in Excel shown as the format dictates and sheet names are strings.

You will need to format the number as text in the INDIRECT:

=INDIRECT("'" & TEXT(A1,"dd-mmm") & "'!A1")

Upvotes: 1

Related Questions