Forum Discussion
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.?
- Many thanks, from your response I was able to decipher the code and use in several other situations👍
6 Replies
= "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)- Thanks, worked a treat😊
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)
- Many thanks, from your response I was able to decipher the code and use in several other situations👍
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.
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.
