Last column with data .. kinda

[uglynotstupid]

See attached.
Problem. If B <> Closed or Resolved, then I need to find the last column with data-2. Example, number 3 would be 700.10.1.1

I know how to do the IF and get the last column with data, but not how to get the 2nd to last column with data.

Thank you.

[alansidman]

Here is a vba solution

Please Login or Register  to view this content.

[uglynotstupid]

Here is a vba solution

Please Login or Register  to view this content.

Unfortunately I cant use VBA here. "Security reasons" lol

What this is for, I need to subtract all the values that are not closed from the total number of values obtained. So if I can pull the number I can populate my countif statement on the other tabs of my worksheet.

[alansidman]

Sorry, but I don't have any other solution for you. Maybe someone else will jump in. Good Luck.

[uglynotstupid]

Thank you. I was hoping to just do it row by row and use one of the blank columns.
Like put something in Column C
Example: =IF(B2<>"Closed",LOOKUP(2,1/(NOT(ISBLANK(E2:Z2))),E2:Z2),"")

That gives me the last column but not the one I need.

[AliGW]

Try this:

=IF(B2<>"Closed",LOOKUP(2,1/(NOT(ISBLANK(E2:Z2))),OFFSET(E2:Z2,,-2)),"")

[uglynotstupid]

YES!!! Thank you!

[alansidman]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[Rick Rothstein]

Try this:

=IF(B2<>"Closed",LOOKUP(2,1/(NOT(ISBLANK(E2:Z2))),OFFSET(E2:Z2,,-2)),"")

You forgot about Column B having "Resolved" in it, the OP wanted that ignored also. This should fix your formula for that...

=IF(AND(B3<>{"Closed","Resolved"}),LOOKUP(2,1/(NOT(ISBLANK(E3:Z3))),OFFSET(E3:Z3,,-2)),"")

As an aside, this is the formula I came up with which also seems to work...

=IF(AND(B2<>{"Closed","Resolved"}),LOOKUP(2,1/(E2:XFD2<>""),C2:XFB2),"")

I did not know the maximum possible column that data could be placed in, so I just used the rest of the column. It would probably be more efficient to change them to something more closely related to the actual maximum column. If you do, note the red range must be two cells to the left of whatever is used for the blue range.

[AliGW]

I was working from the formula the OP gave in post #5, Rick - I didn't actually read the earlier post, since he said that almost worked. Thanks for the correction.