How can I change the tab but keep the row and column constant when

How can I change the tab but keep the row and column constant when filling in
equations?

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 roc constant. Example, my main
tab/sheet is labeled "MIKE" 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 someone could help me out I'd greatly appreciate.

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


copied down will do what you want


--
Regards,

Peo Sjoblom



"MG" wrote in message
news
How can I change the tab but keep the row and column constant when filling
in
equations?

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 roc constant. Example, my main
tab/sheet is labeled "MIKE" 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 someone could help me out I'd greatly appreciate.

='001'!$I$46...and when I drag it down I want it to pick up sheet
'002'!$I$46
for the next line, '003'!$I$46 for the line after...etc.

One way ..

Use: =INDIRECT("'00"&ROW(A1)&"'!I46")
and copy down
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"MG" wrote:
How can I change the tab but keep the row and column constant when filling in
equations?

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 roc constant. Example, my main
tab/sheet is labeled "MIKE" 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 someone could help me out I'd greatly appreciate.

Mike?,

If your sheet tabs are always three digit numbers, you could use, for example, this formula, entered
into a cell I5:

=INDIRECT(TEXT(ROW()-ROW($I$4),"000") & "!$I$46")

Note that the cell reference (in this case $I$4) needs to be from the row just above the first
instance of the formula.

HTH,
Bernie
MS Excel MVP


"MG" wrote in message
news
How can I change the tab but keep the row and column constant when filling in
equations?

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 roc constant. Example, my main
tab/sheet is labeled "MIKE" 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 someone could help me out I'd greatly appreciate.

Note that it will not work for more than 9 sheets if sheet 10 is named 010


--
Regards,

Peo Sjoblom


"Max" wrote in message
...
='001'!$I$46...and when I drag it down I want it to pick up sheet
'002'!$I$46
for the next line, '003'!$I$46 for the line after...etc.

One way ..

Use: =INDIRECT("'00"&ROW(A1)&"'!I46")
and copy down
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"MG" wrote:
How can I change the tab but keep the row and column constant when
filling in
equations?

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 roc constant. Example, my main
tab/sheet is labeled "MIKE" 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 someone could help me out I'd greatly appreciate.

Yes, that's right. It was careless of me. Pl dismiss it.
Go with Peo's better rendition.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Peo Sjoblom" wrote in message
...
Note that it will not work for more than 9 sheets if sheet 10 is named 010

THanks for all your suggestions...I'm going to try these later today and let
you all know the outcome. Thanks again.

-Mike

"Bernie Deitrick" wrote:

Mike?,

If your sheet tabs are always three digit numbers, you could use, for example, this formula, entered
into a cell I5:

=INDIRECT(TEXT(ROW()-ROW($I$4),"000") & "!$I$46")

Note that the cell reference (in this case $I$4) needs to be from the row just above the first
instance of the formula.

HTH,
Bernie
MS Excel MVP


"MG" wrote in message
news
How can I change the tab but keep the row and column constant when filling in
equations?

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 roc constant. Example, my main
tab/sheet is labeled "MIKE" 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 someone could help me out I'd greatly appreciate.

Max

I posted the same answer yesterday to the same OP and also didn't think about
more than 10 sheets.

Wonder if OP ran into a problem and re-posted rather than staying in the same
thread?


Gord

On Tue, 19 Jun 2007 23:43:51 +0800, "Max" wrote:

Yes, that's right. It was careless of me. Pl dismiss it.
Go with Peo's better rendition.

Didn't work. I have numerous tabs "001", "002", "003" etc and on each one of
them in column L and Row 46 I have information I want to transfer to column I
on my main page "Mike". When I fill in the equation down I want the row and
column part to remain and change the page number each time. Any other ideas?

"Bernie Deitrick" wrote:

Mike?,

If your sheet tabs are always three digit numbers, you could use, for example, this formula, entered
into a cell I5:

=INDIRECT(TEXT(ROW()-ROW($I$4),"000") & "!$I$46")

Note that the cell reference (in this case $I$4) needs to be from the row just above the first
instance of the formula.

HTH,
Bernie
MS Excel MVP


"MG" wrote in message
news
How can I change the tab but keep the row and column constant when filling in
equations?

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 roc constant. Example, my main
tab/sheet is labeled "MIKE" 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 someone could help me out I'd greatly appreciate.

Peo.

Didn't work. I have numerous tabs "001", "002", "003" etc and on each one of
them in column L and Row 46 I have information I want to transfer to column I
on my main page "Mike". When I fill in the equation down I want the row and
column part to remain and change the page number each time. Any other ideas?

"Peo Sjoblom" wrote:

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


copied down will do what you want


--
Regards,

Peo Sjoblom



"MG" wrote in message
news
How can I change the tab but keep the row and column constant when filling
in
equations?

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 roc constant. Example, my main
tab/sheet is labeled "MIKE" 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 someone could help me out I'd greatly appreciate.