Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
If B1=Level 1 then B2=10
Sorry for the double post - but I just realized by Subject line didn't fit at
all!! Cell B1 is using data validation referring to a named range. There are 4 possible values: Level 1, Level 2, Level 3, Level 4. I have another named range with the number of days associated with each Level. So, if the user selects Level 1 from the dropdown list in B1, I want the default number of days (10) to be put automatically into B2. If the user selects Level 2 from the dropdown list in B1, I want the default number of days (20) to be put automatically into B2. And the same for Level 3 (30 days) and Level 4 (40 days). I know there is a way to do this, I think by putting the 2 lists next to each other... but I can't remember how to do it! Help! Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
If B1=Level 1 then B2=10
Shelly,
Try the formula below in Cell B2: =if(b1="Level 1",10,if(b1="Level 2",20,if(b1="Level 3",30,if(b1="Level 4",40,"")))) "Shelly" wrote: Sorry for the double post - but I just realized by Subject line didn't fit at all!! Cell B1 is using data validation referring to a named range. There are 4 possible values: Level 1, Level 2, Level 3, Level 4. I have another named range with the number of days associated with each Level. So, if the user selects Level 1 from the dropdown list in B1, I want the default number of days (10) to be put automatically into B2. If the user selects Level 2 from the dropdown list in B1, I want the default number of days (20) to be put automatically into B2. And the same for Level 3 (30 days) and Level 4 (40 days). I know there is a way to do this, I think by putting the 2 lists next to each other... but I can't remember how to do it! Help! Thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
If B1=Level 1 then B2=10
On Jan 4, 9:01*am, AccessHelp
wrote: Shelly, Try the formula below in Cell B2: =if(b1="Level 1",10,if(b1="Level 2",20,if(b1="Level 3",30,if(b1="Level 4",40,"")))) "Shelly" wrote: Sorry for the double post - but I just realized by Subject line didn't fit at all!! Cell B1 is using data validation referring to a named range. There are 4 possible values: Level 1, Level 2, Level 3, Level 4. I have another named range with the number of days associated with each Level. So, if the user selects Level 1 from the dropdown list in B1, I want the default number of days (10) to be put automatically into B2. If the user selects Level 2 from the dropdown list in B1, I want the default number of days (20) to be put automatically into B2. And the same for Level 3 (30 days) and Level 4 (40 days). I know there is a way to do this, I think by putting the 2 lists next to each other... but I can't remember how to do it! Help! Thanks!- Hide quoted text - - Show quoted text - I would recomend to use VLookup. for that you have to create a Table, preferably in a sheet for setting up, say "Templates" it will read something like this.. A1 = Level1 B1 = 10 A2 = Level2 B2 = 20 A3 = Level3 B3 = 30 A4 = Level4 B4 = 40 optionally, you can define a Name as TBL = Range("A1:B4") (Select the Range of the tabel and press <CTRL + F3 to define a name.) A1 is your Selected Level. B1 = =VLOOKUP(A1,TBL,2,0) 'TBL is the name of the Table. or if you dont have the name defined, do as follows... B1 = =VLOOKUP(A1,'TEMPLATES'!$A$1:$B$4,2,0) 'where Templates is the sheet name where the Table is... Let me know how it works... HTH Joe |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
If B1=Level 1 then B2=10
Worked perfectly! Thanks!
"Joe" wrote: On Jan 4, 9:01 am, AccessHelp wrote: Shelly, Try the formula below in Cell B2: =if(b1="Level 1",10,if(b1="Level 2",20,if(b1="Level 3",30,if(b1="Level 4",40,"")))) "Shelly" wrote: Sorry for the double post - but I just realized by Subject line didn't fit at all!! Cell B1 is using data validation referring to a named range. There are 4 possible values: Level 1, Level 2, Level 3, Level 4. I have another named range with the number of days associated with each Level. So, if the user selects Level 1 from the dropdown list in B1, I want the default number of days (10) to be put automatically into B2. If the user selects Level 2 from the dropdown list in B1, I want the default number of days (20) to be put automatically into B2. And the same for Level 3 (30 days) and Level 4 (40 days). I know there is a way to do this, I think by putting the 2 lists next to each other... but I can't remember how to do it! Help! Thanks!- Hide quoted text - - Show quoted text - I would recomend to use VLookup. for that you have to create a Table, preferably in a sheet for setting up, say "Templates" it will read something like this.. A1 = Level1 B1 = 10 A2 = Level2 B2 = 20 A3 = Level3 B3 = 30 A4 = Level4 B4 = 40 optionally, you can define a Name as TBL = Range("A1:B4") (Select the Range of the tabel and press <CTRL + F3 to define a name.) A1 is your Selected Level. B1 = =VLOOKUP(A1,TBL,2,0) 'TBL is the name of the Table. or if you dont have the name defined, do as follows... B1 = =VLOOKUP(A1,'TEMPLATES'!$A$1:$B$4,2,0) 'where Templates is the sheet name where the Table is... Let me know how it works... HTH Joe |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Two Level Search | Excel Worksheet Functions | |||
20 level nested If | Excel Discussion (Misc queries) | |||
workbook level name vs worksheet level name | Excel Programming | |||
Using macro to convert single level BOM to Multi Level BOM | Excel Programming | |||
Why, when I create workbook-level name does it jump it to Sheet-level ? | Excel Programming |