Create a list of data from same cell but different sheets

E

Eddie P

I'm creating a list of data found in the same cell on 12 different
worksheets. Is there an easy way to do this rather than write 12 separate
formulas?
 
M

Miguel Zapico

You can use INDIRECT, with the name of the worksheet in a separate cell. For
example, if the cells with the worksheet names are in A1 to A12, and you want
to retrieve the cell C3 from all those worksheet, this formula on B1 (copied
all the way to B12) will do the trick:
=INDIRECT(A1 & "!C3")

Hope this helps,
Miguel.
 
B

Biff

Hi!

If your sheet names are completely unique (Sales,Prod,Supply), you'll need
separate formulas. (or, list the sheet names but that is no better than just
using separate formulas if you only have 12 to do)

B1 = sheet name = Sales
B2 = sheet name = Prod
B3 = sheet name = Supply

=INDIRECT(B1&"!A1")

Then copy down. This would be equivalent to:

=Sales!A1
=Prod!A1
=Supply!A1

If your sheet names have a common identifier with a sequence like the
default sheet names (Sheet1,Sheet2,Sheet3), then you can use a single
formula that when copied increments:

=INDIRECT("sheet"&ROWS($1:1)&"!A1")
=INDIRECT("sheet"&ROWS($1:2)&"!A1")
=INDIRECT("sheet"&ROWS($1:3)&"!A1")

This is equivalent to:

=Sheet1!A1
=Sheet2!A1
=Sheet3!A1

Biff
 
Top