Address Function Cell Reference Help

[ajt35]

Can anyone help me with adding to an address reference? I am trying to add a cell to an address reference. For example, the using the address function a cell returns the text $AH$11. I would like to subtract 3 to that reference so it becomes $AH$8. Ultimately, I will use a minimum function with these two cells as the range. Is this possible? Can anyone help?

 

[Special-K99]

How are you obtaining that address reference, e.g. ADDRESS(11,34) produces $AH$11
but ADDRESS(11-3,34) will give you the result you want.

This works

=LEFT(A1,FIND("$",A1,2))&RIGHT(A1,LEN(A1)-FIND("$",A1,2))-3

 

[Scott Huish]

First off, why are you using the ADDRESS function?

Why, not just:

=MIN($AH$8,$AH$11)

 

[ajt35]

Bless are thou oh Special-K99! Thanks. I think that will work. Thanks for your help!

 

[ajt35]

Thanks for your reply HOTPEPPER. It is a long story, but that won't work for what I am trying to do. Thanks!

 

[Scott Huish]

Can't say as I understand, but you could use INDIRECT and OFFSET. Assuming your address in A1:

=MIN(INDIRECT(A1),OFFSET(INDIRECT(A1),,-3))