naming

  • Thread starter Thread starter peter
  • Start date Start date
P

peter

Hi,
Is there a way to name cell d21 salary in all sheets in a
workbook so that if you type =salary on any sheet it would
return the value in d21 of that sheet.

more specifically...
=INDIRECT("'[checks" & year & ".XLS]" & blsname &"'!"
& "Salary")

I want to name all sheets d21 in a workbook salary so that
the above formula will return the result based on the
sheet name (blsname).

It works fine if you substitute d21 for salary. But if
you name any cell salary on any sheet, that's the value
returned.

thanks
peter
 
Hi Peter
in the name definition dialog just add the worksheetname to your name.
e.g.
blsname!salary

With this the name is only valid for the specific worksheet. You can
reference the name with =salary in your worksheet

HTH
Frank
 
in the name definition dialog just add the worksheetname to your name.
e.g.
blsname!salary
Click to expand...

Maybe I read it wrong ; that would require a name for every sheet?

???,
Andy
 
Yep !
That's the way it goes.
Sheet1!myname
Sheet2!myname
Sheet3!myname
To make "myname" workSHEET specific, as opposed to just entering a name
(myname) in the name box, which designates the name as workBOOK specific.

There is a sneaky way around this however, if you plan ahead.

*Before* you create your other WSs, create your WS specific name in sheet1,
Sheet1!myname.
Now, simply copy sheet1 and rename it sheet2.

You now have sheet2 with "myname" designated as workSHEET specific for
sheet2.

So, create all your workSHEET specific names *first* in sheet1, and then
simply copy the sheet to populate the rest of your WB, and you can save some
typing.
 
Peter,

Here is a little trick that I discovered a month or so ago. I haven'[t
tested it to death, but it seems to work fine

Define the name Salary as this

=!$D$21

You should then find that whatever sheet you are on, a reference to Salary
will pick up he value of D21 on that sheet.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi Bob
thats a nice trick!. Never know about this -> Excel seems to have some
hidden features :-)
Frank
 
Hi Frank,

It's a great one isn't it? I discovered it when playing about with the
ubiquitous CELL("filename",A1) facility, and announced it to the world in a
discussion with Norman Harker. Here's a link to that thread for anyone
interested http://tinyurl.com/28qsk

I found another little gem the other day. There was a recent discourse on
the advantages of workbook/worksheet names, and I made the comment that you
could use names to store formulae, and use the name in another formula, but
that the big disadvantage was that you could not pass variables to workbook
names, other than the encompassing cell. This thread can also be found here
http://tinyurl.com/3gru8 if interested. I did add '... unless someone knows
better ...', and got no response, but at least 2 people (David Hager and Jan
Karel Pieterse did), and although it is not passing variables in the
accepted sense, it is possible to have a dynamic value depending on some
other input (effectively an IF in the name). It can be found here
http://www.jkp-ads.com/ExcelNames.htm. I need to investigate this further,
but it looks very promising to me.
--

Regards

Bob

Frank Kabel said:
Hi Bob
thats a nice trick!. Never know about this -> Excel seems to have some
hidden features :-)
Frank

Bob said:
Peter,

Here is a little trick that I discovered a month or so ago. I haven'[t
tested it to death, but it seems to work fine

Define the name Salary as this

=!$D$21

You should then find that whatever sheet you are on, a reference to
Salary will pick up he value of D21 on that sheet.
Click to expand...
Click to expand...
 
You're right !

I guess that learning is part of living, and since I ain't dead yet, I'll
continue to learn.

But that is one neat trick.

It sticks like glue and propagates like a virus !

Once you put it on a sheet, *anything* that sheet, or a copy of that sheet
touches, picks it up, as far as I can see.

WAY-TO-GO Bob !
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

Hi RD.
Yep !
That's the way it goes.
Click to expand...

But it doesn't have to ; as Bob has now confirmed.

Rgds,
Andy
 
RD,

Thanks, I got quite excited myself when I found it.

But I am not sure about '... sticks like glue and propagates like a virus
!'<vbg>

Bob
 
...
...
It's a great one isn't it? I discovered it when playing about with the
ubiquitous CELL("filename",A1) facility, and announced it to the world in a
discussion with Norman Harker. Here's a link to that thread for anyone
interested http://tinyurl.com/28qsk
Click to expand...
...

Discovery is a wonderful thing.

http://www.google.com/[email protected]

Also

http://www.google.com/groups?selm=ORiq5icTAHA.284@cppssbbsa03

Very, very little is original in these newsgroups.

FWIW, INDIRECT("A1") and !$A$1 accomplish exactly the same thing in defined
names for pretty much the same reason: the define name process doesn't impose a
filename reference. The advantage to the INDIRECT call is that row 1 or column A
can be deleted or other stuff can be *cut* and pasted into A1, and it'll still
work.
 
Hey Bob,

Something just came up !
Would you verify it for me?

I *can't* get this to auto-calculate.

Original set-up works fine, but when I change the number in the named cell,
it doesn't calculate without a click in the formula bar.
Acts as if I have "AutoCalculate turned off, which of course, I don't.

Naming a sheet specific cell in the normal manner, right next to it, works
as expected.

I'm using XL2k

Maybe I'm doing something "different" ?!?!?
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

RD,

Thanks, I got quite excited myself when I found it.

But I am not sure about '... sticks like glue and propagates like a virus
!'<vbg>

Bob
 
Hi guys remember me...

so maybe i just don't get it.
I have a book (checks), with sheets with peoples names
sam, frank etc. In this book I named a range salary =!
$d$22
In a diff book I enter =[checks.xls]sam!d22 and I get the
value. If I replace the d22 with salary, the formula
changes to =checks.xls!salary and returns #ref. It won't
let me reference a specific sheet.

What am i missing if this is suppose to work?

peter
-----Original Message-----
Hi Peter
in the name definition dialog just add the worksheetname to your name.
e.g.
blsname!salary

With this the name is only valid for the specific worksheet. You can
reference the name with =salary in your worksheet

HTH
Frank
Hi,
Is there a way to name cell d21 salary in all sheets in a
workbook so that if you type =salary on any sheet it would
return the value in d21 of that sheet.

more specifically...
=INDIRECT("'[checks" & year & ".XLS]" & blsname &"'!"
& "Salary")

I want to name all sheets d21 in a workbook salary so that
the above formula will return the result based on the
sheet name (blsname).

It works fine if you substitute d21 for salary. But if
you name any cell salary on any sheet, that's the value
returned.

thanks
peter
Click to expand...


.
Click to expand...
 
Peter,

You didn't mention that you would be referring to it from another workbook.
The name will refer to the cell on the active sheet, but for the workbook
you have included it, it is not defined. If you notice, the statement
=[checks.xls]sam!Salary
changes to
=checks.xls!Salary
as it is a workbook name.

I am afraid I don't think this technique will work from another workbook.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

peter said:
Hi guys remember me...

so maybe i just don't get it.
I have a book (checks), with sheets with peoples names
sam, frank etc. In this book I named a range salary =!
$d$22
In a diff book I enter =[checks.xls]sam!d22 and I get the
value. If I replace the d22 with salary, the formula
changes to =checks.xls!salary and returns #ref. It won't
let me reference a specific sheet.

What am i missing if this is suppose to work?

peter
-----Original Message-----
Hi Peter
in the name definition dialog just add the worksheetname to your name.
e.g.
blsname!salary

With this the name is only valid for the specific worksheet. You can
reference the name with =salary in your worksheet

HTH
Frank
Hi,
Is there a way to name cell d21 salary in all sheets in a
workbook so that if you type =salary on any sheet it would
return the value in d21 of that sheet.

more specifically...
=INDIRECT("'[checks" & year & ".XLS]" & blsname &"'!"
& "Salary")

I want to name all sheets d21 in a workbook salary so that
the above formula will return the result based on the
sheet name (blsname).

It works fine if you substitute d21 for salary. But if
you name any cell salary on any sheet, that's the value
returned.

thanks
peter
Click to expand...


.
Click to expand...
Click to expand...
 
Hi guys remember me...
Click to expand...

What, you think we pay attention to who starts these discusions?
so maybe i just don't get it.
I have a book (checks), with sheets with peoples names
sam, frank etc. In this book I named a range
salary =!$d$22
Click to expand...

Nope. It can't be resolved correctly to that particular worksheet in other
workbooks because worksheet references like this are resolved to the worksheet
containing the cell containing the formula using this reference.
In a diff book I enter =[checks.xls]sam!d22 and I get the
value. If I replace the d22 with salary, the formula
changes to =checks.xls!salary and returns #ref. It won't
let me reference a specific sheet.
Click to expand...

So you have one workbook named checks.xls as well as another the name of which
you haven't yet provided. I'll call this other workbook other.xls. If other.xls
has a worksheets with exactly the same names as those in checks.xls and you want
to pull data from worksheet Sam in checks.xls into worksheet Sam in other.xls,
then you could try defining a name in other.xls (*NOT* in checks.xls) such as
_WSN_ defined as

=MID(CELL("Filename",INDIRECT("A1")),
FIND("]",CELL("Filename",INDIRECT("A1")))+1,32)

Then use the indirect reference in other.xls that look like

=INDIRECT("'[checks.xls]"&_WSN_&"'!"&CELL("Address",D22))

If checks.xls is open, this should give the value in [checks.xls]Sam!D22.

Getting really tricky, if you have a particular worksheet in checks.xls that
could serve as a model for all the other worksheets in it, you could define
workbook-level names in that 'model' worksheet, e.g., defining Salary to refer
to =Sam!$D$22. Then, in other.xls, you could use the following in, say, the
worksheet Chloe to return the value of [checks.xls]Chloe!D22 (salary).

=INDIRECT("'[checks.xls]"&_WSN_&"'!"&CELL("Address",'checks.xls'!Salary))

Then again, you could put the 'model' worksheet and all its defined names in
other.xls, then simplify these references slightly to

=INDIRECT("'[checks.xls]"&_WSN_&"'!"&CELL("Address",Salary))
 
Back
Top