Assigning Values to Text...

Hello!

I'm new to Excel Forum but I'm hoping you Excel Geniuses can help me!

I'm going to do my best in explaining what I'm trying to do.

I am creating a spreadsheet that is able to track the events that our clients have been invited to AND in a separate column, I need to track who actually attended in order to get a total of how much we are prepared to spend on the client, AND how much we actually spent, to date, on the client.

I need to give each event a value, so if “Jim” Says “Yes” to Golf, “yes” will equal “$400.00”. But if Jim says Yes to the Golf ($400.00), Spring Event ($100), and Executive retreat ($1400.00)” The “Yes” in those three columns will be calculated in a side column ($400+$100+$1400). Which means that the “Yes” must have a different value assigned to it, depending on which column it calls under.

The other important functions that my spreadsheet should have are as follows:

1) I need to be able to manually enter an amount, if need be, instead of a “yes’, and have the total be calculated all the same, in the total column.

2) As mentioned, I need to be able to have one column that keeps the total of how much we have projected to spend on the client (if we invite them to 4 events, and the total of the four =$2000.00, that would be our projected amount)… but I need a separate column that holds the amount spent on the client to date (If we have “Yes” next to Golf, spa, and trip, meaning we have invited him for the three events and are prepared to spend $1000 on him, but he only accepted to the Golf, I need one column to keep the total $1000.00 and a side column to now read “250”, for example. And if in a few months he accepts to the Trip, I need to be able to track that he accepted, and the “Total Spent” should now be “750” while the “total projected amount” should still be $1000.00. Is that possible??

Please see the attached Test-Formula2 to see a quick example of what I would like the end product to look like.

I created something using vlookup (Please see attachment - Test-formula), where instead of “yes” I put in a different character for each event, where each character returns a different value. But if I leave any of the cells blank a “n/a” error shows up… Another problem is that I can’t enter a value into the cell.

Is there a macro or a formula I can use in order to get all my needs met?

Thank you so much for even taking the time to read my post!

Any help will be greatly appreciated!