combining week sheets to a month end sheet

**kirstenniemann** · 02-21-2011, 11:44 PM

I have looked all over this forum to try and figure this out on my own. Please help. I have 5 sheets for the different weeks in the month and one for the end of the month. What I want is for the five weeks to transfer to the month end sheet and to be able to sort it accordingly to needs. Also can you make it put different dollar amounts in say the E column based off input in the d column. For example if D has Port Orchard then E=$25.00, but if D has Shelton then E=$35.00, and how many perameters can I put on the D column if this is do-able. Please dumb it down for me, as I am very new to excell. Thanks in advance!

**MarvinP** · 02-22-2011, 01:11 AM

Hi kirstenniemann and welcome to the forum,

We really need to have a sample of your data to help much. To attach a sample file click on the "Go Advanced" below the message area and then the PaperClip Icon that will appear above the message area. This will open a window, allowing you to attach a file.

We simply need to see how you have your data on your worksheets to help much. Also if you could give an explaination of what you are trying to achieve, it really helps us focus on your problem and giving you a good answer.

**kirstenniemann** · 02-22-2011, 01:48 PM

Ok so I attached the document (hopefully). I figured out how to do the amounts based off the location. but still need to have the weeks transfer over to the month end, and sort by the type of service and by date with in the type of service.

**MarvinP** · 02-22-2011, 02:33 PM

Hi kirsten,

I have attached a spreadsheet with a better way. Put all you data on a single sheet and let Pivot Tables do all the work for you. To show you how I've created some data and showed you how to do it.

Also, you can have a small table and do a VLookup for the city prices. See the example. That formula you had was way too hard.

The Pivot Table will do running totals so you won't need them. Also you can simlpy click on the date filter and select the month you are calculating

You won't need a workbook per month as this will do the whole year or multiple years.

You can filter out all cities but a few or services to see how much you bill/make on tha specific one.

See the attached where I hope you will see that Excel can do all the work and all you have to do is add data.

See if this works for you.

**kirstenniemann** · 02-23-2011, 12:28 AM

Ok that is amazing! The VLOOK is so much simpler, however I guess I did not explain the other part well enough. I enter in the weekly data. and for the month end it compiles all data from the five weeks and creates a master llist for month end. What I currently do is copy week 1 and paste on month end copy week 2 and paste on month end, etc. Then I have to go through and remove any thing with the service type cry(only on month end) and make a seperate list of those in order by the date. What you did was really amazing though! I really appreciate the help.

**MarvinP** · 02-23-2011, 01:12 AM

I think that can be done using Pivot Tables. PTs can show stuff by weeks and can show only data of a certain type (cry?).

I guess I need a partially filled in sample of what you do to better explain it. Putting all your data in a single table instead of 4 or 5 different sheets is obvious after you can see what Pivot Tables can do.

Can you give a better sample of your data and be more specific on what your need is that isn't accomplished by what I did?