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.

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.


"MG" wrote:

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.

Try it like this:

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

Biff

"MG" wrote in message
...
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.

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





On Jun 18, 4:36 pm, FSt1 wrote:
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.



"MG" wrote:
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.- Hide quoted text -

- Show quoted text -

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


Gord Dibben MS Excel MVP


On Mon, 18 Jun 2007 12:52:26 -0700, MG wrote:

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.