extract column or row reference

[Guest]

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.

 

[Chip Pearson]

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

 

[Guest]

Thank you

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

 

[Guest]

Chip, I do appreciate your hint of using INDIRECT. And while it helps set my
references for all the sums I need to do, I'm still wondering if there is a
function to extract the row number from a cell referencing another cell.

I have ='sheet1'!A22 in a cell which references a date, so displayed value
is 01/04/05. I need to change this date to reference a different row in
column A so that the ton of summaries are also changed. While the INDIRECT
lets me make massive changes without 'Replace All' I still would like to set
the value used by INDIRECT dynamically so that the cell used would have a
value of 22. And when I change to A99, it would have 99.

I tried the MID function to get to"22", tried using CONCATENATE with a
leading single quote to convert formula to text, but I think my problem is
that the cell is not text and those function can't deal with the formula in
the cell.

I may be asking for a function that doesn't exist in Excel, but I'm hoping
I'm wrong.

Thanks for any assistance/guidance.