Multisheet formula

T

Todd Nelson

How can I pull a particular cell on each sheet onto a completely separate
worksheet. ex. Sheet1c4, sheet2c4, without having to type each particular
sheet name in?
 
B

Bob Phillips

In the target cell, type =, then click the other tab and then cell in that
sheet, and Excel fills it in for you.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
D

Domenic

Assuming that you want to list your values in Column B, starting at B2,
try...

B2, copied down:

=INDIRECT("'Sheet"&ROWS($B$2:B2)&"'!C4")

Hope this helps!
 
B

Bob Phillips

Then you can use INDIRECT.

Put the sheet name in B, cell in C, and use

=INDIRECT("'"&B2&"'!"&C2)

and copy down

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
R

RagDyer

Domenic,

What's the perceived advantage of using the longer reference:

ROWS($B$2:B2)

As opposed to using

ROW(B2)
OR
ROW(2:2)
?
 
D

Domenic

For robustness. If a row is inserted before the formula, the correct
results will still be returned.

Hope this helps!
 
R

RagDyer

I checked that out before asking, and all perform *exactly* the same,
whether rows are inserted before, after, or in the middle.

The *only* difference I now found, is if the insertion takes place *before*
Row1.

So, to clarify, the robustness you refer to is that it withstands an
insertion before Row1.

That's good to know.
 
D

Domenic

RagDyer said:
So, to clarify, the robustness you refer to is that it withstands an
insertion before Row1.
Click to expand...

Yes, that's it. It can withstand an insertion of any number of rows at
any point before the row containing the formula. It was a poor choice
of words on my part. :)
 
Top