 SOLVED

# 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

5 Replies

# Re: Reference to a specific cell (even after insert)

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())

# Re: Reference to a specific cell (even after insert)

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 Sergei Baklan (MVP)
Solution

# Re: Reference to a specific cell (even after insert)

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.

# Re: Reference to a specific cell (even after insert)

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

# Re: Reference to a specific cell (even after insert)

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.