Reference other worksheet

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Karla V - 30 Apr 2007 15:02 GMT

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!

Reply to this Message

Mike - 30 Apr 2007 15:26 GMT

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

> I have a workbook with 5 worksheets. Sheet 1 is a summary of data on sheets
> 2-5.
[quoted text clipped - 12 lines]
>
> Thanks!

Reply to this Message

dq - 30 Apr 2007 18:23 GMT

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

Reply to this Message

Karla V - 30 Apr 2007 19:02 GMT

Another great solution....thanks!

> Karia,
>
[quoted text clipped - 10 lines]
>
> DQ

Reply to this Message

Karla V - 30 Apr 2007 19:02 GMT

It's almost TOO simple, but works!

Thanks...

> Open new workbook and test this. Enter this into sheet1 Cell A1
> =Sheet2!A1
[quoted text clipped - 17 lines]
> >
> > Thanks!

Reply to this Message