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
...
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
...
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
...
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
...
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
...
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-

It's not working...your equation is changing the "A1" to "A2" etc... when I
want the rows and columns to remain the same and just change the tab/sheet
number. from 001 to 002 to 003 etc... Anymore suggestions?

"Max" wrote:

='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.

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

In your original post, I couldn't tell what your desired cell address was....

HTH,
Bernie
MS Excel MVP


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

Try it! The only thing that changes is the sheet name


--
Regards,

Peo Sjoblom


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

Gord...I couldn't find my old thread, having a little email problems at the
office. Still can't get this equation to work though.

"Gord Dibben" wrote:

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.

Bernie,

I want the information from cells L46 on the other sheets "001", "002",
etc.. to go to column "I" of my first sheet "MIKE"

"Bernie Deitrick" wrote:

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

In your original post, I couldn't tell what your desired cell address was....

HTH,
Bernie
MS Excel MVP


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

I've been trying it with no luck.

=INDIRECT("'"&TEXT(ROWS($A$1:A1),"000")&"'!I46")
changes to
=INDIRECT("'"&TEXT(ROWS($A$1:A2),"000")&"'!I46") for the next line and,
=INDIRECT("'"&TEXT(ROWS($A$1:A3),"000")&"'!I46") and so on...its not
changing th sheet names....001, 002, 003, etc.



"Peo Sjoblom" wrote:

Try it! The only thing that changes is the sheet name


--
Regards,

Peo Sjoblom


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

You don't seem to understand what the formula does, this part

ROWS($A$1:A1)

should change

it's because you want the sheet names to change to

002 (which would be ROWS($A$1:A2))


and so on, it obviously shouldn't return the sheet name but what is in I46

If it doesn't work then it is because you either have spaces in the sheet
names, that you didn't explain in an understandable way what you wanted
(note that all answers are basically the same so I am not the only way
interpreting it this way) or you simply don't understand how to apply it
correctly


--
Regards,

Peo Sjoblom




"MG" wrote in message
...
I've been trying it with no luck.

=INDIRECT("'"&TEXT(ROWS($A$1:A1),"000")&"'!I46")
changes to
=INDIRECT("'"&TEXT(ROWS($A$1:A2),"000")&"'!I46") for the next line and,
=INDIRECT("'"&TEXT(ROWS($A$1:A3),"000")&"'!I46") and so on...its not
changing th sheet names....001, 002, 003, etc.



"Peo Sjoblom" wrote:

Try it! The only thing that changes is the sheet name


--
Regards,

Peo Sjoblom


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

Thanks I got it working now, what it was is that you thought it was I46 I
wanted when it was L46...letter's just got confused when typing lower and
upper cases. Also is there a way when you fill in the equation down the row
to skip a row in between filling. so there's a nice space between information?

"Peo Sjoblom" wrote:

You don't seem to understand what the formula does, this part

ROWS($A$1:A1)

should change

it's because you want the sheet names to change to

002 (which would be ROWS($A$1:A2))


and so on, it obviously shouldn't return the sheet name but what is in I46

If it doesn't work then it is because you either have spaces in the sheet
names, that you didn't explain in an understandable way what you wanted
(note that all answers are basically the same so I am not the only way
interpreting it this way) or you simply don't understand how to apply it
correctly


--
Regards,

Peo Sjoblom




"MG" wrote in message
...
I've been trying it with no luck.

=INDIRECT("'"&TEXT(ROWS($A$1:A1),"000")&"'!I46")
changes to
=INDIRECT("'"&TEXT(ROWS($A$1:A2),"000")&"'!I46") for the next line and,
=INDIRECT("'"&TEXT(ROWS($A$1:A3),"000")&"'!I46") and so on...its not
changing th sheet names....001, 002, 003, etc.



"Peo Sjoblom" wrote:

Try it! The only thing that changes is the sheet name


--
Regards,

Peo Sjoblom


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

BJ, Yeah I have it working now...it was a simple typo...now I'm just trying
to figure out how to fill in the equation and have it skip a row in between
data.

"bj" wrote:

may seem like an odd question but do you have <tools<options<calculations
set to automatic.
if it is set to manual, when you drag the equation down, it will have the
answer from just the first cell in all of the others, until you tell it to
calculate.
the suggestions people have been giving should be working.

"MG" wrote:

Bernie,

I want the information from cells L46 on the other sheets "001", "002",
etc.. to go to column "I" of my first sheet "MIKE"

"Bernie Deitrick" wrote:

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

In your original post, I couldn't tell what your desired cell address was....

HTH,
Bernie
MS Excel MVP


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

may seem like an odd question but do you have <tools<options<calculations
set to automatic.
if it is set to manual, when you drag the equation down, it will have the
answer from just the first cell in all of the others, until you tell it to
calculate.
the suggestions people have been giving should be working.

"MG" wrote:

Bernie,

I want the information from cells L46 on the other sheets "001", "002",
etc.. to go to column "I" of my first sheet "MIKE"

"Bernie Deitrick" wrote:

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

In your original post, I couldn't tell what your desired cell address was....

HTH,
Bernie
MS Excel MVP


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

My suggestion is to not do this. Don't add an empty row.

If you want to make it look pretty, then just increase the rowheight so that it
looks double spaced.

MG wrote:

Peo,

Thanks I got it working now, what it was is that you thought it was I46 I
wanted when it was L46...letter's just got confused when typing lower and
upper cases. Also is there a way when you fill in the equation down the row
to skip a row in between filling. so there's a nice space between information?

"Peo Sjoblom" wrote:

You don't seem to understand what the formula does, this part

ROWS($A$1:A1)

should change

it's because you want the sheet names to change to

002 (which would be ROWS($A$1:A2))


and so on, it obviously shouldn't return the sheet name but what is in I46

If it doesn't work then it is because you either have spaces in the sheet
names, that you didn't explain in an understandable way what you wanted
(note that all answers are basically the same so I am not the only way
interpreting it this way) or you simply don't understand how to apply it
correctly


--
Regards,

Peo Sjoblom




"MG" wrote in message
...
I've been trying it with no luck.

=INDIRECT("'"&TEXT(ROWS($A$1:A1),"000")&"'!I46")
changes to
=INDIRECT("'"&TEXT(ROWS($A$1:A2),"000")&"'!I46") for the next line and,
=INDIRECT("'"&TEXT(ROWS($A$1:A3),"000")&"'!I46") and so on...its not
changing th sheet names....001, 002, 003, etc.



"Peo Sjoblom" wrote:

Try it! The only thing that changes is the sheet name


--
Regards,

Peo Sjoblom


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











--

Dave Peterson

Oops! My bad

See Dave's answer to you design question, I agree 100% with him


--
Regards,

Peo Sjoblom



"MG" wrote in message
...
Peo,

Thanks I got it working now, what it was is that you thought it was I46 I
wanted when it was L46...letter's just got confused when typing lower and
upper cases. Also is there a way when you fill in the equation down the
row
to skip a row in between filling. so there's a nice space between
information?

"Peo Sjoblom" wrote:

You don't seem to understand what the formula does, this part

ROWS($A$1:A1)

should change

it's because you want the sheet names to change to

002 (which would be ROWS($A$1:A2))


and so on, it obviously shouldn't return the sheet name but what is in
I46

If it doesn't work then it is because you either have spaces in the sheet
names, that you didn't explain in an understandable way what you wanted
(note that all answers are basically the same so I am not the only way
interpreting it this way) or you simply don't understand how to apply it
correctly


--
Regards,

Peo Sjoblom




"MG" wrote in message
...
I've been trying it with no luck.

=INDIRECT("'"&TEXT(ROWS($A$1:A1),"000")&"'!I46")
changes to
=INDIRECT("'"&TEXT(ROWS($A$1:A2),"000")&"'!I46") for the next line and,
=INDIRECT("'"&TEXT(ROWS($A$1:A3),"000")&"'!I46") and so on...its not
changing th sheet names....001, 002, 003, etc.



"Peo Sjoblom" wrote:

Try it! The only thing that changes is the sheet name


--
Regards,

Peo Sjoblom


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

MG,

It depends on what you mean by "skip a row in between data"...

Enter this in cell I5:

=INDIRECT(TEXT((ROW()-ROW($I$4)-1)/2 +1,"000") & "!$L$46")

Leave I6 blank.

Then, select I5:I6, and copy.

Select I7 down to I???, enough for 2 rows for each sheet, then paste.

HTH,
Bernie
MS Excel MVP


"MG" wrote in message
...
BJ, Yeah I have it working now...it was a simple typo...now I'm just
trying
to figure out how to fill in the equation and have it skip a row in
between
data.

"bj" wrote:

may seem like an odd question but do you have
<tools<options<calculations
set to automatic.
if it is set to manual, when you drag the equation down, it will have
the
answer from just the first cell in all of the others, until you tell it
to
calculate.
the suggestions people have been giving should be working.

"MG" wrote:

Bernie,

I want the information from cells L46 on the other sheets "001", "002",
etc.. to go to column "I" of my first sheet "MIKE"

"Bernie Deitrick" wrote:

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

In your original post, I couldn't tell what your desired cell address
was....

HTH,
Bernie
MS Excel MVP


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

Well it's not really up to me, is there anyway to do this...or is that
getting too complicated?

"Dave Peterson" wrote:

My suggestion is to not do this. Don't add an empty row.

If you want to make it look pretty, then just increase the rowheight so that it
looks double spaced.

MG wrote:

Peo,

Thanks I got it working now, what it was is that you thought it was I46 I
wanted when it was L46...letter's just got confused when typing lower and
upper cases. Also is there a way when you fill in the equation down the row
to skip a row in between filling. so there's a nice space between information?

"Peo Sjoblom" wrote:

You don't seem to understand what the formula does, this part

ROWS($A$1:A1)

should change

it's because you want the sheet names to change to

002 (which would be ROWS($A$1:A2))


and so on, it obviously shouldn't return the sheet name but what is in I46

If it doesn't work then it is because you either have spaces in the sheet
names, that you didn't explain in an understandable way what you wanted
(note that all answers are basically the same so I am not the only way
interpreting it this way) or you simply don't understand how to apply it
correctly


--
Regards,

Peo Sjoblom




"MG" wrote in message
...
I've been trying it with no luck.

=INDIRECT("'"&TEXT(ROWS($A$1:A1),"000")&"'!I46")
changes to
=INDIRECT("'"&TEXT(ROWS($A$1:A2),"000")&"'!I46") for the next line and,
=INDIRECT("'"&TEXT(ROWS($A$1:A3),"000")&"'!I46") and so on...its not
changing th sheet names....001, 002, 003, etc.



"Peo Sjoblom" wrote:

Try it! The only thing that changes is the sheet name


--
Regards,

Peo Sjoblom


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











--

Dave Peterson

Maybe Peo will know.

MG wrote:

Well it's not really up to me, is there anyway to do this...or is that
getting too complicated?

"Dave Peterson" wrote:

My suggestion is to not do this. Don't add an empty row.

If you want to make it look pretty, then just increase the rowheight so that it
looks double spaced.

MG wrote:

Peo,

Thanks I got it working now, what it was is that you thought it was I46 I
wanted when it was L46...letter's just got confused when typing lower and
upper cases. Also is there a way when you fill in the equation down the row
to skip a row in between filling. so there's a nice space between information?

"Peo Sjoblom" wrote:

You don't seem to understand what the formula does, this part

ROWS($A$1:A1)

should change

it's because you want the sheet names to change to

002 (which would be ROWS($A$1:A2))


and so on, it obviously shouldn't return the sheet name but what is in I46

If it doesn't work then it is because you either have spaces in the sheet
names, that you didn't explain in an understandable way what you wanted
(note that all answers are basically the same so I am not the only way
interpreting it this way) or you simply don't understand how to apply it
correctly


--
Regards,

Peo Sjoblom




"MG" wrote in message
...
I've been trying it with no luck.

=INDIRECT("'"&TEXT(ROWS($A$1:A1),"000")&"'!I46")
changes to
=INDIRECT("'"&TEXT(ROWS($A$1:A2),"000")&"'!I46") for the next line and,
=INDIRECT("'"&TEXT(ROWS($A$1:A3),"000")&"'!I46") and so on...its not
changing th sheet names....001, 002, 003, etc.



"Peo Sjoblom" wrote:

Try it! The only thing that changes is the sheet name


--
Regards,

Peo Sjoblom


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











--

Dave Peterson


--

Dave Peterson

MG,

Here's my reply from a different part of this thread, in case you missed it.....

It depends on what you mean by "skip a row in between data"...

Enter this in cell I5:

=INDIRECT(TEXT((ROW()-ROW($I$4)-1)/2 +1,"000") & "!$L$46")

Leave I6 blank.

Then, select I5:I6, and copy.

Select I7 down to I???, enough for 2 rows for each sheet, then paste.

HTH,
Bernie
MS Excel MVP


"MG" wrote in message
...
Well it's not really up to me, is there anyway to do this...or is that
getting too complicated?

"Dave Peterson" wrote:

My suggestion is to not do this. Don't add an empty row.

If you want to make it look pretty, then just increase the rowheight so that it
looks double spaced.

MG wrote:

Peo,

Thanks I got it working now, what it was is that you thought it was I46 I
wanted when it was L46...letter's just got confused when typing lower and
upper cases. Also is there a way when you fill in the equation down the row
to skip a row in between filling. so there's a nice space between information?

"Peo Sjoblom" wrote:

You don't seem to understand what the formula does, this part

ROWS($A$1:A1)

should change

it's because you want the sheet names to change to

002 (which would be ROWS($A$1:A2))


and so on, it obviously shouldn't return the sheet name but what is in I46

If it doesn't work then it is because you either have spaces in the sheet
names, that you didn't explain in an understandable way what you wanted
(note that all answers are basically the same so I am not the only way
interpreting it this way) or you simply don't understand how to apply it
correctly


--
Regards,

Peo Sjoblom




"MG" wrote in message
...
I've been trying it with no luck.

=INDIRECT("'"&TEXT(ROWS($A$1:A1),"000")&"'!I46")
changes to
=INDIRECT("'"&TEXT(ROWS($A$1:A2),"000")&"'!I46") for the next line and,
=INDIRECT("'"&TEXT(ROWS($A$1:A3),"000")&"'!I46") and so on...its not
changing th sheet names....001, 002, 003, etc.



"Peo Sjoblom" wrote:

Try it! The only thing that changes is the sheet name


--
Regards,

Peo Sjoblom


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











--

Dave Peterson