Sum values from different worsksheets

[txoxmxaxsx]

Hi,

Im working with an excel book that has 13 worksheets. One of the worksheets has a summary of all our POs and the other 12 are the months.
On the summary sheet I have a colum labeled "Surplus". Every month we bill the customers an x amount(may vary from month to month). What I need is to add the billed amount from each month and take it from the PO total.

The trick is that not all the POs go through the entire year so I may get the #N/A and my vlookup wont be of any use.

Thanks in advance

 

[Domski]

Hi,

My first suggestion is to reconsider the layout of your data. Do you really need to have a sheet for each month? For the purpose of analysis you would be much better having all your transaction data on one worksheet.

Dom

 

[txoxmxaxsx]

Yes it needs to be this way.
What we do is complete the billing data on the current month and send that summary to the customer.
So rearranging data is not an option

 

[Domski]

Rearranging the data is almost always an option. Creating summaries from a single source of data is quite straightforward compared to trying to analyse data that is stored across numerous worksheets.

Just my opinion of course.

Dom

 

[txoxmxaxsx]

How would you suggest me to do so?

As I mentioned above each month a given PO may or may not appear and this way it is possible to use the current month worksheets as the billing summary we need to send our customers.

 

[Domski]

It's quite often as straightforward as having a list of transactions. I would assume each customer and PO has a reference you can then use this combined with the date of the transaction to produce summaries based on a point in time. The likes of pivot tables can easily be used to summarise the data and the likes of autofilter can be used to extract particular lists of transactions.

It's not easy to be more specific without seeing your data and as I say it's just my opinion but if it's possible you will find your data much easier to work with in the long run.

Dom

 

[txoxmxaxsx]

Thanks for your answer.
Tried using pivot tables but wont do the trick. Will see how to work this out.

Thanks again