If column text(3 columns) meets criteria then input value in cell

[baggypants]

Hi All,

This site has been generous with me thus far and I will see if my luck continues.
In columns B,C and D I have text.

For example: Column B header is appetizer, column c is main , and column d is desert.
There are set menus so when a certain combination is entered I would like Column E to output the price.
EI: soup, fish, pie = 18
soup, lamb, cake =20
salad fish, cake = 17

I have read a lot on the subject but cant seem to wrap my head around it in vba.
I am thinking it is an if, and, else statement but am not sure anymore.
The list would also be dynamic so I would need the code to search and formulate till last empty row.

Any guidance would be great

Thanks in advance!

[Pojzon]

Hey, if you could set a constant price for each meal, for example soup = 6, fish = 7, pie = 5 then you could use a simple vlookup function for finding price and sum a total price. Something like in attachement:

[baggypants]

Hi, Thanks Pojzon for the speedy reply.
Unfortunately I cant fix the items individually they would need to be grouped. As well as in a macro. I originally used a countif and sorted individually, then entered manually , but it takes too long, especially when if comes to charting.

Thanks for the effort though
It is appreciated.

[Trebor76]

Hi baggypants,

Have a look at the attached non VBA solution where the amount in column D of the Input tab is simply looked up from the Combinations tab. This has the advantage of being easier to maintain and can be populated (updated) by any user.

The Combination tab is where you'd input all of the menu combinations so that the amount can be returned to column D of the Input tab. If a combination cannot be found a zero is returned. You may even consider having drop-down in columns A to C of the Input tab so there's no issues around data input error.

HTH

Robert

[baggypants]

Thanks Trebor76,
At first I wasn't too sure about it being non-VBA as most of the spreadsheet is but the more I think about it, the more I am convinced this may be a better way to go. I imagine the code would get quite wordy with a possible 50 combinations. The combinations worked great by the way. I was playing around with the simple "=and" at the beginning and just thought if I could get it to read the value instead of true it would be great, and this does just that.

Thank-you all.

[Trebor76]

You're welcome

If you could mark the thread as Solved it would be appreciated

Thanks,

Robert