Last non-empty cell in a row function

[geversud]

What is the best way to return the contents of the last empty cell in a row in a worksheet function?

e.g. If I have a function in Column A, then I want the function in Column A to identify the last column that has data in it and return its contents. It needs to be dynamic because columns are constantly being added.

 

[Jonmo1]

that was close domenic,

The problem is there are other collumns in the range that have other text. That formula finds the cell with any text furthest to the right. What I really need is to define my own array as K:K + P:P + U:U + Z:Z and make the formula refernce that array.

I've gone to insert - name - define and defined a range as described above and named it "myrange"

How can I get the formula to reference that range named "myrange"?

 

[Domenic]

Try...

=LOOKUP(REPT("z",255),IF(MOD(COLUMN(K2:Z2)-COLUMN(K2),5)=0,K2:Z2))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Adjust the range accordingly.

Hope this helps!

 

[Jonmo1]

I appreciate you trying, but it's still returning ANY text furthest to the right in the range. Basically, I have 8 sets of collumns, each with a Test result, test date, date result recieved ,comments.

Test1 Result | Test1 Date | Test1 Results Date | Test1 Comments | Test2 Result | Test2 Date | Test2 REsults Date | Test 2 Comments etc...repeated for 8 tests.

So the last formula is getting close, but it's returning Comments, instead of Test Results.

I need the test result furthest to the right.

 

[Jonmo1]

Excellent, it works now. I just rearranged my source data so the copllumn with the Test results is to the Right of the Collumn with the comments.

Thank you for your help.

 

[Domenic]

I see from your last example that every 4 cells (not 5 as in your previous example) contains a test result. In this case, you'll need to change the MOD divisor from 5 to 4.

 

[Jonmo1]

Actually, I got it going great with your first formula suggestion.

=LOOKUP(REPT("z",255),A1:F1)

I just had to rearrange my source data so that the Test Results were to the right of the comments.

Thanks

 

[Domenic]

Oh I see. Of course, if the test result is not available but a comment exists, the formula will return the comment. But I assume the only time a test will have a comment is when a result exists, so I guess this is not an issue...

 

[Jonmo1]

That is correct. Thanks again, great help.