Basically, I need to do what the title says on my spreadsheet here. So for row 3, I need to find the address of the last non-blank cell in that row, while skipping the grayed out columns (with the header "Total").
2 Answers
If your data is in columns A:J (i.e., 1 to 10), and columns to be ignored are C and F (3 and 6), then for row 2 you would use
=ADDRESS(ROW(A2),SUMPRODUCT(MAX((A2:J2<>"")*(COLUMN(A2:J2))*(1-(COLUMN(A2:J2)=3))*(1-(COLUMN(A2:J2)=6)))))
Explanation:
ADDRESS
builds the address, taking the row number (known here) and the column number (what you mean to get).SUMPRODUCT(MAX((A2:J2<>"")*(COLUMN(A2:J2)) ... ))
picks the maximum column number for non-blank cells.... *(1-(COLUMN(A2:J2)=3))*(1-(COLUMN(A2:J2)=6))
makes the formula ignore specific columns (3 and 6 here). You should add other similar factors if needed.
Copy the formula downwards. Note that you might need to use some absolute references.
This also works
=ADDRESS(row(A2),INDEX(MAX((A2:J2<>"")*(COLUMN(A2:J2))*(1-(COLUMN(A2:J2)=3))*(1-(COLUMN(A2:J2)=6))),0))
Adapted from related answers
1. this can be solved with a middle step (side calculation):
=TRANSPOSE(QUERY(QUERY({O60; N60; J60}, "select *"),
"select Col1 where Col1 is not NULL"))
- selected columns
{O60; N60; J60}
needs to be in reverse alphabet order
_______________________________________________________________
2. then you can get absolute reference with this formula:
=CELL("ADDRESS", INDEX(J60:60, MATCH(J60, J60:60, 0), 1))
_______________________________________________________________
3. and if you want to drop absolute reference, you need to wrap it into REGEXREPLACE()
:
=REGEXREPLACE(CELL("ADDRESS", INDEX(J60:60, MATCH(J60, J60:60, 0), 1)), "\$", "")