top ten numbers

Hi I hope someone can assist with this it sounded so easy !!

I have a row of numbers C2:J2. In B2 I enter a new number if that number is greater than the smallest number across C2:J2 I would like B2 to replace it

However C2:J2 may have several numbers the same so it needs to just replace the first lowest number and then lock that change so when I enter a new number into B2 it doesn't lose the previous change

I hope that's clear any help would be appreciated

maybe this:

Please Login or Register  to view this content.

It will use only entered numbers so you can have less than 10 but not more (unless you change range)

Sir I bow down to your brilliance

Many thanks

Zbor I've realized I don't know how to do this macro stuff I was hoping once I had one line I could just add extra rows through
to B40

is it possible to do this?

Originally Posted by zbor

maybe this:

Please Login or Register  to view this content.

It will use only entered numbers so you can have less than 10 but not more (unless you change range)

Do you want to change any B field and check minimum within next 10 columns in same row?
Or perhapes some of them?

B2, B10, B20, B21...

What I want is exactly what you gave me for row 1 but multiply it by 40 rows so that what I put into B2 through to B42 changes c2:L2 all the way down to c42:l42

Just to be clear what figures are put in column B2 through toB42 will be different numbers

Thanks for coming back to me

Actually, mine above solution is example how NOT TO code
Once when you determine Target there's no need to use reference to that same range.

This should deal with all data in B column:

Please Login or Register  to view this content.

Change 10 in resize if you need more columns.

Thanks once more that's working