SOLVED

Excel

Copper Contributor

I need a formula that enters a value in one cell based on another. The entered value can be any one of five numbers i.e. If I enter 3 in cell A1, the adjacent cell A2 should display 3 or if I enter 4 in A1, A2 should display 2 or if I enter 5 in A1, A2 should display 1.

 

Can anyone help me with this formula?

8 Replies

Hello, @Trev63 !

 

You must be just getting started with Excel and formulas. They are very powerful!

 

In our example, let's enter 1, 2, and 3 in the first column in rows 1, 2, and 3. It should look like below. There's a reason I didn't go to 5 yet! Figured I'd show you a trick! ;)

 

EdHarris_0-1680285500477.png

In the lower-right of the selection, notice that little box? It's called the quick fill handle. Excel uses the context of what is selected to guess what you might enter into other cells. Try dragging it down a few rows! I'll go to 10 in my example.

 

EdHarris_1-1680285574876.png

 

Now, let's say we want our formula to display the value of this cell in some other place in the Excel document as your question alludes to. Let's assume column E for this example.

 

Select cell E1. To start typing a formula, enter the equal sign character (=). This special character, when the first character of a cell, tells the application you intend to write a formula there. It can be a mathematical formula or all kinds of other cool stuff, but in this case we simply want to mirror the values.

 

Think in terms of math. If we want to represent the contents of cell A1 regardless of what it value is inside or if it changes. So it makes sense to refer to A1, just like you would x or y in math to represent unknown values.

 

With cell E1 selected, type =. Fan of the mouse? With this equal sign selected, go crazy selecting cells with it! Or you can type them by their reference as I have been. In this case, I will just click A1 with the mouse.

 

EdHarris_2-1680285846652.png

 

Now to commit the formula to cell E1, which will simple display whatever is in A1 and update whenever it changes, press enter.

 

Remember our friend the quick-fill handle? That little box in the lower-right? Why select E1 again (you pressed enter afterall!) and use the quick fill handle to drag down 10 rows? :)

 

EdHarris_3-1680285943683.png

You can review the magic that happened by selecting any of the cells in the E column with your mouse and viewing the "formula" bar. I'll screenshot it so you know what I'm referring to.

 

EdHarris_4-1680285989032.png

 

I hope this is helpful to someone!

 - Ed

best response confirmed by Hans Vogelaar (MVP)
Solution

@Trev63 

=IF(A1=3,3,IF(A1=4,2,IF(A1=5,1,IF(A1=1,5,IF(A1=2,4,"")))))
=CHOOSE(A1,5,4,3,2,1)

In cell A2 you can try any of these formulas.

@OliverScheurich 

 

thank you for your suggestions and helpful tips. The first formula worked a treat. Just had to tweak it a little to add more numbers. Perfect, thanks again.

@OliverScheurich 

 

Hi, I have another!! In this, I have a list of names in one column and associated number in the next column. I've filtered the the numbers and associated names in order of highest number to the lowest but I want this order to change automatically if one or more of the numbers changes!

 

Can anyone add some light..... Please?

 

 

@Trev63 

=SORT(A2:B11,2,-1)

Do you want to sort the numbers and associated names? Then you can try this formula if you have access to the SORT function. It's available in Office 365 and Excel 2021 and Excel for the web.

sort.JPG

Many thanks for looking at this for me its much appreciated. I have 365 but I cant seem to get this to work. Want you have set out is exactly what I need but when it type the formula I get a error message. I can relate to your reference to A2 & B11 in the formula as that is the range but what does the ;2;-1 relate to? This is my version of your formula =SORTIEREN(H32:J43;2;-1)

@Trev63 

Which version (language) of Excel do you work with?

In german excel the formula is:

=SORTIEREN(H32:J43;2;-1)

In english:

=SORT(H32:J43,2,-1)

 

2 is the column by which the range is sorted. -1 is for descending order.

sort ordenar.JPG

 

I'm happy to see so much activity. I believe the original question was answered, so won't you please consider making another thread for your new question? That helps keep things simple and organized and is good stewardship of the community platform.
1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

@Trev63 

=IF(A1=3,3,IF(A1=4,2,IF(A1=5,1,IF(A1=1,5,IF(A1=2,4,"")))))
=CHOOSE(A1,5,4,3,2,1)

In cell A2 you can try any of these formulas.

View solution in original post