Formula's in linking spreadsheets

[Janie]

I am doing a spreadsheet that has twenty tabs and one master (where I collect
all the data from the info entered into each tab). I need to copy the first
formula on the master down the column to reflect the data in each tab. I
have set up the formula that keeps the cell constant, but can't get the tab
number to change when copied. i.e., Tab1, c12; Tab 2, C12, Tab 3, C12, etc.
Every time I copy the link down the column, tab 1 stays as tab 1. What to
do?

 

[Chip Pearson]

You can build up a text string any way you want and then use INDIRECT
to convert it to an actual range reference. For example, if your
worksheets are named Sheet1, Sheet2 and so on, enter the following in,
say, G5 and copy down as far as you need to go.

=INDIRECT("Sheet"&ROW()-ROW($G$5)+1&"!A1")

Change the $G$5 to the first cell in which this formula appears, and
change the A1 to the cell on each worksheet whose value you want to
retrieve.

If your sheets are not so logically named, first create a list of your
worksheets starting in, say, H10. and enter the following formula in
the first cell where the returned list is to begin and copy down as
far as you need to go. Watch carefully the combinations of single
quotes and double quotes.

=INDIRECT("'"&H10&"'!A1")

Both formulas work by using the & concatenate operator with cell
references to build a text string that is a syntactically correct cell
address. INDIRECT then takes that text string and converts it to an
actual range reference and returns the value of that range. In
general, you can use an INDIRECT function anywhere you would normally
use a cell address.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com