Formula using worksheet names 2

[E]

My original question dates from 31st July, and was helpfully answered.
Unfortunately I am now stuck on a similar problem, which I thought would be
easier. I now have sheets called Person a, Person b, . . . with figures I
want to pull through to a summary sheet. This time I have a table with Person
a, Person b, . . . in a column, so I just want to say ='sheet(A3)' cell(B10)
where A3 is 'Person a' and B10 is the cell figure I want to pull through. I'm
getting tangled up with inverted commas. Please help!

 

[John C]

On your summary sheet, assuming A2 through whatever is Person A, Person B, etc.
B2: =INDIRECT("'"&A2&"'!B10")
inside the parentheses, enter as follows
"
'
"
&
A2
"
'
!
B10
"

Hope this helps.

 

[E]

Fab, thank you. Another question, though - I want to copy the formula down so
that from the sheet it gives B10, B11, . . . Normally copying down would do
this automatically, but using INDIRECT I get B10, B10, . . . etc.

 

[Pete_UK]

That's because the B10 is within quotes, so is treated as text. You
can put it outside the quotes like this:

=INDIRECT("'"&A2&"'!B"&ROW(A10))

Hope this helps.

Pete

 

[E]

Thank you, that works really well.

That's because the B10 is within quotes, so is treated as text. You
can put it outside the quotes like this:

=INDIRECT("'"&A2&"'!B"&ROW(A10))

Hope this helps.

Pete

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete