The best way would be to put the value, e.g., 75, in some cell,
say A1, and use the INDIRECT function wherever you need to use
that value. E.g.,

=INDIRECT("Sheet1!$AE$"&A1)

and

=SUM(INDIRECT("$BB$1:$BB$"&A1)

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"BRPtacek" wrote in message
...
I want to extract the row or column values from a cell that is
referencing
another cell so that I can use it in a third.

For cell 1 the formula is =Sheet1$AE$75, I want to be able to
extract 75 so
I can use it in another cell such as Sum($BB$1:$BB$nn) where nn
would be the
value 75. I have a lot of referenced columns that I need to
sum. Their range
would be the same for a summary attempt but that range may need
to change. So
I don't want to do a "replace all" function every time I need
to use a
different number of rows.

I thought there was a substring extract function but I can't
seem to find it.