Assigning value to text

Hello!

I'm new to Excel Forum although I've been reading everyone's hints and tips for quite some time!

I'm going to do my best in explaining what i'm trying to do and hopefully someone can help me...

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!

I think there are better ways to do what you want, than they way you are going about this.

I suggest

1. you create a table of data that you put in all your invitations, events, costs etc
2. create a lookup table to add the price of each event into the table you created above
3. If you want to overwrite the amount, you can just type it in over the formula.
4. then create a pivot table to do the reporting you want. Pivot tables are very flexible, and once you get going, you should be able to do a lot more than you are thinking now, and you also can change your mind and slice and dice the data in a different way.

Here is an example attached. Note there are 2 sheets, data and report.

Hope you can make some progess from here

Thank you for your reply Mallycat!

I see what you're saying and the pivot table is a great idea! But i have a few concerns...

Since each client may be invited to all 5 events, we need to have the event names in the top row.

I need to classify who has been invited and who has accepted with a value not with a "yes" or "no". Say for example they have been invited to all five activities, the total - $2000 dollars will show up in the field "Total Prepared To Spend" But if they only agreed to attend three event, i need to change the field to show "$1000" in the field "Actual Amount Spent" while the total $2000.00 stays in the field "Total Prepared To Spend".

In the example you provided, I can't actually type in the total instead of the formula because say for example that the Spa and Golf rates are a constant, Spa = $400 and golf's = $500... Writing a "YES"under Golf and a "yes" under Spa should total $900.00. I need to be able to write "yes" in one column, "Yes" in another" and "$57.90" in column C (for the dinner column where the amount spent will differ each time) but i would still require a grand total in the total column... (I haven't figured out how to do with with vlookup)

I'm going to look into pivot tables to see how i can get them to help me out...

Sorry if I'm not explaining this well... and thank you for your help!!