| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help w/Validation List & VLOOKUP
At least that's what we THINK we need help with. A co-worker has been working
really hard on this and has previously posted, but shes stumped. Here's our situation - We need a spreadsheet for tracking bonuses. There will be 7 columns - date, policyholder, policy, type, premium, bonus and initials. We'll fill in the date, policyholder and initials columns. We created dropdown boxes for the policy and type columns. Once we choose the Policy, we need the Type dropdown list to be a dependent dropdown, (with only the associated "Types" showing), and for the bonus to fill itself in based on the policy and type chosen. Unless Fire or Auto is chosen, in which case we need it to calculate 2% of the premium we enter. We have worked and worked on this and still cant get everything to work. We have tried the following formulas - =OFFSET(PolicyStart,MATCH(B5,PolicyColumn,0)-1,1,COUNTIF(PolicyColumn,B5),1) =IF(C5=,PolicyList,INDEX(PolicyColumn,MATCH( C5,TypeColumn,0))) =SUM(E5*2%) =vlookup(D6,A1:B3,2,FALSE) =VLOOKUP(D5,Policies!C3:D23,2,FALSE) And we have used the following informational links - http://www.contextures.com/xlFunctions02.html http://www.contextures.com/xlDataVal02.html We have uploaded the file to: http://www.savefile.com/files/1333831 Can anyone help us PLEASE? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help w/Validation List & VLOOKUP
For the cells where you want a Policy dropdown, the data validation
source is: =PolicyList For the cells with a Types dropdown, the data validation formula in row 5 is: =OFFSET(PolicyStart,MATCH(C5,PolicyColumn,0)-1,1,COUNTIF(PolicyColumn,C5),1) The dropdown won't work if Auto or Fire are selected. PolicyColumn should be defined as the entire column, as should TypeColumn. The bonus calculation in row 5 could be: =IF(E5="","",IF(OR(C5="Auto",C5="Fire"),E5*2%, OFFSET(PolicyStart,MATCH(D5,TypeColumn,0)-1,2,1,1))) klg wrote: At least that's what we THINK we need help with. A co-worker has been working really hard on this and has previously posted, but shes stumped. Here's our situation - We need a spreadsheet for tracking bonuses. There will be 7 columns - date, policyholder, policy, type, premium, bonus and initials. We'll fill in the date, policyholder and initials columns. We created dropdown boxes for the policy and type columns. Once we choose the Policy, we need the Type dropdown list to be a dependent dropdown, (with only the associated "Types" showing), and for the bonus to fill itself in based on the policy and type chosen. Unless Fire or Auto is chosen, in which case we need it to calculate 2% of the premium we enter. We have worked and worked on this and still cant get everything to work. We have tried the following formulas - =OFFSET(PolicyStart,MATCH(B5,PolicyColumn,0)-1,1,COUNTIF(PolicyColumn,B5),1) =IF(C5=,PolicyList,INDEX(PolicyColumn,MATCH( C5,TypeColumn,0))) =SUM(E5*2%) =vlookup(D6,A1:B3,2,FALSE) =VLOOKUP(D5,Policies!C3:D23,2,FALSE) And we have used the following informational links - http://www.contextures.com/xlFunctions02.html http://www.contextures.com/xlDataVal02.html We have uploaded the file to: http://www.savefile.com/files/1333831 Can anyone help us PLEASE? -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help w/Validation List & VLOOKUP
Am I just stupid?!?! It's still NOT working!! I have been up literally ALL
night trying to work this out. What am I doing wrong? http://www.savefile.com/files/1335704 "Debra Dalgleish" wrote: For the cells where you want a Policy dropdown, the data validation source is: =PolicyList For the cells with a Types dropdown, the data validation formula in row 5 is: =OFFSET(PolicyStart,MATCH(C5,PolicyColumn,0)-1,1,COUNTIF(PolicyColumn,C5),1) The dropdown won't work if Auto or Fire are selected. PolicyColumn should be defined as the entire column, as should TypeColumn. The bonus calculation in row 5 could be: =IF(E5="","",IF(OR(C5="Auto",C5="Fire"),E5*2%, OFFSET(PolicyStart,MATCH(D5,TypeColumn,0)-1,2,1,1))) klg wrote: At least that's what we THINK we need help with. A co-worker has been working really hard on this and has previously posted, but shes stumped. Here's our situation - We need a spreadsheet for tracking bonuses. There will be 7 columns - date, policyholder, policy, type, premium, bonus and initials. We'll fill in the date, policyholder and initials columns. We created dropdown boxes for the policy and type columns. Once we choose the Policy, we need the Type dropdown list to be a dependent dropdown, (with only the associated "Types" showing), and for the bonus to fill itself in based on the policy and type chosen. Unless Fire or Auto is chosen, in which case we need it to calculate 2% of the premium we enter. We have worked and worked on this and still cant get everything to work. We have tried the following formulas - =OFFSET(PolicyStart,MATCH(B5,PolicyColumn,0)-1,1,COUNTIF(PolicyColumn,B5),1) =IF(C5=,PolicyList,INDEX(PolicyColumn,MATCH( C5,TypeColumn,0))) =SUM(E5*2%) =vlookup(D6,A1:B3,2,FALSE) =VLOOKUP(D5,Policies!C3:D23,2,FALSE) And we have used the following informational links - http://www.contextures.com/xlFunctions02.html http://www.contextures.com/xlDataVal02.html We have uploaded the file to: http://www.savefile.com/files/1333831 Can anyone help us PLEASE? -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help w/Validation List & VLOOKUP
Your PolicyStart named range is broken. If you redefine it, things
should work correctly. klg wrote: Am I just stupid?!?! It's still NOT working!! I have been up literally ALL night trying to work this out. What am I doing wrong? http://www.savefile.com/files/1335704 "Debra Dalgleish" wrote: For the cells where you want a Policy dropdown, the data validation source is: =PolicyList For the cells with a Types dropdown, the data validation formula in row 5 is: =OFFSET(PolicyStart,MATCH(C5,PolicyColumn,0)-1,1,COUNTIF(PolicyColumn,C5),1) The dropdown won't work if Auto or Fire are selected. PolicyColumn should be defined as the entire column, as should TypeColumn. The bonus calculation in row 5 could be: =IF(E5="","",IF(OR(C5="Auto",C5="Fire"),E5*2%, OFFSET(PolicyStart,MATCH(D5,TypeColumn,0)-1,2,1,1))) klg wrote: At least that's what we THINK we need help with. A co-worker has been working really hard on this and has previously posted, but shes stumped. Here's our situation - We need a spreadsheet for tracking bonuses. There will be 7 columns - date, policyholder, policy, type, premium, bonus and initials. We'll fill in the date, policyholder and initials columns. We created dropdown boxes for the policy and type columns. Once we choose the Policy, we need the Type dropdown list to be a dependent dropdown, (with only the associated "Types" showing), and for the bonus to fill itself in based on the policy and type chosen. Unless Fire or Auto is chosen, in which case we need it to calculate 2% of the premium we enter. We have worked and worked on this and still cant get everything to work. We have tried the following formulas - =OFFSET(PolicyStart,MATCH(B5,PolicyColumn,0)-1,1,COUNTIF(PolicyColumn,B5),1) =IF(C5=,PolicyList,INDEX(PolicyColumn,MAT CH(C5,TypeColumn,0))) =SUM(E5*2%) =vlookup(D6,A1:B3,2,FALSE) =VLOOKUP(D5,Policies!C3:D23,2,FALSE) And we have used the following informational links - http://www.contextures.com/xlFunctions02.html http://www.contextures.com/xlDataVal02.html We have uploaded the file to: http://www.savefile.com/files/1333831 Can anyone help us PLEASE? -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help w/Validation List & VLOOKUP
woohoo!! almost there!! it will calculate the 2% for auto and fire, but it
doesn't populate for the others once a policy and type are chosen. the formula i am using is the one you said - =IF(E5="","",IF(OR(C5="Auto",C5="Fire"),E5*2%,OFFS ET(PolicyStart,MATCH(D5,TypeColumn,0)-1,2,1,1))) any ideas? "Debra Dalgleish" wrote: Your PolicyStart named range is broken. If you redefine it, things should work correctly. klg wrote: Am I just stupid?!?! It's still NOT working!! I have been up literally ALL night trying to work this out. What am I doing wrong? http://www.savefile.com/files/1335704 "Debra Dalgleish" wrote: For the cells where you want a Policy dropdown, the data validation source is: =PolicyList For the cells with a Types dropdown, the data validation formula in row 5 is: =OFFSET(PolicyStart,MATCH(C5,PolicyColumn,0)-1,1,COUNTIF(PolicyColumn,C5),1) The dropdown won't work if Auto or Fire are selected. PolicyColumn should be defined as the entire column, as should TypeColumn. The bonus calculation in row 5 could be: =IF(E5="","",IF(OR(C5="Auto",C5="Fire"),E5*2%, OFFSET(PolicyStart,MATCH(D5,TypeColumn,0)-1,2,1,1))) klg wrote: At least that's what we THINK we need help with. A co-worker has been working really hard on this and has previously posted, but shes stumped. Here's our situation - We need a spreadsheet for tracking bonuses. There will be 7 columns - date, policyholder, policy, type, premium, bonus and initials. We'll fill in the date, policyholder and initials columns. We created dropdown boxes for the policy and type columns. Once we choose the Policy, we need the Type dropdown list to be a dependent dropdown, (with only the associated "Types" showing), and for the bonus to fill itself in based on the policy and type chosen. Unless Fire or Auto is chosen, in which case we need it to calculate 2% of the premium we enter. We have worked and worked on this and still cant get everything to work. We have tried the following formulas - =OFFSET(PolicyStart,MATCH(B5,PolicyColumn,0)-1,1,COUNTIF(PolicyColumn,B5),1) =IF(C5=,PolicyList,INDEX(PolicyColumn,MAT CH(C5,TypeColumn,0))) =SUM(E5*2%) =vlookup(D6,A1:B3,2,FALSE) =VLOOKUP(D5,Policies!C3:D23,2,FALSE) And we have used the following informational links - http://www.contextures.com/xlFunctions02.html http://www.contextures.com/xlDataVal02.html We have uploaded the file to: http://www.savefile.com/files/1333831 Can anyone help us PLEASE? -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help w/Validation List & VLOOKUP
Make sure you copy the bonus formula from F5 down to F40
klg wrote: woohoo!! almost there!! it will calculate the 2% for auto and fire, but it doesn't populate for the others once a policy and type are chosen. the formula i am using is the one you said - =IF(E5="","",IF(OR(C5="Auto",C5="Fire"),E5*2%,OFFS ET(PolicyStart,MATCH(D5,TypeColumn,0)-1,2,1,1))) any ideas? "Debra Dalgleish" wrote: Your PolicyStart named range is broken. If you redefine it, things should work correctly. klg wrote: Am I just stupid?!?! It's still NOT working!! I have been up literally ALL night trying to work this out. What am I doing wrong? http://www.savefile.com/files/1335704 "Debra Dalgleish" wrote: For the cells where you want a Policy dropdown, the data validation source is: =PolicyList For the cells with a Types dropdown, the data validation formula in row 5 is: =OFFSET(PolicyStart,MATCH(C5,PolicyColumn,0)-1,1,COUNTIF(PolicyColumn,C5),1) The dropdown won't work if Auto or Fire are selected. PolicyColumn should be defined as the entire column, as should TypeColumn. The bonus calculation in row 5 could be: =IF(E5="","",IF(OR(C5="Auto",C5="Fire"),E5*2%, OFFSET(PolicyStart,MATCH(D5,TypeColumn,0)-1,2,1,1))) klg wrote: At least that's what we THINK we need help with. A co-worker has been working really hard on this and has previously posted, but shes stumped. Here's our situation - We need a spreadsheet for tracking bonuses. There will be 7 columns - date, policyholder, policy, type, premium, bonus and initials. We'll fill in the date, policyholder and initials columns. We created dropdown boxes for the policy and type columns. Once we choose the Policy, we need the Type dropdown list to be a dependent dropdown, (with only the associated "Types" showing), and for the bonus to fill itself in based on the policy and type chosen. Unless Fire or Auto is chosen, in which case we need it to calculate 2% of the premium we enter. We have worked and worked on this and still cant get everything to work. We have tried the following formulas - =OFFSET(PolicyStart,MATCH(B5,PolicyColumn,0 )-1,1,COUNTIF(PolicyColumn,B5),1) =IF(C5=,PolicyList,INDEX(PolicyColumn,M ATCH(C5,TypeColumn,0))) =SUM(E5*2%) =vlookup(D6,A1:B3,2,FALSE) =VLOOKUP(D5,Policies!C3:D23,2,FALSE) And we have used the following informational links - http://www.contextures.com/xlFunctions02.html http://www.contextures.com/xlDataVal02.html We have uploaded the file to: http://www.savefile.com/files/1333831 Can anyone help us PLEASE? -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help w/Validation List & VLOOKUP
Yes ma'am, I did that and it will calculate the 2% for auto and fire, but it
doesn't populate for the others once a policy and type are chosen. i.e. Life - Term - $25.00 or Health - Long Term Care - $50.00. "Debra Dalgleish" wrote: Make sure you copy the bonus formula from F5 down to F40 klg wrote: woohoo!! almost there!! it will calculate the 2% for auto and fire, but it doesn't populate for the others once a policy and type are chosen. the formula i am using is the one you said - =IF(E5="","",IF(OR(C5="Auto",C5="Fire"),E5*2%,OFFS ET(PolicyStart,MATCH(D5,TypeColumn,0)-1,2,1,1))) any ideas? "Debra Dalgleish" wrote: Your PolicyStart named range is broken. If you redefine it, things should work correctly. klg wrote: Am I just stupid?!?! It's still NOT working!! I have been up literally ALL night trying to work this out. What am I doing wrong? http://www.savefile.com/files/1335704 "Debra Dalgleish" wrote: For the cells where you want a Policy dropdown, the data validation source is: =PolicyList For the cells with a Types dropdown, the data validation formula in row 5 is: =OFFSET(PolicyStart,MATCH(C5,PolicyColumn,0)-1,1,COUNTIF(PolicyColumn,C5),1) The dropdown won't work if Auto or Fire are selected. PolicyColumn should be defined as the entire column, as should TypeColumn. The bonus calculation in row 5 could be: =IF(E5="","",IF(OR(C5="Auto",C5="Fire"),E5*2%, OFFSET(PolicyStart,MATCH(D5,TypeColumn,0)-1,2,1,1))) klg wrote: At least that's what we THINK we need help with. A co-worker has been working really hard on this and has previously posted, but shes stumped. Here's our situation - We need a spreadsheet for tracking bonuses. There will be 7 columns - date, policyholder, policy, type, premium, bonus and initials. We'll fill in the date, policyholder and initials columns. We created dropdown boxes for the policy and type columns. Once we choose the Policy, we need the Type dropdown list to be a dependent dropdown, (with only the associated "Types" showing), and for the bonus to fill itself in based on the policy and type chosen. Unless Fire or Auto is chosen, in which case we need it to calculate 2% of the premium we enter. We have worked and worked on this and still cant get everything to work. We have tried the following formulas - =OFFSET(PolicyStart,MATCH(B5,PolicyColumn,0 )-1,1,COUNTIF(PolicyColumn,B5),1) =IF(C5=,PolicyList,INDEX(PolicyColumn,M ATCH(C5,TypeColumn,0))) =SUM(E5*2%) =vlookup(D6,A1:B3,2,FALSE) =VLOOKUP(D5,Policies!C3:D23,2,FALSE) And we have used the following informational links - http://www.contextures.com/xlFunctions02.html http://www.contextures.com/xlDataVal02.html We have uploaded the file to: http://www.savefile.com/files/1333831 Can anyone help us PLEASE? -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help w/Validation List & VLOOKUP
The formula will only display the amount if the premium has been entered
in column E. Could that be the problem? If that's not required, you can change the formula: =IF(OR(C5="Auto",C5="Fire"),E5*2%,IF(OR(C5="",D5=" "),"", OFFSET(PolicyStart,MATCH(D5,TypeColumn,0)-1,2,1,1))) klg wrote: Yes ma'am, I did that and it will calculate the 2% for auto and fire, but it doesn't populate for the others once a policy and type are chosen. i.e. Life - Term - $25.00 or Health - Long Term Care - $50.00. "Debra Dalgleish" wrote: Make sure you copy the bonus formula from F5 down to F40 klg wrote: woohoo!! almost there!! it will calculate the 2% for auto and fire, but it doesn't populate for the others once a policy and type are chosen. the formula i am using is the one you said - =IF(E5="","",IF(OR(C5="Auto",C5="Fire"),E5*2%,O FFSET(PolicyStart,MATCH(D5,TypeColumn,0)-1,2,1,1))) any ideas? "Debra Dalgleish" wrote: Your PolicyStart named range is broken. If you redefine it, things should work correctly. klg wrote: Am I just stupid?!?! It's still NOT working!! I have been up literally ALL night trying to work this out. What am I doing wrong? http://www.savefile.com/files/1335704 "Debra Dalgleish" wrote: For the cells where you want a Policy dropdown, the data validation source is: =PolicyList For the cells with a Types dropdown, the data validation formula in row 5 is: =OFFSET(PolicyStart,MATCH(C5,PolicyColumn, 0)-1,1,COUNTIF(PolicyColumn,C5),1) The dropdown won't work if Auto or Fire are selected. PolicyColumn should be defined as the entire column, as should TypeColumn. The bonus calculation in row 5 could be: =IF(E5="","",IF(OR(C5="Auto",C5="Fire"),E5*2 %, OFFSET(PolicyStart,MATCH(D5,TypeColumn,0)-1,2,1,1))) klg wrote: At least that's what we THINK we need help with. A co-worker has been working really hard on this and has previously posted, but shes stumped. Here's our situation - We need a spreadsheet for tracking bonuses. There will be 7 columns - date, policyholder, policy, type, premium, bonus and initials. We'll fill in the date, policyholder and initials columns. We created dropdown boxes for the policy and type columns. Once we choose the Policy, we need the Type dropdown list to be a dependent dropdown, (with only the associated "Types" showing), and for the bonus to fill itself in based on the policy and type chosen. Unless Fire or Auto is chosen, in which case we need it to calculate 2% of the premium we enter. We have worked and worked on this and still cant get everything to work. We have tried the following formulas - =OFFSET(PolicyStart,MATCH(B5,PolicyColumn,0 )-1,1,COUNTIF(PolicyColumn,B5),1) =IF(C5=,PolicyList,INDEX(PolicyColumn ,MATCH(C5,TypeColumn,0))) =SUM(E5*2%) =vlookup(D6,A1:B3,2,FALSE) =VLOOKUP(D5,Policies!C3:D23,2,FALSE) And we have used the following informational links - http://www.contextures.com/xlFunctions02.html http://www.contextures.com/xlDataVal02.html We have uploaded the file to: http://www.savefile.com/files/1333831 Can anyone help us PLEASE? -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help w/Validation List & VLOOKUP
You are soooooooo the bomb!! I can't thank you enough!! I hope one day I am
as smart as you - and I mean that sincerely!! THANK YOU!! THANK YOU!! THANK YOU!!. "Debra Dalgleish" wrote: The formula will only display the amount if the premium has been entered in column E. Could that be the problem? If that's not required, you can change the formula: =IF(OR(C5="Auto",C5="Fire"),E5*2%,IF(OR(C5="",D5=" "),"", OFFSET(PolicyStart,MATCH(D5,TypeColumn,0)-1,2,1,1))) klg wrote: Yes ma'am, I did that and it will calculate the 2% for auto and fire, but it doesn't populate for the others once a policy and type are chosen. i.e. Life - Term - $25.00 or Health - Long Term Care - $50.00. "Debra Dalgleish" wrote: Make sure you copy the bonus formula from F5 down to F40 klg wrote: woohoo!! almost there!! it will calculate the 2% for auto and fire, but it doesn't populate for the others once a policy and type are chosen. the formula i am using is the one you said - =IF(E5="","",IF(OR(C5="Auto",C5="Fire"),E5*2%,O FFSET(PolicyStart,MATCH(D5,TypeColumn,0)-1,2,1,1))) any ideas? "Debra Dalgleish" wrote: Your PolicyStart named range is broken. If you redefine it, things should work correctly. klg wrote: Am I just stupid?!?! It's still NOT working!! I have been up literally ALL night trying to work this out. What am I doing wrong? http://www.savefile.com/files/1335704 "Debra Dalgleish" wrote: For the cells where you want a Policy dropdown, the data validation source is: =PolicyList For the cells with a Types dropdown, the data validation formula in row 5 is: =OFFSET(PolicyStart,MATCH(C5,PolicyColumn, 0)-1,1,COUNTIF(PolicyColumn,C5),1) The dropdown won't work if Auto or Fire are selected. PolicyColumn should be defined as the entire column, as should TypeColumn. The bonus calculation in row 5 could be: =IF(E5="","",IF(OR(C5="Auto",C5="Fire"),E5*2 %, OFFSET(PolicyStart,MATCH(D5,TypeColumn,0)-1,2,1,1))) klg wrote: At least that's what we THINK we need help with. A co-worker has been working really hard on this and has previously posted, but shes stumped. Here's our situation - We need a spreadsheet for tracking bonuses. There will be 7 columns - date, policyholder, policy, type, premium, bonus and initials. We'll fill in the date, policyholder and initials columns. We created dropdown boxes for the policy and type columns. Once we choose the Policy, we need the Type dropdown list to be a dependent dropdown, (with only the associated "Types" showing), and for the bonus to fill itself in based on the policy and type chosen. Unless Fire or Auto is chosen, in which case we need it to calculate 2% of the premium we enter. We have worked and worked on this and still cant get everything to work. We have tried the following formulas - =OFFSET(PolicyStart,MATCH(B5,PolicyColumn,0 )-1,1,COUNTIF(PolicyColumn,B5),1) =IF(C5=,PolicyList,INDEX(PolicyColumn ,MATCH(C5,TypeColumn,0))) =SUM(E5*2%) =vlookup(D6,A1:B3,2,FALSE) =VLOOKUP(D5,Policies!C3:D23,2,FALSE) And we have used the following informational links - http://www.contextures.com/xlFunctions02.html http://www.contextures.com/xlDataVal02.html We have uploaded the file to: http://www.savefile.com/files/1333831 Can anyone help us PLEASE? -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help w/Validation List & VLOOKUP
You're welcome! Thanks for letting me know that it's working now.
klg wrote: You are soooooooo the bomb!! I can't thank you enough!! I hope one day I am as smart as you - and I mean that sincerely!! THANK YOU!! THANK YOU!! THANK YOU!!. "Debra Dalgleish" wrote: The formula will only display the amount if the premium has been entered in column E. Could that be the problem? If that's not required, you can change the formula: =IF(OR(C5="Auto",C5="Fire"),E5*2%,IF(OR(C5="",D5 =""),"", OFFSET(PolicyStart,MATCH(D5,TypeColumn,0)-1,2,1,1))) klg wrote: Yes ma'am, I did that and it will calculate the 2% for auto and fire, but it doesn't populate for the others once a policy and type are chosen. i.e. Life - Term - $25.00 or Health - Long Term Care - $50.00. "Debra Dalgleish" wrote: Make sure you copy the bonus formula from F5 down to F40 klg wrote: woohoo!! almost there!! it will calculate the 2% for auto and fire, but it doesn't populate for the others once a policy and type are chosen. the formula i am using is the one you said - =IF(E5="","",IF(OR(C5="Auto",C5="Fire"),E5*2% ,OFFSET(PolicyStart,MATCH(D5,TypeColumn,0)-1,2,1,1))) any ideas? "Debra Dalgleish" wrote: Your PolicyStart named range is broken. If you redefine it, things should work correctly. klg wrote: Am I just stupid?!?! It's still NOT working!! I have been up literally ALL night trying to work this out. What am I doing wrong? http://www.savefile.com/files/1335704 "Debra Dalgleish" wrote: For the cells where you want a Policy dropdown, the data validation source is: =PolicyList For the cells with a Types dropdown, the data validation formula in row 5 is: =OFFSET(PolicyStart,MATCH(C5,PolicyColumn, 0)-1,1,COUNTIF(PolicyColumn,C5),1) The dropdown won't work if Auto or Fire are selected. PolicyColumn should be defined as the entire column, as should TypeColumn. The bonus calculation in row 5 could be: =IF(E5="","",IF(OR(C5="Auto",C5="Fire"),E5 *2%, OFFSET(PolicyStart,MATCH(D5,TypeColumn,0)-1,2,1,1))) klg wrote: At least that's what we THINK we need help with. A co-worker has been working really hard on this and has previously posted, but shes stumped. Here's our situation - We need a spreadsheet for tracking bonuses. There will be 7 columns - date, policyholder, policy, type, premium, bonus and initials. We'll fill in the date, policyholder and initials columns. We created dropdown boxes for the policy and type columns. Once we choose the Policy, we need the Type dropdown list to be a dependent dropdown, (with only the associated "Types" showing), and for the bonus to fill itself in based on the policy and type chosen. Unless Fire or Auto is chosen, in which case we need it to calculate 2% of the premium we enter. We have worked and worked on this and still cant get everything to work. We have tried the following formulas - =OFFSET(PolicyStart,MATCH(B5,PolicyColumn ,0)-1,1,COUNTIF(PolicyColumn,B5),1) =IF(C5=,PolicyList,INDEX(PolicyColu mn,MATCH(C5,TypeColumn,0))) =SUM(E5*2%) =vlookup(D6,A1:B3,2,FALSE) =VLOOKUP(D5,Policies!C3:D23,2,FALSE) And we have used the following informational links - http://www.contextures.com/xlFunctions02.html http://www.contextures.com/xlDataVal02.html We have uploaded the file to: http://www.savefile.com/files/1333831 Can anyone help us PLEASE? -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| If, vlookup, data validation & dependent list | Excel Worksheet Functions | |||
| VLookup return validation list | Excel Worksheet Functions | |||
| Combination of Validation List and VLOOKUP Problem | Excel Worksheet Functions | |||
| Complex Vlookup and List Validation and Nested IF statements | Excel Worksheet Functions | |||
| Validation List and VLookup are ackting strange | Excel Worksheet Functions | |||
Linear Mode
