Dependent Dropdowns - Large Data Sets

Hi All,

I am trying to create an expense form in excel for staff that travel globally. The expenses are different depending on the country, city within the country and whether it is an overnight stay or not.
I have run into a limitation with Dependent dropdown's where the number of lists I would have to create will number the thousands due to all the combinations that i would need to include. The figures selected from the data validations will be used to calculate the total expenses due.

Fields that I have:
Country=> City=> Overnight Rate=> Reduced overnight Rate=> Half Day rate=> full day rate=>

I was using the dependent drop-down to allow users to reduce the number of options they are have to choose from as they progress through the different fields. There are 98 countries on the list with a further 300 cities.

Example Table content:
Country City Overnight Reduced Full Day Half Day
AUSTRIA Bregenz 1,000 500.00 300.00 50.00

I have looked at combining variations of Data validation, If and vlookup functions but have not been able to successfully come up with an alternative.

Any advise would be appreciated.

Cheers

Dara

Hi Ali,

Sorry I missed your reply. I have uploaded my worksheet. The first section (To do with mileage) I put together myself. Its basic and not particularly tidy but worked for that scenario. Below that is the larger section, I have included a table with a few of the countries and rates which my mileage solution will not work for.

Any support would be really appreciated.

Cheers

Dara

Time for VBA & UserForms ?????? or MS Access