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

%3CLINGO-SUB%20id%3D%22lingo-sub-1540082%22%20slang%3D%22en-US%22%3EIF%20between%2010%20and%2020%2C%20get%20data%20from%20other%20tab%2Fsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1540082%22%20slang%3D%22en-US%22%3E%3CP%3EI%20want%20to%20auto%20populate%20a%20cell%20from%20another%20sheet%20depending%20on%20a%20range%20of%20numbers.%3C%2FP%3E%3CP%3EExample%3A%3C%2FP%3E%3CP%3EThe%20ranges%20would%20be%20static.%3C%2FP%3E%3CP%3EIF%20F7%20is%20between%20%3A%3C%2FP%3E%3CP%3E10-11%20R7%20would%20be%200%3CBR%20%2F%3E12-13%20R7%20would%20be%201%3CBR%20%2F%3E14-15%20R7%20would%20be%202%3CBR%20%2F%3E16-17%20R7%20would%20be%203%3CBR%20%2F%3E18-19%20R7%20would%20be%204%3CBR%20%2F%3E20-21%20R7%20would%20be%205%3CBR%20%2F%3E22-23%20R7%20would%20be%206%3CBR%20%2F%3E24-25%20R7%20would%20be%207%3CBR%20%2F%3E26-27%20R7%20would%20be%208%3CBR%20%2F%3E28-29%20R7%20would%20be%209%3CBR%20%2F%3E%26gt%3B%3D30%20R7%20would%20be%2010%3C%2FP%3E%3CP%3EI%20figured%20the%20best%20way%20to%20do%20this%20would%20be%20to%20have%20the%20data%20on%20another%20tab%2Fsheet%20but%20that%20may%20be%20completely%20wrong.%20I%20tried%20figuring%20it%20out%20from%20all%20the%20Excel%20Tutorials%20out%20there%20but%20my%20head%20is%20about%20to%20explode.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1540082%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1540160%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20between%2010%20and%2020%2C%20get%20data%20from%20other%20tab%2Fsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1540160%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F737170%22%20target%3D%22_blank%22%3E%40Bonehead%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20would%20be%20a%20simple%20calculation%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DMIN(10%3BF7%2F2-5)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1541911%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20between%2010%20and%2020%2C%20get%20data%20from%20other%20tab%2Fsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1541911%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETalk%20about%20over%20engineering%20a%20solution.%20Man%20I%20was%20going%20nuts.%3CBR%20%2F%3EHowever%2C%20when%20I%20tried%20using%20your%20formula%2C%20I%20get%20an%20error%20stating%3A%3CBR%20%2F%3E%22The%20formula%20you%20typed%20contains%20an%20error.%3CBR%20%2F%3E-%20For%20information%20about%20fixing%20common%20formula%20problems...%3CBR%20%2F%3E-%20To%20get%20assistance%20in%20entering%20a%20function...%3CBR%20%2F%3E-%20If%20you%20are%20not%20trying%20to%20enter%20a%20formula...%22%3CBR%20%2F%3EI%20also%20tried%20using%20%22%3DROUNDDOWN(F7%2F2-5%2C0)%22%20and%20this%20works%20until%20I%20get%20to%20values%20in%20F7%20that%20are%20%26gt%3B30.%20Anything%20%26gt%3B30%20should%20always%20return%2010.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1541974%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20between%2010%20and%2020%2C%20get%20data%20from%20other%20tab%2Fsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1541974%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F737170%22%20target%3D%22_blank%22%3E%40Bonehead%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGOT%20IT!!!%3C%2FP%3E%3CP%3E%3DMIN(10%2C0%2B(ROUNDDOWN(F7%2F2-5%2C0)))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1544381%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20between%2010%20and%2020%2C%20get%20data%20from%20other%20tab%2Fsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1544381%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F737170%22%20target%3D%22_blank%22%3E%40Bonehead%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20guess%20if%20you%20replace%20semicolon%20in%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3Bformula%20on%20comma%20it%20shall%20work.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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

@Bonehead 

That would be a simple calculation:

=MIN(10;F7/2-5)

@Detlef Lewin 

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.

@Bonehead 

GOT IT!!!

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

@Bonehead 

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