Reference other worksheet

[Guest]

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!

 

[Guest]

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

 

[dq]

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

 

[Guest]

Another great solution....thanks!

 

[Guest]

It's almost TOO simple, but works!

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