I have a workbook with 5 worksheets. Sheet 1 is a summary of data on sheets
2-5.

Sheet 1 will always be named "summary", but the names of sheets 2-5 will
change monthly.

How can I "generically" reference sheets 2-5 so I don't have to change the
formulas on my summary sheet each month?

In other words, if the name of sheet 2 is "FLX_75" this month and next month
it is "FLX_79", how do I write the formula to pull data from the correct
worksheet without having to edit it each month. The formula will always
reference the same CELLS on these worksheets, so I only need the changing
worksheet.

Thanks!

Open new workbook and test this. Enter this into sheet1 Cell A1
=Sheet2!A1
Then on sheet2 Cell A1 put data then change sheet name and go back to sheet1
Cell A1 and notice formula has changed to the new name of sheet2

"Karla V" wrote:

I have a workbook with 5 worksheets. Sheet 1 is a summary of data on sheets
2-5.

Sheet 1 will always be named "summary", but the names of sheets 2-5 will
change monthly.

How can I "generically" reference sheets 2-5 so I don't have to change the
formulas on my summary sheet each month?

In other words, if the name of sheet 2 is "FLX_75" this month and next month
it is "FLX_79", how do I write the formula to pull data from the correct
worksheet without having to edit it each month. The formula will always
reference the same CELLS on these worksheets, so I only need the changing
worksheet.

Thanks!

Karia,

Try this:
=INDIRECT(A1&"!B7")
This will give you the contents of cell B7 of the sheet mentioned in
cell A1 of the current sheet.
So if A1 is FLX_79, you get the same result as when you write =FLX_79!
B7
Or instead of entering the name in a cell, you could create a defined
name (Insert/Name/Define) like
="FLX_79" and name this 'Detail1' then your formula would be
=INDIRECT(Detail1&"!B7")

DQ

Another great solution....thanks!

"dq" wrote:

Karia,

Try this:
=INDIRECT(A1&"!B7")
This will give you the contents of cell B7 of the sheet mentioned in
cell A1 of the current sheet.
So if A1 is FLX_79, you get the same result as when you write =FLX_79!
B7
Or instead of entering the name in a cell, you could create a defined
name (Insert/Name/Define) like
="FLX_79" and name this 'Detail1' then your formula would be
=INDIRECT(Detail1&"!B7")

DQ

It's almost TOO simple, but works!

Thanks...


"Mike" wrote:

Open new workbook and test this. Enter this into sheet1 Cell A1
=Sheet2!A1
Then on sheet2 Cell A1 put data then change sheet name and go back to sheet1
Cell A1 and notice formula has changed to the new name of sheet2

"Karla V" wrote:

I have a workbook with 5 worksheets. Sheet 1 is a summary of data on sheets
2-5.

Sheet 1 will always be named "summary", but the names of sheets 2-5 will
change monthly.

How can I "generically" reference sheets 2-5 so I don't have to change the
formulas on my summary sheet each month?

In other words, if the name of sheet 2 is "FLX_75" this month and next month
it is "FLX_79", how do I write the formula to pull data from the correct
worksheet without having to edit it each month. The formula will always
reference the same CELLS on these worksheets, so I only need the changing
worksheet.

Thanks!