Changing the numbers in cells based on the number inputted

**WaqasTariq** · 09-18-2012, 04:25 PM

Hi,
*Found a solution, will post asap.

My scenario is that I will have random numbers in cell B:B, the numbers will be inputted by the user in cell B:B (the numbers may range from 1~9, 1~5, 1~3, any combination). In cell D3 the user will enter the highest range of the numbers (so if B:B contains only numbers between 1~5 the highest range can be 5 or 1, if B:B contains numbers between 1~7 the highest range can be 7 or 1, etc).

What I need to be able to do is that I want to switch numbers if the value in D3 (highest range) is other than 1, so if the range B:B contains numbers from 1~9 and the value in D3 is 9, this means the numbers have to be resorted (using column D:D). 9 will become 1, 8 will be 2, 7 will be 3, 6 will be 4, 5 will be 5, 4 will be 6, 3 will be 7, 2 will be 8, and 1 will be 9)....

To be clear (if the range in B:B was from 1~4, and the Value in D3 was 4, this means it will resort B:B in D:D changing numbers from 4 will be 1, 3 will be 2, 2 will be 3, 1 will be 4). I hope this makes sense.

If the value put in D3 is 1 the exact B:B will be copied to D:D

Any help on this will be much appreciated, my head is spinning right now trying to think how to do this... the excel file is attached here.

Example,

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

**Jacc** · 09-18-2012, 05:22 PM

Is this what you are looking for? I just used formulas to solve it and I put a random formula in column B to test it.

**WaqasTariq** · 09-18-2012, 05:27 PM

Originally Posted by Jacc

Is this what you are looking for? I just used formulas to solve it and I put a random formula in column B to test it.

Thank you for the help, but that is not it. Its changing B:B and D:D both, while what I want to have happen is that taking values from B:B and taking highest range from C3, we sort a new for D:D

**Jacc** · 09-19-2012, 03:15 AM

User puts in random numbers in column B

In C3 the highest number from column B is extracted

In column D the new values are calculated

Is any of this correct?

**WaqasTariq** · 09-19-2012, 09:21 AM

Thank you so much for trying to help, but that is not it.

User puts random numbers in column B
User then puts the highest number that is in column B in C3
In column D new values are calculated.

If the range in column B is from 1~9 and the highest number is 9 (put in D3) then, 9 will become 1, 8 will be 2, 7 will be 3, 6 will be 4, 5 will be 5, 4 will be 6, 3 will be 7, 2 will be 8, and 1 will be 9

If the range in column B is from 1~4 and the highest number is 4 (put in D3) then, 4 will be 1, 3 will be 2, 2 will be 3, 1 will be 4).

The ranges can be 1~2, 1~3, 1~4,1~5, 1~6, 1~7, 1~8, 1~9

If the highest number in D3 is put as one, nothing happens and the column B is exactly copied to column D.