Worksheet name / reference as a formula?

[Guest]

Hello.

I have a Workbook where there are many worksheets; the name of each is a
date, i.e. "1-21", "1-22", "1-23", etc. I have a summary sheet that
centralizes data based on the individual worksheets into a usable form. Every
day I add another worksheet with a new date. This system does not need to be
scalable as I will only be doing this for 6 months. On the summary sheet, I
have VLOOKUP equations that reference to worksheets pulling data for each
date so that the same piece of data may be compared over time in a column.
The problem is, every time I add another worksheet, I have to manually change
the VLOOKUP formula to reference the newly minted sheet.

For instance, here is my formula:

=VLOOKUP(B$3,'1-21'!$E$2:$I$31,5,FALSE)

'1-21' is the worksheet. When I add a sheet for '1-22' I will have to copy
this formula and then manually change the worksheet name.

What I would like to do is change the worksheet name to a formula, so that I
don’t have to manually change it each time.

However, I can’t get this to work at even the most basic level, which would
be to have a cell with “text†formatting with the text “1-22†and then
reference to it from within my VLOOKUP formula.

If that would work, I would have the relative worksheet formula access the
date column which labels the date each row represents so that instead of
something like this:

'1-21'

I would have something like this:

'MONTH(A15)&"-"&DAY(A15)’

That equation produces the text “1-21†and adjusts to the relevant date.

Unfortunately, I can’t get the worksheet name to be relative in any way.

Is there any way to turn the worksheet name into a formula, so that it may
be altered?

Is anything like this possible?

Thank you.

Using:
Excel 2003

 

[Biff]

Hi!

Enter the sheet name in a cell:

A1 = 1-22 (or whatever)

=VLOOKUP(B$3,INDIRECT("'"&A1&"'!E2:I31"),5,0)

Biff

 

[Arvi Laanemets]

Hi

Additionally, you can use an UDF to get a list of sheet names to summary
sheet, like
Public Function TabI(TabIndex As Integer, Optional MyTime As Date) As String
' returns the name of n-th sheet in workbook,
' use TODAY() or NOW() as optional parameter
' to make the function to recalculate automatically
TabI = Sheets(TabIndex).Name
End Function

Copy the code into workbook's module. On your master sheet, p.e. into cell
A4 enter the formula:
=IF(ISERROR(TABI(ROW()-2,TODAY())),"",TABI(ROW()))
(I assume the Summary sheet is the first one, i.e. the leftmost tab)
, and copy the formula down for enough rows to get the list of all sheets
Now into cell B4 you can enter the formula
=IF(A4="","",VLOOKUP(B$3,INDIRECT("'"&A4&"'!E2:I31"),5,0))
, and copy it down too - it's done! When you prepared enough rows, whenever
you add a new sheet to your workbook, and keep summary sheet as 1st one,
your summary sheet is updated automatically.

 

[Guest]

Thanks so much for your help!

This worked beautifully!

I ended up using:

VLOOKUP(B$3,INDIRECT("'"&MONTH($A42)&"-"&DAY($A42)&"'!$E$2:$I$32"),5,FALSE)

A42 has the appropriate date for the relevant row.

This will save me a ton of work, thanks again!

 

[Guest]

Arvi,

I did not get a chance to try your suggestion as I implemented Bill's
solution first and that solved my issue.

I appreciate your help!

Thanks!