ADDRESS Function as cell ref in equation

[TheHat]

The ADDRESS function returns a text string of a cell location; i.e. R17. How
can I use this output as a cell reference in an equation? I keep getting
#VALUE! errors.

 

[Dave Peterson]

Put it inside =indirect().

=indirect("a1")
will always point to A1
=indirect(a1)
will point to the cell whose address is in A1.

=indirect(address(....

 

[TheHat]

Dave - This did solve the VALUE error problem; but the output is an absolute
"0". The resultant value of the new equation is zero. I still can't get a
cell reference that can be used in an equation. I guess the better question
is, how can I write an equation that will determine the cell the equation is
in, and use that cell as a reference in another equation?
--
Thanks,
TheHat

Put it inside =indirect().

=indirect("a1")
will always point to A1
=indirect(a1)
will point to the cell whose address is in A1.

=indirect(address(....

 

[T. Valko]

Post the *exact* formula you tried.

--
Biff
Microsoft Excel MVP

Dave - This did solve the VALUE error problem; but the output is an
absolute
"0". The resultant value of the new equation is zero. I still can't get
a
cell reference that can be used in an equation. I guess the better
question
is, how can I write an equation that will determine the cell the equation
is
in, and use that cell as a reference in another equation?

 

[pwagner2000]

Not an answer but a question in the same line of thought...
Say the SS is incrementing to the next number in the row, but the rows
are grouped in sets of rows. Each set(group) of rows is numbered. A
valid equation would consist of:

=(MAX($A$10:$A20)+1)

This would be placed in cell A21 and would "get" the largest number in
the range of rows from 10 through 20 and increment to the next
number. Thus correctly numbering each set. This will work for a
predefined SS where the lines can be deleted and the groups of rows
will retain the correct numbering...

I desire to make this a bit more autononous w/o the use of a macro.
The purpose being, adding a set of rows w/o having to bother with
readjusting the equation. And in some cases the desire to not execute
macros in the SS for the purposes of security .

Say with an equation like: =(MAX($A$10ADDRESS((ROW()-1),1))))+1)
but EXCEL doesn't like the syntax that is being used and it isn't
clear what should be corrected.