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" wrote:

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....

"vezerid" wrote:

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

On Dec 11, 6:16 pm, Brad wrote:
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?

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

"Brad" wrote:

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" wrote:

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....

"vezerid" wrote:

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

On Dec 11, 6:16 pm, Brad wrote:
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?

Thanks for posting back.

Brad wrote:

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

"Brad" wrote:

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" wrote:

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....

"vezerid" wrote:

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

On Dec 11, 6:16 pm, Brad wrote:
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

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?
--
David Biddulph

"Brad" wrote in message
...
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

"Brad" wrote:

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" wrote:

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....

"vezerid" wrote:

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

On Dec 11, 6:16 pm, Brad wrote:
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?

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.

"David Biddulph" wrote:

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?
--
David Biddulph

"Brad" wrote in message
...
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

"Brad" wrote:

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" wrote:

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....

"vezerid" wrote:

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

On Dec 11, 6:16 pm, Brad wrote:
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?