Links

[TLD]

We have data on 31 separate worksheets in one file. This
data needs to be summarized on one worksheet. The data
is located in the same cell on each worksheet, C104. I
can get the cell reference to remain the same ('$C$104)
without a problem. How do I get the worksheet reference
to update without having to go in and change each formula
with the next worksheet number when I copy the cell to the
next row in the data column? Once we have that squared
away we need to inport data from another workbook, once
again 31 worksheets, to correspond to the data we have
summarized within the initial owrkbook? Can someone
help??

thanks!

 

[Debra Dalgleish]

If you have a list of the worksheets on the Summary sheet, you can use
the Indirect function to return the value on each sheet. For example,
with the first sheet name in cell C4, enter the following formula in
cell C5:

=INDIRECT("'" & C4 & "'!$C$104")

Copy the formula down to the last sheet name