0

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

enter image description here

1

2 Answers 2

0

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

0

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

_______________________________________________________________

6

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.