Updating Table

[tdenk]

Hello, I really need some help here and I am going to do my best to explain this so it makes sense... I handle catering and marketing at my work, so naturally I track the sales of each customer and company. Right now I have each month on a separate tab and it works great for tracking MTD and YTD sales. However, we recently implemented a Loyalty Points program to our catering customers where each dollar they spend = 1 LP and each LP = .02 discount on future catering when they have accumulated at least 500 LP. What I really need to be able to do is find the easiest way to track not only what they spend, but also how many points they have accumulated, how much $ it equals, how many points they have redeemed, what their total LP balance is and $ amount balance. There has to be some way to create something that will either automatically update or a way that I can lock formulas into a cell next to pivot table. I don't really know what my options are, I am just above basic as far as using excel. Any help would be really appreciated and make my life much easier.



Thank you,


TDenk

[Pepe Le Mokko]

If you could post a sample of your data, someone might have a go at it

[tdenk]

I have attached a sample of my data. Each sales dollar is worth one loyalty point. Each loyalty point is worth .02. As you can see, I have customers that order more than once every month, different customers from the same companies, and new customers constantly. I am trying to create some kind of dynamic worksheet that will allow me to update my table as sales come in each month, and then calculate each customer's yearly loyalty points total.