Indirect? Autofill? Can't make anything work.

[Grunty]

My workbook has one summary sheet, 26 data sheets.

In the summary I need to see the contents of V7 on all 26 data sheets.
So I want to repeat this formula, where 1, 2, 3, etc are the sheet references:

='1'!$V$7
='2'!$V$7
='3'!$V$7
etc

How can I copy the formula 26 times and have the sheet reference automatically progress?

[jeffreybrown]

How about...

=INDIRECT("'"&ROW(A1)&"'!V7")

...copied down

[TMS]

=INDIRECT("'" & ROW(A1) & "'!$V$7")

And drag down


Regards, TMS

[Grunty]

Hee hee hee! Yahoooooo! Chuga chuga chuga! Woop woop woop!

[Grunty]

All the other samples I've seen of the Indirect formula had information between the quotation marks. That must have been what was messing it up. Thanks a lot.

[jeffreybrown]

chitty chitty bang bang

You are very welcome.

We are glad to have helped and it seems one of us had to be right or at least close, what do you think Trevor.

Please mark as solved if so...

[TMS]

I can't think of any appropriate literary references, but, yes, one of us had to be close.

Note that you don't need $V$7 as it is a text string and will not change, hence Jeff's simple V7 will give exactly the same result

Regards, TMS

[jeffreybrown]

@Grunty,

I often turn here when I'm struggling with Indirect.