SOLVED

Reference to a specific cell (even after insert)

Copper Contributor

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

5 Replies

@Dshow45 

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!
best response confirmed by VI_Migration (Silver Contributor)
Solution

@Dshow45 

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.

Thank you. That command works... (I must have had a typo...)
Anyway, thank you and have a great day!

@Dshow45 

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.

 

1 best response

Accepted Solutions
best response confirmed by VI_Migration (Silver Contributor)
Solution

@Dshow45 

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.

View solution in original post