Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=OFFSET(!$A$1,,,,) - global range naming
I found the following excerpt from an e-letter published in 1999.
************************************************** ****************** Created by Bob Umlas and David Hager To define a global range name in Excel, go to Insert, Name, Define and, as an example, in the Names in Workbook box type "cellA1" and in the Refers To box type this formula: =OFFSET(!$A$1,,,,). Now, type =cellA1 in a cell on any worksheet in the workbook and it will return the value in A1 for that worksheet. ************************************************** ****************** Dave P., I didn't search for this. It just stared at me and I couldn't resist. I am not sure if I *truly* understand the above formula. You know, sometimes things look simple and yet it can turn out to be quite complicated. I wonder if it will cause the same problems that !$A$1 will. If anyone remembers the details of the problems, please share with us. Can someone convince me why I want to go thru all this instead of just keying in =A1? :) If I have more than just A1, but a range instead (e.g.A1:A10), how do I adjust the above formula to include :A10? Where do I put the commas? Why do we need them? Less than eight characters? I hope this is not outdated. If it is, please let me know. Thanks. Epinn |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=OFFSET(!$A$1,,,,) - global range naming
It is creating a global name, but one that will automatically point at the
actrivesheet. When you normally create a name, and type say =A1 in the Refersto box, Excel adds the sheet name (=Sheet1!A1). So if you are on Sheet1 and use that name, it will pick up the value in A1 on Sheet1, if you are on Sheet2, it will still pick up the value in A1 on Sheet1. If you create a name and in the RefersTo box as =!A1, Excel will not add the sheet name.S o if you are on Sheet1 and use that name, it will pick up the value in A1 on Sheet1, if you are on Sheet2, it will now pick up the value in A1 on Sheet2. It is a flawed method though in some situations. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Epinn" wrote in message ... I found the following excerpt from an e-letter published in 1999. ************************************************** ****************** Created by Bob Umlas and David Hager To define a global range name in Excel, go to Insert, Name, Define and, as an example, in the Names in Workbook box type "cellA1" and in the Refers To box type this formula: =OFFSET(!$A$1,,,,). Now, type =cellA1 in a cell on any worksheet in the workbook and it will return the value in A1 for that worksheet. ************************************************** ****************** Dave P., I didn't search for this. It just stared at me and I couldn't resist. I am not sure if I *truly* understand the above formula. You know, sometimes things look simple and yet it can turn out to be quite complicated. I wonder if it will cause the same problems that !$A$1 will. If anyone remembers the details of the problems, please share with us. Can someone convince me why I want to go thru all this instead of just keying in =A1? :) If I have more than just A1, but a range instead (e.g.A1:A10), how do I adjust the above formula to include :A10? Where do I put the commas? Why do we need them? Less than eight characters? I hope this is not outdated. If it is, please let me know. Thanks. Epinn |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=OFFSET(!$A$1,,,,) - global range naming
Bob,
I understand everything you said as I had a good lesson the other day. I have to apologize for not being clear. What I meant was NOT to use insertnamedefine but just key "=A1" directly into a cell of the appropriate sheet. I can think of one reason for naming a cell: To make the formula more descriptive. Do we really need to include OFFSET since no row or column is specified? I understand the commas now. The joke is on me. It is a flawed method though in some situations. Do you remember one situation? Thanks. Epinn "Bob Phillips" wrote in message ... It is creating a global name, but one that will automatically point at the actrivesheet. When you normally create a name, and type say =A1 in the Refersto box, Excel adds the sheet name (=Sheet1!A1). So if you are on Sheet1 and use that name, it will pick up the value in A1 on Sheet1, if you are on Sheet2, it will still pick up the value in A1 on Sheet1. If you create a name and in the RefersTo box as =!A1, Excel will not add the sheet name.S o if you are on Sheet1 and use that name, it will pick up the value in A1 on Sheet1, if you are on Sheet2, it will now pick up the value in A1 on Sheet2. It is a flawed method though in some situations. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Epinn" wrote in message ... I found the following excerpt from an e-letter published in 1999. ************************************************** ****************** Created by Bob Umlas and David Hager To define a global range name in Excel, go to Insert, Name, Define and, as an example, in the Names in Workbook box type "cellA1" and in the Refers To box type this formula: =OFFSET(!$A$1,,,,). Now, type =cellA1 in a cell on any worksheet in the workbook and it will return the value in A1 for that worksheet. ************************************************** ****************** Dave P., I didn't search for this. It just stared at me and I couldn't resist. I am not sure if I *truly* understand the above formula. You know, sometimes things look simple and yet it can turn out to be quite complicated. I wonder if it will cause the same problems that !$A$1 will. If anyone remembers the details of the problems, please share with us. Can someone convince me why I want to go thru all this instead of just keying in =A1? :) If I have more than just A1, but a range instead (e.g.A1:A10), how do I adjust the above formula to include :A10? Where do I put the commas? Why do we need them? Less than eight characters? I hope this is not outdated. If it is, please let me know. Thanks. Epinn |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=OFFSET(!$A$1,,,,) - global range naming
<<<"Do you remember one situation?"
The main purpose of " =!A1 ", was to create a WS specific name, and to *globally* (concurrently) assign it to *all* the existing sheets in the WB. The shortcoming of this naming technique is that it *doesn't* recalculate. Changing the value in the named cell does *not* force a formula containing this name to auto-recalculate. It makes it appear as if the sheet is set to manual calculation, where the formula containing cell must be individually re-entered in the formula bar. A naming procedure that *does* work to globally assign WS specific names to *all* existing sheets is: =INDIRECT("A1") -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Epinn" wrote in message ... Bob, I understand everything you said as I had a good lesson the other day. I have to apologize for not being clear. What I meant was NOT to use insertnamedefine but just key "=A1" directly into a cell of the appropriate sheet. I can think of one reason for naming a cell: To make the formula more descriptive. Do we really need to include OFFSET since no row or column is specified? I understand the commas now. The joke is on me. It is a flawed method though in some situations. Do you remember one situation? Thanks. Epinn "Bob Phillips" wrote in message ... It is creating a global name, but one that will automatically point at the actrivesheet. When you normally create a name, and type say =A1 in the Refersto box, Excel adds the sheet name (=Sheet1!A1). So if you are on Sheet1 and use that name, it will pick up the value in A1 on Sheet1, if you are on Sheet2, it will still pick up the value in A1 on Sheet1. If you create a name and in the RefersTo box as =!A1, Excel will not add the sheet name.S o if you are on Sheet1 and use that name, it will pick up the value in A1 on Sheet1, if you are on Sheet2, it will now pick up the value in A1 on Sheet2. It is a flawed method though in some situations. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Epinn" wrote in message ... I found the following excerpt from an e-letter published in 1999. ************************************************** ****************** Created by Bob Umlas and David Hager To define a global range name in Excel, go to Insert, Name, Define and, as an example, in the Names in Workbook box type "cellA1" and in the Refers To box type this formula: =OFFSET(!$A$1,,,,). Now, type =cellA1 in a cell on any worksheet in the workbook and it will return the value in A1 for that worksheet. ************************************************** ****************** Dave P., I didn't search for this. It just stared at me and I couldn't resist. I am not sure if I *truly* understand the above formula. You know, sometimes things look simple and yet it can turn out to be quite complicated. I wonder if it will cause the same problems that !$A$1 will. If anyone remembers the details of the problems, please share with us. Can someone convince me why I want to go thru all this instead of just keying in =A1? :) If I have more than just A1, but a range instead (e.g.A1:A10), how do I adjust the above formula to include :A10? Where do I put the commas? Why do we need them? Less than eight characters? I hope this is not outdated. If it is, please let me know. Thanks. Epinn |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Duplicate Range Names by worksheet | Excel Discussion (Misc queries) | |||
How do you Identify text as a named range in excel | Excel Discussion (Misc queries) | |||
VLookup Error in Part of a Named Range | Excel Worksheet Functions | |||
Help with using range names in sum function | Excel Worksheet Functions | |||
Array to named range conversion... | Excel Discussion (Misc queries) |