Building A Formula: Selecting Cell Address As Absolute?

[(PeteCresswell)]

When building a formula as in "=F1*F2", sometimes an absolute
address is needed - as in "F1*$F$2".

Is there a modifier key or something that I can hold down when
selecting a cell into a formula so that it comes in as an
absolute address (e.g. $F$2)?

 

[Clif McIrvin]

When building a formula as in "=F1*F2", sometimes an absolute
address is needed - as in "F1*$F$2".

Is there a modifier key or something that I can hold down when
selecting a cell into a formula so that it comes in as an
absolute address (e.g. $F$2)?

F4 toggles through the various address formats availavle when the cell
address is selected.

--
Clif McIrvin

Change nomail.afraid.org to gmail.com to reply by email.
(nomail.afraid.org has been set up specifically for
use in usenet. Feel free to use it yourself.)

 

[(PeteCresswell)]

Per Clif McIrvin:

F4 toggles through the various address formats availavle when the cell
address is selected.

Thanks.

Also, I stumbled on to something that at least *seems* like good
practice: assign a range name to the cell in question by typing
it in to the area to the left of "fx". Once that's done, the
range name will appear when selecting the cell for inclusion.

Makes formulas more verbose, but cuts down on the "magic numbers"
and arcane references.

e.g.

Instead of:
=(($F$7*$G$7*$B$2)/1000)*$B$1

One winds up with:
=((TvRackDraw*TvRackTimerHours*DaysPerYear)/1000)*KwHrCost

 

[Clif McIrvin]

Per Clif McIrvin:

Thanks.

Also, I stumbled on to something that at least *seems* like good
practice: assign a range name to the cell in question by typing
it in to the area to the left of "fx". Once that's done, the
range name will appear when selecting the cell for inclusion.

Makes formulas more verbose, but cuts down on the "magic numbers"
and arcane references.

e.g.

Instead of:
=(($F$7*$G$7*$B$2)/1000)*$B$1

One winds up with:
=((TvRackDraw*TvRackTimerHours*DaysPerYear)/1000)*KwHrCost

Yes, I've used that myself. Back in 2003, you could also define names
via the Edit | Names submenu (I think it was Edit ... don't have 2003
available anymore) ... I havn't looked for that in 2010 yet.

I believe I've read that in 2010 you can assign a formula directly to a
named label ... I can think of times I'd have loved to be able to do
that!

One issue I run into (I inherited many workbooks with "row data" [for
want of a better term] in sheets instead of rows) ... is that named
ranges become ambiguous as soon as I add the second sheet.

--
Clif McIrvin

Change nomail.afraid.org to gmail.com to reply by email.
(nomail.afraid.org has been set up specifically for
use in usenet. Feel free to use it yourself.)

 

[norie]

As far as I know in 2010 you can just select a range, right click and
select Define name...

On the Formula tab you'll find the Name Manager... which is the
equivalent of Insert>Name... in earlier versions.

There's also other options available.

Also I think the ability to assign formulas to names (or is that the
other way round) has always been available.

Yep, I just created a named range called PI2 with the formula =PI()*2
in the refers to box on 2000 and 2010.