If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
How can I change the tab but keep the row and column constant
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. |
#12
|
|||
|
|||
How can I change the tab but keep the row and column constant
=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 news 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. |
#13
|
|||
|
|||
How can I change the tab but keep the row and column constant
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 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. |
#14
|
|||
|
|||
How can I change the tab but keep the row and column constant
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. |
#15
|
|||
|
|||
How can I change the tab but keep the row and column constant
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 news 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. |
#16
|
|||
|
|||
How can I change the tab but keep the row and column constant
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 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. |
#17
|
|||
|
|||
How can I change the tab but keep the row and column constant
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 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. |
#18
|
|||
|
|||
How can I change the tab but keep the row and column constant
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 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. |
#19
|
|||
|
|||
How can I change the tab but keep the row and column constant
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 toolsoptionscalculations 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 news 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. |
#20
|
|||
|
|||
How can I change the tab but keep the row and column constant
may seem like an odd question but do you have toolsoptionscalculations
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 news 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. |
Thread Tools | |
Display Modes | |
|
|