SOLVED

# Auto update a cell ref with in text instruction

Copper Contributor

# Auto update a cell ref with in text instruction

To help worksheets users, I write instructions in a cells at the end of relevant columns.
Typically “Check that this value (i.e. the value calculated in the following cell) equals D54.”

How can I make D54 written in the text update automatically when I add rows and/or columns to the worksheet.?

6 Replies

# Re: Auto update a cell ref with in text instruction

@Rocketscientist1 Enter this, for instance, in D55:

``="Check that this value (i.e. the value calculated in the following cell) equals "& ADDRESS(ROW()-1,COLUMN(),4,1)``

The instruction suggests that "the following cell" would be D56 and it should be equal to D54 (i.e. one row above but same column).

Insert rows and columns and it will keep that relationship of one row above and same column.

In case you would want to put such an instruction towards the end of each row, just change the last part of the formula to  ADDRESS(ROW(),COLUMN()-1,4,1)

# Re: Auto update a cell ref with in text instruction

@Riny_van_Eekelen Hi, thanks for the quick response.
I think my description was a little misleading.
I use a cell at the end of a row of input cells say M22 to sum the inputs and use this to check that another part of the sheet (G26) is calculating correctly. In cell M21 I wright “Check that M22 equals cell G26”.

So I need the “M22” and “G22” reference in the text to update automatically when I add rows and/or columns to the spreadsheet.

# Re: Auto update a cell ref with in text instruction

@Rocketscientist1 Same principle:

``="Check that " & ADDRESS(ROW()+1,COLUMN(),4,1) & " equals " & ADDRESS(ROW()+1,COLUMN()-6,4,1)``

assuming you meant to write Check that M22 equals cell G22

It will update when you add rows and columns before G. If that is not what you have in mind, share a workbook that shows what you are actually working on and where columns may be inserted.

# Re: Auto update a cell ref with in text instruction

``= "Check that this value equals " &  ADDRESS(ROW(D54), COLUMN(D54),4,)``

Or to prove that no formula is too trivial to be a Lambda function

``````= Instructionλ("Check that this value equals ", D56)

where
Instructionλ(text, ref)
= text & ADDRESS(ROW(ref), COLUMN(ref), 4)``````

# Re: Auto update a cell ref with in text instruction

Thanks, worked a treat:smiling_face_with_smiling_eyes:
best response confirmed by Rocketscientist1 (Copper Contributor)
Solution

# Re: Auto update a cell ref with in text instruction

Many thanks, from your response I was able to decipher the code and use in several other situations:thumbs_up: