problem with ADDRESS function

[TFlanagan]

Good morning. I am using the ADDRESS function to locate a cell on a worksheet and then add other values to it. I have this expression:

=((ADDRESS(H2,(HLOOKUP(1,'Sheet1'!$DD$1:$EB$2,2,FALSE)),,,"Sheet1")))

This evaluates to:
'Sheet1'!$CE$1

If I try to add 1 to this result I get the #VALUE error. But if I just enter the formula "='Sheet1'!$CE$1" I get the intended number.

So what doesn't Excel like about
=1+((ADDRESS(H2,(HLOOKUP(1,'Sheet1'!$DD$1:$EB$2,2,FALSE)),,,"Sheet1"))) because it's doesn't seem any different than =1+'Sheet1'!$CE$1.

Thank you in advance for any advice.

 

[SkipVought]

Hi,

Address returns TEXT. You cannot use TEXT in arithmetic expressions. Try sonething like this...
[tt]
=((ADDRESS(H2,(HLOOKUP(1,'Sheet1'!$DD$1:$EB$2,2,FALSE)+1),,,"Sheet1")))
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mintjulep]

=INDIRECT((ADDRESS(H2,(HLOOKUP(1,'Sheet1'!$DD$1:$EB$2,2,FALSE)),,,"Sheet1")))+1