How do you key a number in one cell that automatically puts a number in another cell?

[Pollydp]

I'm trying to set up a worksheet that when you put a rating in one cell it automatically puts a factor in the cell underneath.

What I'm trying to achieve
Cell D7 - Rating cell that has a dropdown with the numbers 1, 2, 3, 4, 5
Cell E7 - Factor cell that relates to the number put in D7 being: 1 will equal 0.5, 2 will equal 0.7, 3 will equal 0.9, 4 will equal 1.1, 5 will equal 1.3

Example
So if I put the number 1 in D7 it then automatically puts the number 0.5 will go into E7, if I put 2 in D7 then it automatically puts 0.7 into E7, and so on using all the numbers up to 5 with its corresponding number

I've tried to do it but can't get it to work. If someone knows a way to do this I would be very appreciate.

Ta

 

[Gerald Higgins]

Re: How do you keyt a number in one cell that automatically puts a number in another cell?

Hi, welcome to the board.

To control the input values to D7, use Data Validation.
Allow
List
Source
1, 2, 3, 4, 5

To set the value in E7, use this formula
=0.5+(D7-1)*0.2

This will show 0.5 if D7 is blank - if you don't want that, post back and we can adapt it.

 

[Pollydp]

Re: How do you keyt a number in one cell that automatically puts a number in another cell?

Thank you for your reply but I don't think I explained myself too well.

What I want to do is have one cell (D7) for a Rating system using a dropdown (which is already set up) that has the numbers 1, 2, 3, 4, 5 in.
When they choose a rating number from the drop list in D7 a factor number automatically comes up in in the cell below (E7), as per below:
If they choose rating 1 in D7 it will automatically put factor 0.5 in E7
If they choose rating 2 in D7 it will automatically put factor 0.7 in E7
If they choose rating 3 in D7 it will automatically put factor 0.9 in E7
If they choose rating 4 in D7 it will automatically put factor 1.1 in E7
If they choose rating 5 in D7 it will automatically put factor 1.3 in E7

Basically I want the number they put in D7 to determine the number that comes up in E7

Can you help. Ta

 

[Michael M]

Re: How do you keyt a number in one cell that automatically puts a number in another cell?

Is this what you mean ??

=LOOKUP(D7,{1,2,3,4,5},{0.5,0.7,0.9,1.1,1.3})

 

[Gerald Higgins]

Re: How do you keyt a number in one cell that automatically puts a number in another cell?

Pollydp - did you actually try my suggestion in post #2 ? I've tested it again and it seems to do exactly what you said in post #1 and post #3.

If you've already got D7 working, you can ignore what I said about Data Validation. Just use the bit I wrote about E7.

Like I said, it will return 0.5 if there is no value in D7.

Michael M's suggestion should also work, although like mine it may produce undesired results if D7 is empty. Also, I think mine has the advantage in that if the sequence of values continues with the same logic to (almost) infinity, mine should be able to handle that, whereas Michael M's suggestion would require updating the lookup array.

 

[Raymondc190466]

Re: How do you keyt a number in one cell that automatically puts a number in another cell?

If put this on sheet2:
[TABLE="width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD]0.5[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0.7[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]0.9[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1.1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1.3[/TD]
[/TR]
</tbody>[/TABLE]

And this formula in E1 (sheet1):

=IF(D1="";0;VLOOKUP($D$1;Sheet2!$A$1:$B$5;2))