I am working on a rather large inventory workbook. I have a main page which
compilies data from 130 sites. Each one of these sites has it's own sheet
titled with a number ex:1, 2, 3, 4, 5. On the main sheet i would like to
fill data across a columns while keeping the cells "$frozen$" but changing
the tab title which it is pulling from without retyping it in every cell
since it is a rather large worksheet.

EX:
a b c d
Site 1 Site2 site 3 site4

4 '1'$d$2 '2'$d$2
5 '1'$d$3 '2'$d$3
6 '1'$d$4 '3'$d$4

So I would like to fill horizontally and keep the cells absolute but have
the name of the sheet change. My sheets are in numerical order

2 choices. One is to do all the copying and then do a Find/Replace to
change the sheet name. This would be tedious.

Two is to put the sheet names into a cell somewhere, probably near the
top of the column. You can reference that cell using INDIRECT so your
formula is the same all the way across. If the sheet names are in the
2nd row:
=INDIRECT(A$2&"$D$2")

I am working on a rather large inventory workbook.

Depending on how many of these link formulas you will have, this may slow
things down.

Try this entered in A4 then copied across then down as needed:

=INDIRECT("'"&COLUMNS($A4:A4)&"'!D"&ROWS(A$4:A4)+1 )

--
Biff
Microsoft Excel MVP


"Buffulo Alice" wrote in message
...
I am working on a rather large inventory workbook. I have a main page which
compilies data from 130 sites. Each one of these sites has it's own sheet
titled with a number ex:1, 2, 3, 4, 5. On the main sheet i would like to
fill data across a columns while keeping the cells "$frozen$" but changing
the tab title which it is pulling from without retyping it in every cell
since it is a rather large worksheet.

EX:
a b c d
Site 1 Site2 site 3 site4

4 '1'$d$2 '2'$d$2
5 '1'$d$3 '2'$d$3
6 '1'$d$4 '3'$d$4

So I would like to fill horizontally and keep the cells absolute but have
the name of the sheet change. My sheets are in numerical order