How do I find the address of last non-blank cell in a row (while skipping columns)

Question

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").

Answer 1

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:

1. ADDRESS builds the address, taking the row number (known here) and the column number (what you mean to get).

2. SUMPRODUCT(MAX((A2:J2<>"")*(COLUMN(A2:J2)) ... )) picks the maximum column number for non-blank cells.

3. ... *(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

https://stackoverflow.com/questions/30874261/simple-enquiry-with-complex-answer-how-do-i-select-rowa6-rowlast-non-blank-f/30875675#30875675

https://stackoverflow.com/a/19915043/2707864

Answer 2

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)), "\$", "")

_______________________________________________________________