Indirect function

[Guest]

I have a workbook containing 52 worksheets - one for each day of the year. At the end of the workbook I also have a Totals worksheet, adding up figures from the previous 52 sheets. I have a formula on the Totals sheet that picks up a figure in cell E8 off Week 1 worksheet, however, I want to be able to copy the formula down so it picks up the same cell on Week 1-52. I have made the necessary cell absolute, however, when I copy the formula down, it looks at the same cell because worksheet names are not relative.

I have tried to use the Indirect function but am struggling. Can anybody offer any more help?

Thank you.

Louise

 

[Bob Phillips]

TRy somethin g like

=INDIRECT("Week"&ROW()&"!E8")

which requires the sheets to be named Week1, Weedk2, etc, and your summary
in rows 1,2,3. Adjsut to fit.

--

HTH

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

I have a workbook containing 52 worksheets - one for each day of the year.

At the end of the workbook I also have a Totals worksheet, adding up figures
from the previous 52 sheets. I have a formula on the Totals sheet that
picks up a figure in cell E8 off Week 1 worksheet, however, I want to be
able to copy the formula down so it picks up the same cell on Week 1-52. I
have made the necessary cell absolute, however, when I copy the formula
down, it looks at the same cell because worksheet names are not relative.

 

[Andy B]

Hi

Try something like:
=INDIRECT("Week"&ROW(1:1)&"!$E$8")

--
Andy.

I have a workbook containing 52 worksheets - one for each day of the year.

At the end of the workbook I also have a Totals worksheet, adding up figures
from the previous 52 sheets. I have a formula on the Totals sheet that
picks up a figure in cell E8 off Week 1 worksheet, however, I want to be
able to copy the formula down so it picks up the same cell on Week 1-52. I
have made the necessary cell absolute, however, when I copy the formula
down, it looks at the same cell because worksheet names are not relative.

 

[Guest]

Hi Andy

thanks for your quick response, however, I'm still having difficulty. In your formula, you have entered row(1:1). Is this the number of the row that the data is coming from on the other worksheets???

Thanks again

Louise

 

[Andy B]

No. That is purely a value that returns a 1 - and it will increment as you
drag it down.

--
Andy.

Hi Andy

thanks for your quick response, however, I'm still having difficulty. In

your formula, you have entered row(1:1). Is this the number of the row that
the data is coming from on the other worksheets???

 

[Andy B]

OK

Send it to [email protected] without the 999 bit!

--
Andy.

Don't know whether I'm having a 'blonde moment' but I can't get it to

work. I know it's something I'm doing it wrong but i don't know what.
Would it be possible for me to send you a little attachment [not all 52
worksheets], showing you what I'm trying to do?

 

[Nigel]

(e-mail address removed)>,
(e-mail address removed) says...

Hi Andy

thanks for your quick response, however, I'm still having difficulty.

In your formula, you have entered row(1:1). Is this the
number of the row that the data is coming from on the other
worksheets???

Thanks again

Louise

Hi Louise
I had the same problem and was offered the same solutions.
A much better one if you are brave is to write a function.
I used one from J Walkenbach's excellent book "Excel 2000
formulas" which is designed to solve this exact problem.

Let me know if you want more info.
Cheers