Adding Columns of Several Worksheets

[jksb]

I have several worksheets that I am working with as inventory control. They have Start Inv; # In, # Out, End Inv. Pretty basic. I have a Summary worksheet that shows the Start Inv. and End Inv (which I've linked). How can I make a link that adds the # In/# Out columns for the month without changing them with in each daily worksheet so that I can show how many total came in during the month, how many out. I've tried paste/special/add but it changes the daily worksheets, which I want to remain the same; I just want a final total on the summary worksheet. I've linked the summary worksheet for Start/End Inv.; it's just those last two columns that are hanging me up (and that wacky date thing

Thank you!!!

Jackie

 

[paulovey]

Jackie,

If I follow you right.

You want to total all the daily worksheet columns and display that total on the summary worksheet..

OK, I think it will be something like

@sum(worksheet1:range1..range2+worksheet2:range1..range2.... etc etc)

Paul

 

[paulovey]

Jackie,

Sorry slight mistake in the forumla

It shoule read =sum(sheet1!range1:range2)

Its been a few years since I've done this

Paul

 

[jksb]

Thank you! Of course I figured it out right after . Then I couldn't get on to tell you all. This is what I used...I didn't use the range; I just copied it to all cells.

=SUM('4th'!E2+'3rd'!E2+'2nd'!E2+'1st'!E2)


Jackie

 

[jksb]

One more question on this...we will be making new worksheets daily, until we have one month's worth. When we copy the worksheet, is there a way to have it automatically add the new worksheet/range in, or will that have to be manual? (Remember, this is just the one totals sheet that needs updating).

Thanks Again!

Jackie

 

[paulovey]

Jackie,

I believe you can add a range including woorksheets, if you are looking at days in a month you will 1 to 31.. So if you have a 32nd worksheet, with a figure of say 0 you are adding to the totals.

In the summary woeksheet the formula would total 1 to 31, and as you add each days worksheet in that would total the lot.

Paul

 

[jksb]

Being brain dead this am, could you show me how that would look? Thanks!

Jackie

 

[paulovey]

Jackie

Probably the best way to do it. Would be to have all the worksheets numbered 1 to 31 and the totals sheet.

And the totals sheets would already have the formula in each cell that would total the columns as you already have, you would just have to ensure that until each daily sheet was used they all had zero vaules in them.

But let me have a play with excel, to see what I can come up with.

Paul

 

[jksb]

Great! Thanks! I"ll play with it too.

Jackie

 

[Dreamboat]

Jackie:

Seems to me that you might be able to put all of your data in one worksheet for each month. Then use autofilter and subtotals feature (under Data menu) to get desired results. Both features can be turned on and off as needed. Autofilter will allow you to pick just one date or a range of dates, subtotals will give you totals for the day and the month.

Best layout: Row one contains column headings, row two contains first record, no completely blank rows or columns separating data.

You should at least try this. Email me if you need some help with it. I'll send you a sample file.


techsupportgirl@home.com

http://www.wedomanuals.com