 # IF between 10 and 20, get data from other tab/sheet

I want to auto populate a cell from another sheet depending on a range of numbers.

Example:

The ranges would be static.

IF F7 is between :

10-11 R7 would be 0
12-13 R7 would be 1
14-15 R7 would be 2
16-17 R7 would be 3
18-19 R7 would be 4
20-21 R7 would be 5
22-23 R7 would be 6
24-25 R7 would be 7
26-27 R7 would be 8
28-29 R7 would be 9
>=30 R7 would be 10

I figured the best way to do this would be to have the data on another tab/sheet but that may be completely wrong. I tried figuring it out from all the Excel Tutorials out there but my head is about to explode.

4 Replies

# Re: IF between 10 and 20, get data from other tab/sheet

That would be a simple calculation:

``=MIN(10;F7/2-5)``

# Re: IF between 10 and 20, get data from other tab/sheet

Talk about over engineering a solution. Man I was going nuts.
However, when I tried using your formula, I get an error stating:
"The formula you typed contains an error.
- For information about fixing common formula problems...
- To get assistance in entering a function...
- If you are not trying to enter a formula..."
I also tried using "=ROUNDDOWN(F7/2-5,0)" and this works until I get to values in F7 that are >30. Anything >30 should always return 10.

# Re: IF between 10 and 20, get data from other tab/sheet

GOT IT!!!

=MIN(10,0+(ROUNDDOWN(F7/2-5,0)))

# Re: IF between 10 and 20, get data from other tab/sheet

I guess if you replace semicolon in @Detlef Lewin formula on comma it shall work.