Excel Help

[Emyn]

Hi,

I have attached a spreadsheet outlining what I want to achieve.

Basically these are the following process steps: -

1) The User enters a claim date.
2) The User can use a drop down/combo box to choose a Scheme. The only schemes which should appear in the drop down box are those where the claim date falls within 'To' & 'From' dates on data list.
3) Once the relevant scheme has been choosen the remaining fields are populated automatically.

Any help you can give me would be most appreciated.

Regards

[Kristin]

Here is how I would approach this:

1) Add a new column (can be hidden if you want) to the Car rental schemes worksheet. The purpose of the new column will be to determine if the scheme is appropriate based on the date entered on the front end. The formulas in the column would look like this:

=IF('FRONT END'!$B$6>=E2,IF('FRONT END'!$B$6<=F2,"OK","NO"),"NO")

2) Use the new column created in step 1 above to help create a new list in rows 16 - 27 (or somewhere on the same sheet) for the drop-down range. This list can be hidden later if preferred. This assumes there will always be 12 choices for the schemes. If new choices are added or existing choices removed, the drop-down list range will need to be modified accordingly.

Here is a sample formula to use for the above:

=IF(B2="OK",C2,"-")

In other words, if the scheme is OK, then show it, if not, show a dash instead.

The input range for the schemes drop-down on the front end would look like this:

CAR RENTAL SCHEMES'!$C$16:$C$27

Regarding the rest of the data flowing in automatically, that would be done with a lookup function. Perhaps someone else can provide the details on that part.

See attachment .

[Kristin]

I could not resist completing the VLOOKUP functions and getting this working. The file is attached.

- Kristin

[Emyn]

Kristin,

Thanks so much for doing that for me. It works great.

Can you tell me how you make the drop down arrow appear when you click on the scheme field?

Regards