Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
If then, vlook up and list box
Hi,
A1 is a list box with 12 options to pick from B1 is a yes or no field C1 needs to look at B1, if yes, look at A1, see the chosen option and go to a separate worksheet to pull the corresponding cost. If no, then enter $0.00. I've playing with this formula but not getting anywhere. Is it possible to do this? =IF(ISNA(VLOOKUP(GB3,RATES!$A$67:$B$78,2,FALSE))," ",VLOOKUP(GB3,RATES!$A$67:$B$79,2,FALSE)) Kathleen |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
If then, vlook up and list box
=if(b1="no",0,vlookup(a1,rates!a:b,2,false))
This assumes that the value in A1 is either Yes or No (nothing else!). You may want to add that =isna() stuff in case there's no match. =IF(B1="no",0,IF(ISNA(VLOOKUP(A1,Rates!A:B,2,FALSE )),"", VLOOKUP(A1,Rates!A:B,2,FALSE))) Kathleen wrote: Hi, A1 is a list box with 12 options to pick from B1 is a yes or no field C1 needs to look at B1, if yes, look at A1, see the chosen option and go to a separate worksheet to pull the corresponding cost. If no, then enter $0.00. I've playing with this formula but not getting anywhere. Is it possible to do this? =IF(ISNA(VLOOKUP(GB3,RATES!$A$67:$B$78,2,FALSE))," ",VLOOKUP(GB3,RATES!$A$67:$B$79,2,FALSE)) Kathleen -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
If then, vlook up and list box
Hi Dave,
I think I've done a poor job in explaining and I apologize. I think I should have added another cell to make more sense. A1 is a list box of 12 different coverage types B1 is a yes or no field D1/E1 (on separate rate sheet) is a listing of rates corresponding to the coverage types in A1 ie A1 = car, rate sheet = car (D1) $100 (E1) C1 needs to look at B1, if yes, then look at A1 - see coverage type - then go to rate sheet to get corresponding cost. Outcome examples: A1 available options in list box = car, cat, dog, truck Rate Sheet columns D1/E1 car $100 cat $ 50 dog $ 75 Truck $200 A1 = car B1 = yes C1 = $100 (from rate sheet) ...... A2 = car B2 = no C2 = $0.00 ----- A3 = dog B3 = yes C3 = $75 (from rate sheet) Does this make sense? Either way, I very much appreciate your taking the time to help. "Dave Peterson" wrote: =if(b1="no",0,vlookup(a1,rates!a:b,2,false)) This assumes that the value in A1 is either Yes or No (nothing else!). You may want to add that =isna() stuff in case there's no match. =IF(B1="no",0,IF(ISNA(VLOOKUP(A1,Rates!A:B,2,FALSE )),"", VLOOKUP(A1,Rates!A:B,2,FALSE))) Kathleen wrote: Hi, A1 is a list box with 12 options to pick from B1 is a yes or no field C1 needs to look at B1, if yes, look at A1, see the chosen option and go to a separate worksheet to pull the corresponding cost. If no, then enter $0.00. I've playing with this formula but not getting anywhere. Is it possible to do this? =IF(ISNA(VLOOKUP(GB3,RATES!$A$67:$B$78,2,FALSE))," ",VLOOKUP(GB3,RATES!$A$67:$B$79,2,FALSE)) Kathleen -- Dave Peterson . |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
If then, vlook up and list box
Hi Kathleen - I created a formula that might be along the lines of what your after. I used your example and created a sheet named "Rates" and populated d1:e4 as you implied. On the original sheet, I placed a value of True or False in cell B1 and typed car or cat or whatever. (I'm assuming your list box assigns such a value to cell a1) Then this formula =IF(B1,VLOOKUP(A1,rates!$D$1:$E$4,2,FALSE),0) If cell b1 contained the word "Yes" or some other text, then you might need to edit the formula to look something like =IF(B1="Yes",VLOOKUP(A1,rates!$D$1:$E$4,2,FALSE),0 ) Peace EQC |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
If then, vlook up and list box
If you really have a listbox that hovers above A1, you'll have to have a way to
get the value out of that listbox--maybe using the linked cell property??? After that, it still sounds like =vlookup() and =if() will work for you. Debra Dalgleish has lots of notes: http://www.contextures.com/xlFunctions02.html (for =vlookup()) and http://contextures.com/xlFunctions02.html#Trouble Kathleen wrote: Hi Dave, I think I've done a poor job in explaining and I apologize. I think I should have added another cell to make more sense. A1 is a list box of 12 different coverage types B1 is a yes or no field D1/E1 (on separate rate sheet) is a listing of rates corresponding to the coverage types in A1 ie A1 = car, rate sheet = car (D1) $100 (E1) C1 needs to look at B1, if yes, then look at A1 - see coverage type - then go to rate sheet to get corresponding cost. Outcome examples: A1 available options in list box = car, cat, dog, truck Rate Sheet columns D1/E1 car $100 cat $ 50 dog $ 75 Truck $200 A1 = car B1 = yes C1 = $100 (from rate sheet) ..... A2 = car B2 = no C2 = $0.00 ----- A3 = dog B3 = yes C3 = $75 (from rate sheet) Does this make sense? Either way, I very much appreciate your taking the time to help. "Dave Peterson" wrote: =if(b1="no",0,vlookup(a1,rates!a:b,2,false)) This assumes that the value in A1 is either Yes or No (nothing else!). You may want to add that =isna() stuff in case there's no match. =IF(B1="no",0,IF(ISNA(VLOOKUP(A1,Rates!A:B,2,FALSE )),"", VLOOKUP(A1,Rates!A:B,2,FALSE))) Kathleen wrote: Hi, A1 is a list box with 12 options to pick from B1 is a yes or no field C1 needs to look at B1, if yes, look at A1, see the chosen option and go to a separate worksheet to pull the corresponding cost. If no, then enter $0.00. I've playing with this formula but not getting anywhere. Is it possible to do this? =IF(ISNA(VLOOKUP(GB3,RATES!$A$67:$B$78,2,FALSE))," ",VLOOKUP(GB3,RATES!$A$67:$B$79,2,FALSE)) Kathleen -- Dave Peterson . -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
If then, vlook up and list box
Thank you both!! Its working very well.
"Dave Peterson" wrote: If you really have a listbox that hovers above A1, you'll have to have a way to get the value out of that listbox--maybe using the linked cell property??? After that, it still sounds like =vlookup() and =if() will work for you. Debra Dalgleish has lots of notes: http://www.contextures.com/xlFunctions02.html (for =vlookup()) and http://contextures.com/xlFunctions02.html#Trouble Kathleen wrote: Hi Dave, I think I've done a poor job in explaining and I apologize. I think I should have added another cell to make more sense. A1 is a list box of 12 different coverage types B1 is a yes or no field D1/E1 (on separate rate sheet) is a listing of rates corresponding to the coverage types in A1 ie A1 = car, rate sheet = car (D1) $100 (E1) C1 needs to look at B1, if yes, then look at A1 - see coverage type - then go to rate sheet to get corresponding cost. Outcome examples: A1 available options in list box = car, cat, dog, truck Rate Sheet columns D1/E1 car $100 cat $ 50 dog $ 75 Truck $200 A1 = car B1 = yes C1 = $100 (from rate sheet) ..... A2 = car B2 = no C2 = $0.00 ----- A3 = dog B3 = yes C3 = $75 (from rate sheet) Does this make sense? Either way, I very much appreciate your taking the time to help. "Dave Peterson" wrote: =if(b1="no",0,vlookup(a1,rates!a:b,2,false)) This assumes that the value in A1 is either Yes or No (nothing else!). You may want to add that =isna() stuff in case there's no match. =IF(B1="no",0,IF(ISNA(VLOOKUP(A1,Rates!A:B,2,FALSE )),"", VLOOKUP(A1,Rates!A:B,2,FALSE))) Kathleen wrote: Hi, A1 is a list box with 12 options to pick from B1 is a yes or no field C1 needs to look at B1, if yes, look at A1, see the chosen option and go to a separate worksheet to pull the corresponding cost. If no, then enter $0.00. I've playing with this formula but not getting anywhere. Is it possible to do this? =IF(ISNA(VLOOKUP(GB3,RATES!$A$67:$B$78,2,FALSE))," ",VLOOKUP(GB3,RATES!$A$67:$B$79,2,FALSE)) Kathleen -- Dave Peterson . -- Dave Peterson . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOK-pivot table expanding want to update vlook automatically | Excel Worksheet Functions | |||
VLOOK-pivot table expanding want to update vlook automatically | Excel Worksheet Functions | |||
VLOOK UP | Excel Discussion (Misc queries) | |||
If with Vlook up | Excel Discussion (Misc queries) | |||
How do I use drop down list selections/values in a vlook up formu. | Excel Discussion (Misc queries) |