Help w/Validation List & VLOOKUP

K

klg

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 she’s 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 can’t 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?
 
D

Debra Dalgleish

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)))
 
D

Debra Dalgleish

Your PolicyStart named range is broken. If you redefine it, things
should work correctly.
 
K

klg

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?
 
K

klg

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.
 
D

Debra Dalgleish

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)))
 
K

klg

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!!.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top