Adding a value to data validation list depending on what text is returned

[Dexel]

Hello,

Tried googling for an hour and couldnt find what i needed.

So im making a spreadsheet for a hotel, wich im hoping will produce a number depending on how many guest, if flight is included, if the adventure pack is included and if food is included or not.

So far ive defined names for the different ones, and now id like to add a number to the text.

For example "Number of guests = "3", then add flight cost of 1000 * 3 + if adventure pack=Yes then add 1000 * amount of guests, output number to cell X"

Or split it into several numbers and i can just add them togheter in the end.

"number of guests = "3", (Base cost * 3), output number to cell X"
"adventure pack = (number of guests * 200), output to cell Y"
add cell X + Y = Z

Any help would be appriciated

//dexel

[gmr4evr1]

Something like the attached?
Dexel Vacation Package Cost.xlsx

[protonLeah]

deleted....

[Dexel]

Dexel hotel cost.xlsx

Something like that yes, but i want it in a dropdown list so that the hotel manager can easily use it.
I dont have alot of excel knowledge, and the hotel manager is basically illiterate, both when it comes to reading and computers.


Regards
Dexel

[gmr4evr1]

What part(s) of it do you want in a drop down list?

[Dexel]

Ive tried to work on it a bit, right now id like field F2, H2 and J2 to say YES/NO, and do a calculation depending on B2 * certain amount and outup it into the respective 5 of each column.

Also im not very happy wit the =CHOOSE setup for B3, not sure if its possible to make it in a better way.

//dexel

[gmr4evr1]

Um, it's in another language that I don't know, not sure if that will be an issue for me or not. I am confused as to what you want where. You say you want F2, H2 and J2 to say YES/NO....on which sheet? The data you have on both sheets in your example doesn't match what you are saying here so I'm kind of lost. Example, on the Blad1 sheet, F2, H2 and J2 have numeric values in them, so does F2 of the Data sheet. H2 and J2 of the Data sheet are blank.

[newdoverman]

I tried to translate your workbook using Google then tried to make the calculations (that made sense to me for the various categories). I made the assumption that the Food costs varies directly with the number of guests and the length of stay. I also made the assumption for the Adventure Pack that it was a per day per person charge.

I made a lookup table for the number of guests to return the value for each number of guests and placed this formula in B3 instead of the long CHOOSE formula.

Please Login or Register  to view this content.

I think that you can easily make changes where my assumptions are not correct.