Oct 21 2023 02:04 AM
Oct 21 2023 02:04 AM
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.?
Oct 21 2023 02:22 AM - edited Oct 21 2023 02:23 AM
@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)
Oct 21 2023 04:00 AM
@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.
Oct 21 2023 04:34 AM - edited Oct 21 2023 04:35 AM
@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.
Oct 21 2023 04:55 AM
= "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)
Oct 25 2023 11:55 PMSolution