| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to do it in excel?
Does anyone have any suggestions on how to do following task in excel?
I get a workbook with 10 working sheets, which is named by A, B, C, D, E, F, G, H, Summary, Temp and I would like to display all cells' content in a specific sheet into "Temp" sheet based on the sheet name in "Summary" sheet. For example, I type "A" in cell A1 under "Summary" sheet, then in "Temp" sheet, all cell's content will connect to "A" sheet and display what the cells' content under "A" sheet, if I input "E" in cell A1 under "Summary" sheet, then in Temp" sheet, all cell's content will connect to "E" sheet and display what the cells' content under "E" sheet. Does anyone have any suggestions on how to do it? Thanks in advance for any suggestions Eric |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to do it in excel?
One way:
=INDIRECT(Summary!A1 & "!J10") In article , Eric wrote: Does anyone have any suggestions on how to do following task in excel? I get a workbook with 10 working sheets, which is named by A, B, C, D, E, F, G, H, Summary, Temp and I would like to display all cells' content in a specific sheet into "Temp" sheet based on the sheet name in "Summary" sheet. For example, I type "A" in cell A1 under "Summary" sheet, then in "Temp" sheet, all cell's content will connect to "A" sheet and display what the cells' content under "A" sheet, if I input "E" in cell A1 under "Summary" sheet, then in Temp" sheet, all cell's content will connect to "E" sheet and display what the cells' content under "E" sheet. Does anyone have any suggestions on how to do it? Thanks in advance for any suggestions Eric |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to do it in excel?
Thank you for your suggestions
=INDIRECT(Summary!$A$1 & "!J10") How to modify "!J10" into a variable? If I drag and drop this cell, all cell locations will refer to J10 only. I would like to drag and drop this cell across a table A1:Z1000, therefore, In cell D1, the formula will be =INDIRECT(Summary!$A$1 & "!D1") In cell H12, the formula will be =INDIRECT(Summary!$A$1 & "!H12") Do you have any suggestions? Thank you for any suggestions Eric "JE McGimpsey" wrote: One way: =INDIRECT(Summary!A1 & "!J10") In article , Eric wrote: Does anyone have any suggestions on how to do following task in excel? I get a workbook with 10 working sheets, which is named by A, B, C, D, E, F, G, H, Summary, Temp and I would like to display all cells' content in a specific sheet into "Temp" sheet based on the sheet name in "Summary" sheet. For example, I type "A" in cell A1 under "Summary" sheet, then in "Temp" sheet, all cell's content will connect to "A" sheet and display what the cells' content under "A" sheet, if I input "E" in cell A1 under "Summary" sheet, then in Temp" sheet, all cell's content will connect to "E" sheet and display what the cells' content under "E" sheet. Does anyone have any suggestions on how to do it? Thanks in advance for any suggestions Eric |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to do it in excel?
Eric,
You can modify JE McGimpsey's formula as follows: =INDIRECT(Summary!$A$1 & "!" & ADDRESS(ROW(),COLUMN())) To avoid getting "0" whenever there is a blank cell on the original sheet, use this: =IF(ISBLANK(INDIRECT(Summary!$A$1 & "!" & ADDRESS(ROW(),COLUMN()))),"",INDIRECT(Summary!$A$1 & "!" & ADDRESS(ROW(),COLUMN()))) Either of these might be cumbersome to recalculate over 26,000 cells, however. - David Eric wrote: Thank you for your suggestions =INDIRECT(Summary!$A$1 & "!J10") How to modify "!J10" into a variable? If I drag and drop this cell, all cell locations will refer to J10 only. I would like to drag and drop this cell across a table A1:Z1000, therefore, In cell D1, the formula will be =INDIRECT(Summary!$A$1 & "!D1") In cell H12, the formula will be =INDIRECT(Summary!$A$1 & "!H12") Do you have any suggestions? Thank you for any suggestions Eric "JE McGimpsey" wrote: One way: =INDIRECT(Summary!A1 & "!J10") In article , Eric wrote: Does anyone have any suggestions on how to do following task in excel? I get a workbook with 10 working sheets, which is named by A, B, C, D, E, F, G, H, Summary, Temp and I would like to display all cells' content in a specific sheet into "Temp" sheet based on the sheet name in "Summary" sheet. For example, I type "A" in cell A1 under "Summary" sheet, then in "Temp" sheet, all cell's content will connect to "A" sheet and display what the cells' content under "A" sheet, if I input "E" in cell A1 under "Summary" sheet, then in Temp" sheet, all cell's content will connect to "E" sheet and display what the cells' content under "E" sheet. Does anyone have any suggestions on how to do it? Thanks in advance for any suggestions Eric |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to do it in excel?
Another way which might be easier to understand when copied across/down
since it uses a cell reference -- Regards, Peo Sjoblom "David Hilberg" wrote in message news:lcVFi.5320$eh3.2743@trndny07... Eric, You can modify JE McGimpsey's formula as follows: =INDIRECT(Summary!$A$1 & "!" & ADDRESS(ROW(),COLUMN())) To avoid getting "0" whenever there is a blank cell on the original sheet, use this: =IF(ISBLANK(INDIRECT(Summary!$A$1 & "!" & ADDRESS(ROW(),COLUMN()))),"",INDIRECT(Summary!$A$1 & "!" & ADDRESS(ROW(),COLUMN()))) Either of these might be cumbersome to recalculate over 26,000 cells, however. - David Eric wrote: Thank you for your suggestions =INDIRECT(Summary!$A$1 & "!J10") How to modify "!J10" into a variable? If I drag and drop this cell, all cell locations will refer to J10 only. I would like to drag and drop this cell across a table A1:Z1000, therefore, In cell D1, the formula will be =INDIRECT(Summary!$A$1 & "!D1") In cell H12, the formula will be =INDIRECT(Summary!$A$1 & "!H12") Do you have any suggestions? Thank you for any suggestions Eric "JE McGimpsey" wrote: One way: =INDIRECT(Summary!A1 & "!J10") In article , Eric wrote: Does anyone have any suggestions on how to do following task in excel? I get a workbook with 10 working sheets, which is named by A, B, C, D, E, F, G, H, Summary, Temp and I would like to display all cells' content in a specific sheet into "Temp" sheet based on the sheet name in "Summary" sheet. For example, I type "A" in cell A1 under "Summary" sheet, then in "Temp" sheet, all cell's content will connect to "A" sheet and display what the cells' content under "A" sheet, if I input "E" in cell A1 under "Summary" sheet, then in Temp" sheet, all cell's content will connect to "E" sheet and display what the cells' content under "E" sheet. Does anyone have any suggestions on how to do it? Thanks in advance for any suggestions Eric |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to do it in excel?
Oops!
=INDIRECT(Summary!$A$1&"!"&CELL("address",A1)) -- Regards, Peo Sjoblom "Peo Sjoblom" wrote in message ... Another way which might be easier to understand when copied across/down since it uses a cell reference -- Regards, Peo Sjoblom "David Hilberg" wrote in message news:lcVFi.5320$eh3.2743@trndny07... Eric, You can modify JE McGimpsey's formula as follows: =INDIRECT(Summary!$A$1 & "!" & ADDRESS(ROW(),COLUMN())) To avoid getting "0" whenever there is a blank cell on the original sheet, use this: =IF(ISBLANK(INDIRECT(Summary!$A$1 & "!" & ADDRESS(ROW(),COLUMN()))),"",INDIRECT(Summary!$A$1 & "!" & ADDRESS(ROW(),COLUMN()))) Either of these might be cumbersome to recalculate over 26,000 cells, however. - David Eric wrote: Thank you for your suggestions =INDIRECT(Summary!$A$1 & "!J10") How to modify "!J10" into a variable? If I drag and drop this cell, all cell locations will refer to J10 only. I would like to drag and drop this cell across a table A1:Z1000, therefore, In cell D1, the formula will be =INDIRECT(Summary!$A$1 & "!D1") In cell H12, the formula will be =INDIRECT(Summary!$A$1 & "!H12") Do you have any suggestions? Thank you for any suggestions Eric "JE McGimpsey" wrote: One way: =INDIRECT(Summary!A1 & "!J10") In article , Eric wrote: Does anyone have any suggestions on how to do following task in excel? I get a workbook with 10 working sheets, which is named by A, B, C, D, E, F, G, H, Summary, Temp and I would like to display all cells' content in a specific sheet into "Temp" sheet based on the sheet name in "Summary" sheet. For example, I type "A" in cell A1 under "Summary" sheet, then in "Temp" sheet, all cell's content will connect to "A" sheet and display what the cells' content under "A" sheet, if I input "E" in cell A1 under "Summary" sheet, then in Temp" sheet, all cell's content will connect to "E" sheet and display what the cells' content under "E" sheet. Does anyone have any suggestions on how to do it? Thanks in advance for any suggestions Eric |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to do it in excel?
One way:
Create a defined name (Insert/Name/Define), say Name in workbook: MyVariableRange Refers to: =INDIRECT(Summary!$A$1 & "!A1:Z1000") Then in your worksheet, enter A1: =INDEX(MyVariableRange,ROW(),COLUMN()) Drag to A1:Z1000. In article , Eric wrote: How to modify "!J10" into a variable? If I drag and drop this cell, all cell locations will refer to J10 only. I would like to drag and drop this cell across a table A1:Z1000, therefore, In cell D1, the formula will be =INDIRECT(Summary!$A$1 & "!D1") In cell H12, the formula will be =INDIRECT(Summary!$A$1 & "!H12") |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to do it in excel?
Thank everyone very much for suggestions
=INDIRECT(Summary!$A$1&"!"&CELL("address",A1)) Under Summary sheet, if "A" is in cell a1, then it works, what if the content becomes "A 2007", then there is an error to locate the address, do you have any suggestions on modify the above formula to accept the string "A 2007" in cell A1 under Summary sheet? Thank everyone for any suggestions Eric "Peo Sjoblom" wrote: Oops! =INDIRECT(Summary!$A$1&"!"&CELL("address",A1)) -- Regards, Peo Sjoblom "Peo Sjoblom" wrote in message ... Another way which might be easier to understand when copied across/down since it uses a cell reference -- Regards, Peo Sjoblom "David Hilberg" wrote in message news:lcVFi.5320$eh3.2743@trndny07... Eric, You can modify JE McGimpsey's formula as follows: =INDIRECT(Summary!$A$1 & "!" & ADDRESS(ROW(),COLUMN())) To avoid getting "0" whenever there is a blank cell on the original sheet, use this: =IF(ISBLANK(INDIRECT(Summary!$A$1 & "!" & ADDRESS(ROW(),COLUMN()))),"",INDIRECT(Summary!$A$1 & "!" & ADDRESS(ROW(),COLUMN()))) Either of these might be cumbersome to recalculate over 26,000 cells, however. - David Eric wrote: Thank you for your suggestions =INDIRECT(Summary!$A$1 & "!J10") How to modify "!J10" into a variable? If I drag and drop this cell, all cell locations will refer to J10 only. I would like to drag and drop this cell across a table A1:Z1000, therefore, In cell D1, the formula will be =INDIRECT(Summary!$A$1 & "!D1") In cell H12, the formula will be =INDIRECT(Summary!$A$1 & "!H12") Do you have any suggestions? Thank you for any suggestions Eric "JE McGimpsey" wrote: One way: =INDIRECT(Summary!A1 & "!J10") In article , Eric wrote: Does anyone have any suggestions on how to do following task in excel? I get a workbook with 10 working sheets, which is named by A, B, C, D, E, F, G, H, Summary, Temp and I would like to display all cells' content in a specific sheet into "Temp" sheet based on the sheet name in "Summary" sheet. For example, I type "A" in cell A1 under "Summary" sheet, then in "Temp" sheet, all cell's content will connect to "A" sheet and display what the cells' content under "A" sheet, if I input "E" in cell A1 under "Summary" sheet, then in Temp" sheet, all cell's content will connect to "E" sheet and display what the cells' content under "E" sheet. Does anyone have any suggestions on how to do it? Thanks in advance for any suggestions Eric |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to do it in excel?
=INDIRECT("'"&Summary!$A$1&"'!"&CELL("address",A1) )
(wrapped the worksheet name (what ever is in summary!$a$1) inside apostrophes.) Eric wrote: Thank everyone very much for suggestions =INDIRECT(Summary!$A$1&"!"&CELL("address",A1)) Under Summary sheet, if "A" is in cell a1, then it works, what if the content becomes "A 2007", then there is an error to locate the address, do you have any suggestions on modify the above formula to accept the string "A 2007" in cell A1 under Summary sheet? Thank everyone for any suggestions Eric "Peo Sjoblom" wrote: Oops! =INDIRECT(Summary!$A$1&"!"&CELL("address",A1)) -- Regards, Peo Sjoblom "Peo Sjoblom" wrote in message ... Another way which might be easier to understand when copied across/down since it uses a cell reference -- Regards, Peo Sjoblom "David Hilberg" wrote in message news:lcVFi.5320$eh3.2743@trndny07... Eric, You can modify JE McGimpsey's formula as follows: =INDIRECT(Summary!$A$1 & "!" & ADDRESS(ROW(),COLUMN())) To avoid getting "0" whenever there is a blank cell on the original sheet, use this: =IF(ISBLANK(INDIRECT(Summary!$A$1 & "!" & ADDRESS(ROW(),COLUMN()))),"",INDIRECT(Summary!$A$1 & "!" & ADDRESS(ROW(),COLUMN()))) Either of these might be cumbersome to recalculate over 26,000 cells, however. - David Eric wrote: Thank you for your suggestions =INDIRECT(Summary!$A$1 & "!J10") How to modify "!J10" into a variable? If I drag and drop this cell, all cell locations will refer to J10 only. I would like to drag and drop this cell across a table A1:Z1000, therefore, In cell D1, the formula will be =INDIRECT(Summary!$A$1 & "!D1") In cell H12, the formula will be =INDIRECT(Summary!$A$1 & "!H12") Do you have any suggestions? Thank you for any suggestions Eric "JE McGimpsey" wrote: One way: =INDIRECT(Summary!A1 & "!J10") In article , Eric wrote: Does anyone have any suggestions on how to do following task in excel? I get a workbook with 10 working sheets, which is named by A, B, C, D, E, F, G, H, Summary, Temp and I would like to display all cells' content in a specific sheet into "Temp" sheet based on the sheet name in "Summary" sheet. For example, I type "A" in cell A1 under "Summary" sheet, then in "Temp" sheet, all cell's content will connect to "A" sheet and display what the cells' content under "A" sheet, if I input "E" in cell A1 under "Summary" sheet, then in Temp" sheet, all cell's content will connect to "E" sheet and display what the cells' content under "E" sheet. Does anyone have any suggestions on how to do it? Thanks in advance for any suggestions Eric -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to do it in excel?
Thank everyone very much for suggesitons
Eric "Dave Peterson" wrote: =INDIRECT("'"&Summary!$A$1&"'!"&CELL("address",A1) ) (wrapped the worksheet name (what ever is in summary!$a$1) inside apostrophes.) Eric wrote: Thank everyone very much for suggestions =INDIRECT(Summary!$A$1&"!"&CELL("address",A1)) Under Summary sheet, if "A" is in cell a1, then it works, what if the content becomes "A 2007", then there is an error to locate the address, do you have any suggestions on modify the above formula to accept the string "A 2007" in cell A1 under Summary sheet? Thank everyone for any suggestions Eric "Peo Sjoblom" wrote: Oops! =INDIRECT(Summary!$A$1&"!"&CELL("address",A1)) -- Regards, Peo Sjoblom "Peo Sjoblom" wrote in message ... Another way which might be easier to understand when copied across/down since it uses a cell reference -- Regards, Peo Sjoblom "David Hilberg" wrote in message news:lcVFi.5320$eh3.2743@trndny07... Eric, You can modify JE McGimpsey's formula as follows: =INDIRECT(Summary!$A$1 & "!" & ADDRESS(ROW(),COLUMN())) To avoid getting "0" whenever there is a blank cell on the original sheet, use this: =IF(ISBLANK(INDIRECT(Summary!$A$1 & "!" & ADDRESS(ROW(),COLUMN()))),"",INDIRECT(Summary!$A$1 & "!" & ADDRESS(ROW(),COLUMN()))) Either of these might be cumbersome to recalculate over 26,000 cells, however. - David Eric wrote: Thank you for your suggestions =INDIRECT(Summary!$A$1 & "!J10") How to modify "!J10" into a variable? If I drag and drop this cell, all cell locations will refer to J10 only. I would like to drag and drop this cell across a table A1:Z1000, therefore, In cell D1, the formula will be =INDIRECT(Summary!$A$1 & "!D1") In cell H12, the formula will be =INDIRECT(Summary!$A$1 & "!H12") Do you have any suggestions? Thank you for any suggestions Eric "JE McGimpsey" wrote: One way: =INDIRECT(Summary!A1 & "!J10") In article , Eric wrote: Does anyone have any suggestions on how to do following task in excel? I get a workbook with 10 working sheets, which is named by A, B, C, D, E, F, G, H, Summary, Temp and I would like to display all cells' content in a specific sheet into "Temp" sheet based on the sheet name in "Summary" sheet. For example, I type "A" in cell A1 under "Summary" sheet, then in "Temp" sheet, all cell's content will connect to "A" sheet and display what the cells' content under "A" sheet, if I input "E" in cell A1 under "Summary" sheet, then in Temp" sheet, all cell's content will connect to "E" sheet and display what the cells' content under "E" sheet. Does anyone have any suggestions on how to do it? Thanks in advance for any suggestions Eric -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
