Excell dragging formula to change sheet/tab not column/row

[Guest]

I'm orgainizing a excel spreadsheet in which I have many sheets/tabs in. I'm
trying to put a formula in on my main page where I can get the data from many
different sheets while keeping my column and row constant on each page. I'm
trying to do this so I can drag this down and it will automatically switch
pages and while keeping column and row constant. Example, my main tab/sheet
is labeled "TODAY" and I want to put information from many different
tabs/sheets in column "I" on the main page. Right now I've got the formula
='001'!$l$46...and when I drag it down I want it to pick up sheet '002'!$l$46
for the next line, '003'!$l$46 for the line after...etc. Is there anyway I
can do that?? If you could help me out I'd greatly appreciate.

 

[Guest]

hi,
personally I seldom use the drag function for formulas but this function
works on cells and not sheets. I know of no where in excel where you can
change this.

 

[T. Valko]

Try it like this:

=INDIRECT(TEXT(ROWS($1:1),"000")&"!I46")

Biff

 

[krcowen]

MG

You will probably have to build your linked formula within an Indirect
function.

If you have in column A, 001, 002, 003, 004, ... you can put in column
B the following formula

=INDIRECT("'"&A1&"'!$a$1")

and copy it down it will return the value from cell A1 on each of the
sheets named 001, 002, 003, ...

Good luck.

Ken
Norfolk, Va

 

[Gord Dibben]

=INDIRECT("00" & (ROW()) & "!$I$46") entered in any column in row 1 and copied
down that column.


Gord Dibben MS Excel MVP