Make a cell value = the furthest value to the right

[Lucien]

How do I make a cell value update to equal the furthest value to the right in the same row?

This cell just needs to look along the right in its own row and grab the value furthest away to the right. It will be a letter value, not a number. eg, AB or AC or AD or AE. It will not (in this case) need to look further than about 150 columns or cells.

Thanks.

[sglife]

formula in A6,
Assume there is no empty cells

Please Login or Register  to view this content.

[Lucien]

thanks heaps for the prompt reply.

There are empty cells.

The document is a transmittal register for CAD drawings and not every drawing is transmitted on every occasion. The Cell in question must recall the latest revision number.

<edit> sglife, I tried your formula - it is a ripper. It works perfectly if there are no empty cells. Unfortunately there are empty cells in this case.

[sglife]

Maybe you should use VBA.
Alt + F11
Insert > Modules
Paste the following code

Please Login or Register  to view this content.

Save.
Then you can use the formula of =rightmost(A1:F1) eg.

[rwgrietveld]

How about this one:

Please Login or Register  to view this content.

[Lucien]

WOOT!!!!

Thanks you heaps.

I used sglife's VBA macro and it works a treat. I look forward to trying rwgrietvelds formula as perhaps it is worth keeping the spreadsheet non macro enabled..... and I tend to follow the KISS rule wherever possible.
(Keep It Simple, Stupid)

This is so far beyond what I was expecting that it is nearly more than I was hoping for!!!!

Thanks again. Unbelievable

[arthurbr]

This formula should return the lest text or number in a row
=LOOKUP(2,1/(1:1<>""),1:1)

[rwgrietveld]

Arthur,
I was trying to use the lookup, but could not make it stick. I like the way of "dividing" to simplify this function.

[arthurbr]

Do you mean it didn't work?

[rwgrietveld]

Yours works. Mine didn't. Therefore I came up with a different solution.