Apr 09 2022 06:01 AM
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
Apr 09 2022 06:09 AM
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())
Apr 09 2022 07:27 AM
Apr 09 2022 08:11 AM
SolutionINDIRECT(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.
Apr 09 2022 08:18 AM
Apr 09 2022 09:52 AM
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.
Apr 09 2022 08:11 AM
SolutionINDIRECT(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.