=OFFSET(!$A$1,,,,) - global range naming

[Epinn]

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

 

[Bob Phillips]

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)

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

 

[Epinn]

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 insert>name>define 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.

Do you remember one situation?

Thanks.

Epinn

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)

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

 

[Ragdyer]

<<<"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 !
---------------------------------------------------------------------------


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
insert>name>define 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.

Do you remember one situation?

Thanks.

Epinn

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)

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