Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Excel cell address from cell contents
After years with Quattro Pro, I'm diving into Excel 2007. One conversion
problem involves INDIRECT as a substitute for QPro's @@ function. Suppose a workbook with a SUMMARY sheet (tab) and a 1989 sheet In SUMMARY, cell A4 has the value 1989, and cell A41 is a string from A4's value (QPro) or just a value reference (=a4) in Excel In B41 I want the value in cell C18 of sheet 1989 In Qpro I have: @@(cell("contents",$A41)&":$c$18") In Excel I have: =INDIRECT(CELL("contents",$A41)&"!"&"$c$18) Question: Is this the most efficient way in Excel to get the results I want in B41? I want to be able to enter any sheet name (e.g. 1989) in A4, and have B41 read c18 of sheet 1989. I hope I have been clear. Also, following this same example, suppose the source cell I want is not always c18. Can I name a cell in each sheet (1989, 1990 ...) with the same name, and then use it in the formula put in B17 of the SUMMARY? I don't think so. Thanks for any advice, as I use this sort of reference in a lot of my workbooks. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Excel cell address from cell contents
Maybe...
=indirect("'"&a41&"'!c18") And yes, you can use a formula like: ='1989'!myCell ='1990'!myCell I named the cell on each sheet "myCell" Insert|Names|define (in xl2003 menus) Names in Workbook: '1990'!myCell refers to: ='1990'!$A$1 You'll want to make sure that you use a local/worksheet level name (not global/workbook level). TQuestar wrote: After years with Quattro Pro, I'm diving into Excel 2007. One conversion problem involves INDIRECT as a substitute for QPro's @@ function. Suppose a workbook with a SUMMARY sheet (tab) and a 1989 sheet In SUMMARY, cell A4 has the value 1989, and cell A41 is a string from A4's value (QPro) or just a value reference (=a4) in Excel In B41 I want the value in cell C18 of sheet 1989 In Qpro I have: @@(cell("contents",$A41)&":$c$18") In Excel I have: =INDIRECT(CELL("contents",$A41)&"!"&"$c$18) Question: Is this the most efficient way in Excel to get the results I want in B41? I want to be able to enter any sheet name (e.g. 1989) in A4, and have B41 read c18 of sheet 1989. I hope I have been clear. Also, following this same example, suppose the source cell I want is not always c18. Can I name a cell in each sheet (1989, 1990 ...) with the same name, and then use it in the formula put in B17 of the SUMMARY? I don't think so. Thanks for any advice, as I use this sort of reference in a lot of my workbooks. -- Dave Peterson |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Excel cell address from cell contents
You'll want to make sure that you use a local/worksheet level name (not
global/workbook level). And to do that, you specify it in the "Names in Workbook" textbox. Dave Peterson wrote: Maybe... =indirect("'"&a41&"'!c18") And yes, you can use a formula like: ='1989'!myCell ='1990'!myCell I named the cell on each sheet "myCell" Insert|Names|define (in xl2003 menus) Names in Workbook: '1990'!myCell refers to: ='1990'!$A$1 You'll want to make sure that you use a local/worksheet level name (not global/workbook level). TQuestar wrote: After years with Quattro Pro, I'm diving into Excel 2007. One conversion problem involves INDIRECT as a substitute for QPro's @@ function. Suppose a workbook with a SUMMARY sheet (tab) and a 1989 sheet In SUMMARY, cell A4 has the value 1989, and cell A41 is a string from A4's value (QPro) or just a value reference (=a4) in Excel In B41 I want the value in cell C18 of sheet 1989 In Qpro I have: @@(cell("contents",$A41)&":$c$18") In Excel I have: =INDIRECT(CELL("contents",$A41)&"!"&"$c$18) Question: Is this the most efficient way in Excel to get the results I want in B41? I want to be able to enter any sheet name (e.g. 1989) in A4, and have B41 read c18 of sheet 1989. I hope I have been clear. Also, following this same example, suppose the source cell I want is not always c18. Can I name a cell in each sheet (1989, 1990 ...) with the same name, and then use it in the formula put in B17 of the SUMMARY? I don't think so. Thanks for any advice, as I use this sort of reference in a lot of my workbooks. -- Dave Peterson -- Dave Peterson |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Excel cell address from cell contents
Thanks for your quick response, Dave.
"Dave Peterson" wrote: Maybe... =indirect("'"&a41&"'!c18") It appears that the single quotes can be eliminated, leaving this: =indirect(""&a41&"!c18") This works, but I'm unclear why the outer (i.e. 1st and last) quotes are required. This is certainly an improvement on my method! Thanks. And yes, you can use a formula like: ='1989'!myCell ='1990'!myCell I named the cell on each sheet "myCell" Insert|Names|define (in xl2003 menus) Names in Workbook: '1990'!myCell refers to: ='1990'!$A$1 You'll want to make sure that you use a local/worksheet level name (not global/workbook level). Perfect. Thanks for the clear explanation of the local vs global name use. I was only aware of global names before reading this. Finally, a minor question. All the workbooks that I have exported from Quattro Pro x4 into xls files lack grid lines when opened in Excel 2007, and when I go View Show/Hide Gridlines, the gridline box is checked. I can save the file in Excel into a newly named xls or xlsx file, but still no gridlines. Do you know a workaround? TQ PS-I really appreciate your help, and though I'm going to be away for a few days, you may be sure that I will read any response with interest and thanks. TQuestar wrote: After years with Quattro Pro, I'm diving into Excel 2007. One conversion problem involves INDIRECT as a substitute for QPro's @@ function. Suppose a workbook with a SUMMARY sheet (tab) and a 1989 sheet In SUMMARY, cell A4 has the value 1989, and cell A41 is a string from A4's value (QPro) or just a value reference (=a4) in Excel In B41 I want the value in cell C18 of sheet 1989 In Qpro I have: @@(cell("contents",$A41)&":$c$18") In Excel I have: =INDIRECT(CELL("contents",$A41)&"!"&"$c$18) Question: Is this the most efficient way in Excel to get the results I want in B41? I want to be able to enter any sheet name (e.g. 1989) in A4, and have B41 read c18 of sheet 1989. I hope I have been clear. Also, following this same example, suppose the source cell I want is not always c18. Can I name a cell in each sheet (1989, 1990 ...) with the same name, and then use it in the formula put in B17 of the SUMMARY? I don't think so. Thanks for any advice, as I use this sort of reference in a lot of my workbooks. -- Dave Peterson . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return cell address of a cell based on contents of cell. | Excel Worksheet Functions | |||
Comparing cell contents, know cell address | Excel Discussion (Misc queries) | |||
linking to contents of a cell vs cell address | Excel Worksheet Functions | |||
Insert Cell Contents in middle of web address | Excel Discussion (Misc queries) | |||
How do I use cell contents as an address in a formula | Excel Worksheet Functions |