parenthesis on reference cause failure

  • Thread starter Thread starter Robert H
  • Start date Start date
R

Robert H

Im using VBA to insert the following formula
=IF(ADDRESS(5,COLUMN(Type))="HL",$F$25,$F$22)

the probelm is that the reference created by ADDRESS(5,COLUMN(Type))
returns the correct reference but in parenthesis

the result in the formula auditor look like this:
=IF("$C$5"="HL",$F$25,$F$22) which returns a false even if HL is in C5

replacing the formula with this works fine
=IF($C$5="HL",$F$25,$F$22) however, I need to creatte the reference.

Any idea how to get the reference using the address function.
Thanks
 
You have to wrap it inside an INDIRECT function:

=IF(INDIRECT(ADDRESS(5,COLUMN(Type)))="HL",$F$25,$F$22)
 
T. Valko said:
You have to wrap it inside an INDIRECT function:

=IF(INDIRECT(ADDRESS(5,COLUMN(Type)))="HL",$F$25,$F$22)
Click to expand...
....

Or you could avoid volatile functions and use

=IF(INDEX($5:$5,COLUMN(Type))="HL",$F$25,$F$22)
 
Thanks T. and Harland but solutions work.

Harland, what do you mean by "volatile". Is what was happening in my
case "buggish" results or normal for that function?
thanks
Robert
 
You could use:
=IF(INDIRECT(ADDRESS(5,COLUMN(Type)))="HL",$F$25,$F$22)
or
Since you can use R1C1 reference style within the =indirect() function:
=IF(INDIRECT("R"&5&"C"&COLUMN(Type),FALSE)="HL",$F$25,$F$22)
 
Robert H said:
Harland, what do you mean by "volatile".  Is what was happening in my
case "buggish" results or normal for that function?
Click to expand...
....

Cells with formulas calling volatile functions always recalculate
whenever anything triggers recalculation, not just when something
changes. For example, if cell C5 contained the formula =C4+1, it only
needs to recalculate when the value in C4 changes. That's how Excel
would handle recalculating C5. However, if cell D5 contained the
formula =INDIRECT("D4")+1, then Excel will recalculate it whenever
anything changes that would trigger recalculation anywhere else. If
you enter a new value in cell C4, both C5 and D5 would recalculate,
even though D5 didn't need to. OTOH, if you enter a new value in cell
D4, cell C5 won't recalculate, but D5 will (in this case properly so).
If you enter a new value in cell E5, C5 won't recalc, but D5 will.

If you have a lot of cells calling volatile functions, you can slow
down recalc a lot. If you only have a few cells doing so, you may not
notice them.

Your original formula tried to use ADDRESS(5,COLUMN(Type)) as a cell
reference. It's not. The ADDRESS function returns a text
representation of a cell address, but not a usable cell reference. The
difference between C5 and "C5" is that C5 means use the value of cell
C5 while "C5" means use the literal text "C5" which could just as
easily have been "this is not a cell reference". So your original
formula was comparing the text string "$C$5" to the text string "HL",
and that comparison will always result as FALSE. You'd either have to
wrap an INDIRECT call around the ADDRESS call, so INDIRECT(ADDRESS
(5,COLUMN(Type))), in order to treat it as a cell reference, or use
something else entirely, such as what I proposed, INDEX($5:$5,COLUMN
(Type)). IMO, there are no advantages to the INDIRECT(ADDRESS(..))
approach, only drawbacks which may or may not matter.
 
The INDIRECT function is a volatile function. Normally, a formula only
calculates upon its initial entry or if a dependency of that formula
changes. Volatile functions are different. They calculate every time some
event triggers a calculation. This can adversely effect the performance and
efficiency of the file especially if the file is "big" and contains lots of
formulas. For this reason, it's generally a good idea to avoid volatile
functions when/if possible *but* don't think using volatile functions is a
"fatal" design flaw. There are some things that can only be done using
volatile functions.

Bottom line, if efficieny is a primary concern then try to avoid using
volatile functions.

--
Biff
Microsoft Excel MVP


Thanks T. and Harland but solutions work.

Harland, what do you mean by "volatile". Is what was happening in my
case "buggish" results or normal for that function?
thanks
Robert
 
Thanks Biff, the education continues!
Robert

The INDIRECT function is a volatile function. Normally, a formula only
calculates upon its initial entry or if a dependency of that formula
changes. Volatile functions are different. They calculate every time some
event triggers a calculation. This can adversely effect the performance and
efficiency of the file especially if the file is "big" and contains lots of
formulas. For this reason, it's generally a good idea to avoid volatile
functions when/if possible *but* don't think using volatile functions is a
"fatal" design flaw. There are some things that can only be done using
volatile functions.

Bottom line, if efficieny is a primary concern then try to avoid using
volatile functions.

--
Biff
Microsoft Excel MVP


Thanks T. and Harland but solutions work.

Harland, what do you mean by "volatile". Is what was happening in my
case "buggish" results or normal for that function?
thanks
Robert
Click to expand...
 
Given what the OP expected to be in the cell ($C$5 directly), and that the
OP was doing this in VB, wouldn't just constructing the string directly and
interjecting it into the formula be a better approach? I'm guessing the Type
reference is to a Defined Name (as Type would not be an allowed variable
name in VB), so I would be thinking something like assigning this to Formula
property of the cell the OP is making his assignment to...

"=IF(" & Cells(5, Range("Type").Column).Address & "=""HL"",$F$25,$F$22)"
 
No matter how much you know about Excel, you'll never know it all (or
enough!). There's just too much to absorb and then trying to remember it is
another issue altogether.

Thanks for the feedback!
 
Harlan, that was an excellent explanation. I did not even come close
to that understanding reading the function help. I would have
eventually got it working but not understood the implications
(nuances) of the choices available.
Many thanks
Robert
 
Rick Rothstein said:
Given what the OP expected to be in the cell ($C$5 directly), and that the
OP was doing this in VB, wouldn't just constructing the string directly and
interjecting it into the formula be a better approach? I'm guessing the Type
reference is to a Defined Name (as Type would not be an allowed variable
name in VB), . . .
Click to expand...

Yup, but if for whatever reason the cell named Type were moved, C5
might no longer be the same thing as INDEX(5:5,COLUMN(Type)). Up to
the OP to decide whether that matters.
 
Back
Top