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! |
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! |
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 |
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 |
All times are GMT +1. The time now is 05:04 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com