IF functionality issues

**loner2003** · 06-13-2013, 09:58 AM

Hey guys, I am a novice excel user who is trying to learn and apply the functionality of the IF function. For this project I am working on, I would like to have data from sheet one which is highlighted in the attached doc. pulled and sorted into sheet 2 when a user inputs a certain criteria in the cells A2 to D2.What I am thinking is that there will be a drop down tab for each criteria with all the information from the table. The user will select policy prefix and coverage and fill in the date. I personally am not sure if the drop down tab is the best way of doing this so I’m open to suggestions. From there after all that information is plugged in the criteria on sheet 2 from cells A12 to A15 will be filled in automatically with the corresponding information from sheet one data. Pretty much this is to short allot of data later and just want to get the processes started of how this would work. I believe the IF function would work to process the desired results to be filled in from A12 to A15 but I just can figure out how to set the formula or if it is even the correct way of doing this. Please send and suggestions or help. I’m just a intern trying to make this project work and any help would be greatly appreciated.

**ChemistB** · 06-13-2013, 10:32 AM

Questions:

You want dropdowns for DOL and Policy Incept Date (mm/dd/yyyy). I assume one of these is Treaty Expiration Date. Is the other Treaty Effective date (I don't think so as that's one of the terms you want returned in B12:B15)? Sure you want these as dropdowns? would be easier if they are manual entry, I think. Do you want exact matches to these dates or >= or <=?

You have multiple prefixes in col F of sheet1. Would multiples have to be choosen or if either is picked, pull that info? Your prefixes in column A of sheet1 don't even include 3RV.

You don't have Coverage on every line of your table in Sheet1. Is it just incomplete or am I missing something?

How many results would you expect to return on Sheet2 once you've selected from your dropdowns?

Comments: This is definitely not something that can be done with IF (although it might be used somewhere in the formulas). The dropdowns can be done with DataValidation>List. Make unique lists on a separate sheet and name them. Depending on your answers to the above questions, the rest can probably be done with some helper columns and INDEX/MATCH.

**loner2003** · 06-13-2013, 11:01 AM

Hey thank for responding! ill answer the questions below in order

1. For DOL and policy inception date my boss is asking for manual input of these dates. If you suggest drop down tab i will just do it. Also when it reads if it does not have to be exact but fall on or between those dates as you have described.

2. The DOL will be a date that help determines what treaty will be pulled and used in the results for the items in a12 to a15

3.Also A12-a15 results will not be drop downs just information that is being derived from the fill in information

4.As for the prefixes only worry about the ones in yellow. I work in insurance and they are there for other reasons besides this project. Just focus on highlights. Also i would like them to be selected but if you think there is a better way let me know.

5. For the coverage’s i have be told it can be the policies can be referenced back to those five or six individually without have to mark them all. I thought you would have to have them next to the corresponding item but i have been told otherwise.

6. As for results i just expect one corresponding treaty name, treaty eff date, reinsurance limit and claims contact info.

I personally think this project is more along the line of access but my company does not carry it. I have no idea where to go with this so anything will help alot! If you have more questions or i did not answer them clearly enough let me know.

**ChemistB** · 06-13-2013, 11:34 AM

So the DOL and Policy Inception dates have to fall between the Treaty Effective Date and the Treaty Expiration Date?
I still don't see how Excel will know that rows 2:16 of sheet1 relate to "Auto Physical Damage"?

Here's what I have so far. I ignored "coverage" since I don't know how Excel would know.

I created a worksheet for dropdown lists (called Lists) you can hide this.
I used data validation for dropdowns in sheet2 A2 and D2 which refer to the lists in Lists.

I created a helper column in Sheet1 Col K (you can hide this column too)
It looks at each row and sees if it matches the criteria from sheet2. In K2 for example

=--AND(D2<=Sheet2!$B$2, E2>=Sheet2!$B$2,D2<=Sheet2!$C$2, E2>=Sheet2!$C$2, F2=Sheet2!$A$2)

Then in B12 of Sheet2 copied down

=INDEX(Sheet1!$D$2:$J$16, MATCH(1,Sheet1!$K$2:$K$16,0),MATCH(A12,Sheet1!$D$1:$J$1,0))
Let me know what assumptions I made are incorrect. A working example would be very helpful.

**loner2003** · 06-13-2013, 12:34 PM

Wow, that is really good start. In this example the policy inception date will have to fall between the date listed on sheet one. In this case DOL is a dud, as I expand this doc later on the dol will matter and not the policy inception date will be the dud. Only worry about policy inception date falling between the dates on sheet one for now. I will figure out the rest once i get this got this working right. As for coverage, Auto physical damage will be related to all of these items i have listed now. As i add to this doc it will change also. Would you recommend just putting a column in sheet one list what each one is or just keep referencing them how we planned to. I also have no working document of anything similar to this. This is the first of its kind at this small company and im really sorry for the troubles. I would also like to truly thank you for helping me out. You’re a saint you really are.

**ChemistB** · 06-13-2013, 12:57 PM

If you have a logical thought process to assign Coverage, (i.e. certain prefixes are related to certain coverages) then we can work with that.

By working doc, I just mean one in which you set up specific variables and manually explain what the result would be and why. You'll also need to come up with criteria as to when you would look at DOL and when you'd look at Inception. (this is probably when you'd start using that IF statement).

It's best if you give all the information up front rather than gradually adding more factors. Certain solutions may only work for certain factors and then we'd have to start all over at some point.