Home |
Search |
Today's Posts |
#1
|
|||
|
|||
extract column or row reference
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
Thank you
"Chip Pearson" wrote: 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. |
#4
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Value between 2 dates | Excel Worksheet Functions | |||
Lookup Table Dilemma | Excel Worksheet Functions | |||
Extract AutoFilter Column Values? | Excel Discussion (Misc queries) | |||
I need to find the Average from Column A - but Reference Column B | Excel Worksheet Functions | |||
How can I sort an entire spreadsheet from a list | Excel Worksheet Functions |