Forum Discussion
Reference to a specific cell (even after insert)
I've got a question that seems like there would be a simple solution... But I don't know it...
I have a financial spreadsheet. The value of each account, for a given date, is put into cells in column C. I have formulas that reference the values in column C. My issue is, whenever I update this spreadsheet, I insert a new column in C and the prior value is now in column D. I want my formula to reference the new value in column C, but since I inserted a column, it now references D.
I want my formula to alway reference the value in column C even after I insert a new column. I tried RC format, but it changed the formula after I inserted the column.
Thank you
INDIRECT(address) returns the value of the cell with the specified address, whether that is a text value, a number, a date, ...
=INDIRECT("C114")/(INDIRECT("C98")+INDIRECT("C103")) should work if C114, C98 and C102 contain valid numbers.
You do have to set the number format of the cell with the formula the way you want.
5 Replies
The is an alternative technique that does not involve volatile functions. Since I only use defined names for referencing data, I first define the horizontal band of cells that contain the values to reference. Then column 3 of the band can be returned by INDEX.
band = Sheet1!$3:$134; values = INDEX(band,0,3); valuesλ = LAMBDA(k,INDEX(band,k,3));If you have access to lambda functions this can be dressed up to accept an index that will return specific terms of the value array e.g.
= valuesλ({102;114})It looks nothing like 'spreadsheeting', more a case of programming but it all works with Excel 365.
If you want to refer to a fixed cell, e.g. C2:
=INDIRECT("C2")
If you want to refer to the cell in column C in the same row as the cell with the formula:
=INDIRECT("C"&ROW())
- Thank you Hans! The "Indirect" function is just what I needed.
How do I do simple math using the "Indirect" function, like: =C114/(C98+C103)? I tried =Indirect("C114")/(Indirect("C98")+Indirect("C103")) but not sure if the function returns a number or string... Thanks again!INDIRECT(address) returns the value of the cell with the specified address, whether that is a text value, a number, a date, ...
=INDIRECT("C114")/(INDIRECT("C98")+INDIRECT("C103")) should work if C114, C98 and C102 contain valid numbers.
You do have to set the number format of the cell with the formula the way you want.
