Problems with indirect

[Brad]

On one particular sheet I'm having problem getting indirect to work

=indirect("G"&11+ad65) will give me a value error -

However

=indirect("G"&text(11+ad65,"##)) - will give me the correct answer

In the same workbook - the first method of indirect works just fine.

Does anyone know what is going on with this?

 

[Dave Peterson]

What's in AD65? Is it a number?

What's in the cell (G<11+whateverisinad65>)

And it's probably best to post the formulas you actually used in the worksheet
instead of retyping them in the message.

Maybe you made a mistake in the formulas you posted--for instance, the second
one isn't correct. Maybe the same thing happened in the worksheet???

 

[vezerid]

What is the value stored in AD65? Try, in the formula bar, to select
the INDIRECT argument i.e. "G"&11+ad65 and press F9. What does the
expression evaluate to?

The strange thing is you are getting #VALUE! and not #REF!. This is
likely if INDIRECT interprets the argument as a range rather than a
single cell, in which case you would need to array-enter it to avoid
#VALUE!.

HTH
Kostis Vezerides

 

[David Biddulph]

In your first expression you have a text string "G11", formed from the
concatenation of "G" with 11, and then you are trying to conduct an
arithmetic add (with the + symbol) between that text string and the content
of AD65. You can't do an arithmetic add on a text string like "G11".

In your second expression you are conducting an arithmetic operation to add
the number 11 to what is presumably a number in AD65. The result of this
arithmetic operation is a number, which you are then converting to text with
the TEXT function, then concatenating that text to the text "G", and the
resulting text string is the input to the INDIRECT function.

You say that in the same workbook the first method works just fine. Exactly
what is the formula that you think works, and what are the values in the
cells which are inputs to that formula?

 

[Brad]

What is in cell AD65 it is just a number in this example it is 65 (it could
also be 55 or 60)

What is in cell ("G"&11+Whateverisinad65) - just a sum of other numbers -
not an array. example =SUM(F96:F$130)

This is not a mistake in typing

Just a bit of clarification - there are four tabs that I can't get the
indirect to work without using the text command. - going through Evaluate
formula - it doesn't attach the two components (the letters and the numbers
together). However, if I add a new worksheet - the conventional indirect
works just fine.

 

[Brad]

I would agree that trying to add 65 to "G11" would not work - however that is
not what the formula is doing - I'm taking "G" and add it 11+65 or 76 to end
up with "G76".

This is not the first time that I have used this feature of Excel - however
this is the first time that it hasn't worked. What is weird is that I can
add a worksheet and the fomula will work okay on that sheet, but not the
sheets that I need.

There are no macro's that are running in the background....

 

[Brad]

I agree that getting the value error is odd. When I'm in four certain tabs -
the indirect does not "link" the letters and numbers together. In going
through the formula evaluator -
=Indirect("G"&11+ad65)
=Indirect("G"&11+65)
=Indirect("G"&76)
#value

Rather than giving me Indirect("G76")

Don't know why....

 

[Brad]

What is even more weird is that I can take an indirect formula that is
working and put it in one of the sheets and it will not work.

 

[Brad]

SYMPTOMS
In Microsoft Excel, when you concatenate cells, you receive a #VALUE! error
if the Transition Formula Evaluation (TFE) option in version 5.0 or the
Alternate Expression Evaluation (AEE) option in version 4.0 is enabled and
one of the concatenated cells is blank or contains a number.

This behavior also occurs with the INDIRECT function when you use
concatenated cells. For example, the formula

=INDIRECT("A"&B1)

Turning this off solved the problem

 

[Dave Peterson]

Thanks for posting back.

SYMPTOMS
In Microsoft Excel, when you concatenate cells, you receive a #VALUE! error
if the Transition Formula Evaluation (TFE) option in version 5.0 or the
Alternate Expression Evaluation (AEE) option in version 4.0 is enabled and
one of the concatenated cells is blank or contains a number.

This behavior also occurs with the INDIRECT function when you use
concatenated cells. For example, the formula

=INDIRECT("A"&B1)

Turning this off solved the problem

 

[David Biddulph]

Congratulations of finding the failure mode.

I guess that it might be worth seeing whether it works if you change
=Indirect("G"&11+ad65) to =Indirect("G"&(11+ad65)) , but the way you
describe the problem I fear that it might not.

Your formula =Indirect("G"&11+ad65) provokes an interesting question on
precedence of operations. We know that multiplication and division take
precedence over addition or subtraction in expressions like =A1+B1*C1, but
where does string concatenation fit compared with these arithmetic
precences?

 

[Brad]

David

The way that indirect works (I believe) is that it uses AOS between the "&"'s
Having the additional paren's doesn't make a difference here.