Referencing tabs

C

CENorth7272

I have a workbook that has 5 daily tabs (Mon. - Fri.) and some summar
pages. On one of the summary pages I have a table that lists the day
of the week along the left and a total column on the right.

I would like to make a function that will lookup the day on the lef
and use that reference as the tab name in a sum function in the tota
column.

Any/All help is appreciated.

-Cla
 
B

Bob Phillips

Clay,

something like

=SUM(INDIRECT("'"&A1&"'!A1:A100")

where A1 holds that sheet number

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
M

Mark Graesser

Clay
You can use the INDIRECT function for this. The foloowing formula will return the value in cell A1 from whatever sheet name you enter in I29

=INDIRECT("'"&I29&"'!A1"

So for your SUM you could use something like

=SUM(INDIRECT("'"&I29&"'!A1:A20")

Good Luck
Mark Graesse
[email protected]


----- CENorth7272 > wrote: ----

I have a workbook that has 5 daily tabs (Mon. - Fri.) and some summar
pages. On one of the summary pages I have a table that lists the day
of the week along the left and a total column on the right.

I would like to make a function that will lookup the day on the lef
and use that reference as the tab name in a sum function in the tota
column

Any/All help is appreciated

-Cla
 
C

CENorth7272

Thanks guys,

I had accomplished what I was looking to do but your way was easier an
more simple

-Cla
 
M

Mark Graesser

I always go for simple. I'm not to good with complex

Thanks for the feedback
Mark Graesse

----- CENorth7272 > wrote: ----

Thanks guys

I had accomplished what I was looking to do but your way was easier an
more simpl

-Cla
 
C

CENorth7272

Is it possible to paste the above 'Indirect' functions so that the rang
that it references will change.

For example, on the summary page, column 2 is referencing a range i
column A on one of the daily pages, but column 3 is referencing a rang
in column B on one of the daily pages.

I would rather not go through each formula and change the A's to B's.

Looking for a better solution please

-Cla
 
Top