Combine or Merge Issue

Greetings:

First, I have machines with both Office 2000 and 2003, so I have both
available if that matters.

Now, the issue:

Our company accountant generates a sheet every two weeks with 401(k)
figures...what the employee contributed, what the employer
contributed, etc.

Each sheet is exactly the same as far as what data goes where, but the
amounts sometimes change from pay period to pay period.

So, at the end of the year, she has 26 sheets. Is there a way to
create a 27th sheet and combine all the data from the first 26?

For example, if in A3 we have a dollar amount that represents what
Employee X contributed, can I somehow add all the A3's together and
have the total of all of them appear in A3 on that 27th sheet?

Of course, I mean to automate this somehow. I realize I could copy and
paste with a + after each C&P, but that would take forever and a day.

Thanks in advance for any help.

James
0
nospam9043 (13)
11/15/2003 7:04:41 AM
excel 39879 articles. 2 followers. Follow

4 Replies
466 Views

Similar Articles

[PageSpeed] 1

James,

Try a formula like

=SUM(Sheet1:Sheet26!A3)


-- 
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"James Monroe" <nospam@lessspam.net> wrote in message
news:uhjbrvo7dlu38budj9noqki05lpjs7p50v@4ax.com...
> Greetings:
>
> First, I have machines with both Office 2000 and 2003, so I have both
> available if that matters.
>
> Now, the issue:
>
> Our company accountant generates a sheet every two weeks with 401(k)
> figures...what the employee contributed, what the employer
> contributed, etc.
>
> Each sheet is exactly the same as far as what data goes where, but the
> amounts sometimes change from pay period to pay period.
>
> So, at the end of the year, she has 26 sheets. Is there a way to
> create a 27th sheet and combine all the data from the first 26?
>
> For example, if in A3 we have a dollar amount that represents what
> Employee X contributed, can I somehow add all the A3's together and
> have the total of all of them appear in A3 on that 27th sheet?
>
> Of course, I mean to automate this somehow. I realize I could copy and
> paste with a + after each C&P, but that would take forever and a day.
>
> Thanks in advance for any help.
>
> James


0
chip1 (1821)
11/15/2003 11:17:35 AM
On Sat, 15 Nov 2003 05:17:35 -0600, "Chip Pearson" <chip@cpearson.com>
wrote:

>James,
>
>Try a formula like
>
>=SUM(Sheet1:Sheet26!A3)

Well, yeah, I know that would work, but I was trying to avoid having
to type formulas for the next month or so..

The formula you wrote above would have to be duplicated 26 times
(Sheet2:Sheet25!A3) (...24!Ac) etc for EACH CELL.

I thought that perhaps there was a function where I could put all 26
sheets in the same folder and say "Combine all the A3's from all the
sheets."

See what I mean?

Thanks for your reply.

0
nospam9043 (13)
11/15/2003 6:16:55 PM
No it does not have to be duplicated, Chip's formula will total all A3
in sheets 1, 2, 3, 4, 5  and so on. And if you are smart you would use 2
dummy sheets
that always will be the first and the last sheet, that way you don't have to
change the formula

=SUM(First:Last!A3)

just make sure when you add a new sheet it will be between the first and the
last

-- 

Regards,

Peo Sjoblom

"James Monroe" <nospam@lessspam.net> wrote in message
news:c1rcrv88j2mv43f1fulnm78l2lp56urpch@4ax.com...
> On Sat, 15 Nov 2003 05:17:35 -0600, "Chip Pearson" <chip@cpearson.com>
> wrote:
>
> >James,
> >
> >Try a formula like
> >
> >=SUM(Sheet1:Sheet26!A3)
>
> Well, yeah, I know that would work, but I was trying to avoid having
> to type formulas for the next month or so..
>
> The formula you wrote above would have to be duplicated 26 times
> (Sheet2:Sheet25!A3) (...24!Ac) etc for EACH CELL.
>
> I thought that perhaps there was a function where I could put all 26
> sheets in the same folder and say "Combine all the A3's from all the
> sheets."
>
> See what I mean?
>
> Thanks for your reply.
>


0
terre081 (3244)
11/15/2003 6:36:21 PM
On Sat, 15 Nov 2003 13:36:21 -0500, "Peo Sjoblom" <terre08@mvps.org>
wrote:

>No it does not have to be duplicated, Chip's formula will total all A3
>in sheets 1, 2, 3, 4, 5  and so on. And if you are smart you would use 2
>dummy sheets
>that always will be the first and the last sheet, that way you don't have to
>change the formula
>
>=SUM(First:Last!A3)
>
>just make sure when you add a new sheet it will be between the first and the
>last

Ah....I see now.

Thanks very much for the detailed explanation; I misunderstood Chip's
meaning.

Looking back at it now, I see where he was correct and I missed it.

Thanks to both of you.

James

0
nospam9043 (13)
11/15/2003 11:21:41 PM
Reply:

Similar Artilces:

Problem with Catalog Merge and Master pages
I have a four page document set up that has a 2-page master page. The master page has mirrored margins with the inside margin at 0.5" and the outside margin at 0.125". I set up a catalog merge with the first page having 1 across and 4 down. When I go to merge the data, all my pages are numbered 1 and they all have the same margins instead of the mirrored margins for oposite pages. How do I get the catalog merge to merge across the pages correctly? Are you creating a new publication in step three? (2007) If you do this and then add the page numbers all will be well. Print me...

Merged Cell Character Limitation?
Is there a limitation to the number of characters that can be entered into a merged cell? I have a document which has a merged cell consisting of 12 rows and 9 coulmns (for text entry). I set the cell to word wrap. However the word wrapping ceases after roughly 1500 characters, and the text is no longer visible to the user in the cell (even though there appears to be room). If this is a limitation, is there a known work around? Hi there's a maximum of 1024 characters per cell. Though you can increase this by manually adding line breaks with ALT+ENTER -- Regards Frank Kabel Fra...

Set Cell Padding on word tables with merged cells
Dear Experts: Below macro sets certain padding values for the selected table. Regrettably it fails to work on tables with merged cells. Have you got any idea how to re-write the code so that tables featuring merged cells also get worked on? Help is much appreciated. Thank you very much in advance. Regards, Andreas Sub SetPadding Dim myCell As Cell Dim myRow As row Dim myTable As Table Set myTable = Selection.Tables(1) For Each myRow In myTable.rows For Each myCell In myRow.Cells myCell.TopPadding = CentimetersToPoints(0) myCell.BottomPadding = C...

Mails Combined
HI, I mistakenly delete all mails from Inbox and then found them on the deleted Items and selected all and ask to move folders back to Inbox. the problem is all mails were combined into one email and moved back in to Inbox as a single email. How do i go back to the original version. How do i uncombined all the mails in to individual mails from the right recipient? Please help ASAP.. Submitted using http://www.outlookforums.com ...

Issues with GLAccounts
1) It would be great if GP did not allow deletion of an account if there was a budget amount attached to it. We are in the process of changing our Chart of Accounts and as a result a very large number of accounts have been made inactive. When we closed our GL at year end, many of these accounts were automatically deleted (without notification to the user, which is another HUGE problem) even though there were budget dollars attached to them. 2) GP should NOT delete inactive accounts during year end close without notification to the user - a report should be produced of all delet...

merge/combine workshhets
I need to merge about 75 worksheets into one spreadsheet on a regular basis - all have exactly the same format/layout etc back can vary in the number of rows Try this Jeff http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Jeff224" <Jeff224@discussions.microsoft.com> wrote in message news:C6790506-2888-4F4D-80EA-BB0D9F9B27A2@microsoft.com... >I need to merge about 75 worksheets into one spreadsheet on a regular basis - > all have exactly the same format/layout etc back can vary in the number of > rows Or http://www.rondebru...

Outlook and iTunes
I may be getting an iPhone. If I do it will replace an HP iPaq WM based PDA. I understand that I'll need to install iTunes in order to move data back and forth from the iPhone. And given that it will sync with Outlook I'm wondering if there are any problems or issues with this sort of setup? For the most part, it is as problem free as other sync programs. Check Apple's iPhone forum for Mail, Calendar and Contact issues at http://discussions.apple.com/forum.jspa?forumID=1144 - this will give you an idea of the issues. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http:/...

Finding the combination that appears more times
Hi everybody i have a list on excel 2007 that displays the purchase of items on columns A:E, each column showing one (1) item I want to find what combination of items appears more times, especifically, which combination of four (4) is the favorite mix eg: A B C D F A C D E F B C D E F B C D F Z in this example, the winner would be (B, C, D, E) as it appears 3 times the main problem is that I have over 1,000 rows and trying with =SUMPRODUCT(COUNTIF(A1:E1,$N$1:$R$1)) por each combination would take forever... is there a simpler, quicker ...

Combining Publications
Can I combine two different publications into one? Suzi wrote: > Can I combine two different publications into one? ========================================= Personally, I would simply open two instances of Publisher (one document in each) / tile the two screens...and copy/paste. Maybe the following link will be useful: Combining Publisher Documents http://tinyurl.com/2lpj5w -- John Inzer MS Picture It! - Digital Image MVP Digital Image Highlights and FAQs http://tinyurl.com/aczzp Notice This is not tech support I am a volunteer Solutions that wo...

Mail Merge labels, Figured out ! (at last)
While posting a question, I figured out how to make this work! Hope it helps you, too. OK, I've read the posts and tried several things, but I STILL get 1 record 30 times on 1 sheet of labels, avery 5160. - I have the template set up so that I only see 1 label on the screen. I merge to the printer and get 30 labels exactly the same. After selecting the Excel worksheet, I add the fields, then Print Merge, which is the same as hitting "Print." The Print box comes up and here's the key: Print Options: "Multiple Pages per Sheet." (voila) - the default (it cam...

Combining 2 Excel (xls) files
I have 2 xls workbooks with different sheet names and both with different range names. I would like to combine them both into one workbook so that I don't have to re-create all of the range names. Is there a way to do that? -- LAF ------------------------------------------------------------------------ LAF's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=9656 View this thread: http://www.excelforum.com/showthread.php?threadid=346017 Hi LAF- Open both files (let's call them A & B, and assume A will be your "combined" file). Working i...

My Data Base will Not open in Publisher Mail Merge
When I try to Merge a Data Base into a Publisher piece heres what i get: The data source you have chosen contains more than one worksheet or table. Please enter the name of the worksheet or table that contains the information you want to merge. I have NO IDEA what its asking me for. Publisher Help doesn t seem to have the answer, the co. that I bought the Data from has no idea. She sent it in CSV format if that helps. Thanks jba195@yahoo.com Sounds like it has move than one worksheet in an Excel file. Make a backup copy, then open it with Excel, if it has more than one worksheet delete...

How to Combine Data in Different Columns
Hi Everyone I need help in this issue. I have tried many ways though some wer successful but they were not efficient. I have say 5 columns of data and the data could be on the same rows o all 5 columns or on different rows hence if I need to combine all th data of these 5 columns, I would have to sort or filter each of the manually and copy and paste to a different worksheet or column. This i very tedious and time consuming. I also tried to write a macro to loo for data in one column then put them into another column but i was no successful. However, experts like you guys shouldn't have ...

Merging databases
Sorry if this is in the wrong place - I am very new to this site I am using Acess 2000 and I am not that brilliant at it Is there any way to merge two of the sample databases which comes with Access together - I would like to have the Inventory Control and the Order Entry databases as one database - can this be done? There is no data in the databases yet Hope someone can help and thanks for looking Marvic That really depends on what you mean by "merging two databases". Just like Word or Excel, you could just copy what's in one into another. Unlike Word or Excel, you ty...

how to delete and insert in a two cells merge using Access 2003?
I want to merge a column of names to a column of 10 digit mobile numbers; but in doing so I need to delete "0" (the first digit) before adding "44" to the front ot the remaining 9. I know of the "=A1& " "&B1" formula, but it does not do the necessary deletion and insertion I need to do. Any help? Hi, Try this in cell C1 =A1&SUBSTITUTE(B1,0,99,1) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Dubois2U" <Dubois2U@discussions.microsoft.com> wrote in message news:F954F2E7-44DD-49F0...

combinations
I have a finite number of values that represent length (right now I have less than 20 values). Can Excel take these values and list all possible combinations of the values? Any pointers appreciated. Stephen R. Stephen, No. There are 2,432,902,008,176,640,000 possible ways to combine 20 different values. That is, unless you have another limitation, like only using 4 at a time.... HTH, Bernie MS Excel MVP "Stephen R" <nospam@nospam.com> wrote in message news:u9ZiYhOeFHA.1456@TK2MSFTNGP15.phx.gbl... > I have a finite number of values that represent length (right...

merging data
Hi all:) I'm new to this forum and to to Excel (2000) and not sure that I'm asking this question correctly but here is what I'm trying to do. The data on my work sheet is pasted from another sorce and includes information: name, ref #, time, date. I use this information looking up the status of an order and enter a "status note" in 5th (last) column. As the day progresses, the source that the data comes from is copied and pasted under the current list I already have going and much of the data on the lower list is a duplicate. How can I merge the to list so that I hav...

Mail Merge and Stamps.com
Has anyone been able to do a mail merge from SFO client and add postage to their mailing? I am receiving an error saying that either one of the required fields is missing (checked, all there) or the data source is not compatible with Stamps.com. ...

Combine 2 Charts 2007 Excel
I have a line chart (looks like an S curve graph) with two rows of data below & this chart looks exactly like I want it to. Period % Complete Dec-04 7% Jan-05 9% Feb-05 10% Mar-05 12% Apr-05 14% May-05 19% Jun-05 22% Jul-05 23% Aug-05 24% Sep-05 26% Oct-05 28% Nov-05 32% Dec-05 36% Jan-06 40% Feb-06 45% Mar-06 50% Apr-06 55% May-06 59% Jun-06 63% Jul-06 66% Aug-06 71% Sep-06 75% Oct-06 78% Nov-06 81% Dec-06 85% Jan-07 87% Feb-07 89% Mar-07 91% Apr-07 93% May-07 94% Jun-07 94% Jul-07 95% Aug-07 95% Sep-07 95% Oct-07 95% Nov-07 95% Dec-07 95% Jan-08...

Known Outlook issue?
Background information- Docs Open 3.9.6 (File management software) Office XP (including Outlook) MS Exchange Server Issue: In the previous version of Outlook, you could attach a Word document to an email by generating it from DocsOpen (right-click on the document and select "Mail") without Outlook running in the background. You now have to open/launch Outlook before doing so. Otherwise you generate a "permissions" error. Is this a known problem and if so, will there be a service pack that will address this issue? (NOTE: It's more of an inconvenience than an iss...

Slow printing mail merge
Hi, I'm hoping to find a solution to slow printing from Publisher 2003, 4-up postcards with thousands of name/addresses being merged onto a very simple Publisher doc (each 4-up sheet spools at about 12K in size). I am printing through a Fiery print controller on a Xerox 252 using the PCL driver (PS was no help so far). I have already applied changes recommended in KBA 891904, which did speed up the processby sending in larger batches. I also checked the setting changes shown on KBA 279385, but saw no difference. Spooling jobs is still taking an unusually long time, though. Any thoug...

combining worksheets
I have a number of identically structured worksheets. I wish to treat them as alternative realities, assigning a probability of occurrence to each. I want to combine all this into a probabilistically "average" worksheet. Anybody have a good way to do this? Thanks mike Mike - You can set up one spreadsheet with the spreadsheets listed and the weighting you want to assign to each. Then set up another spreadsheet that will contain the results. Copy any header / row information from one of the spreadsheets, then for each cell you want 'averaged', enter a f...

Issue with OPOS drivers for STAR TSP643D
I'm having an issue with an STAR TSP643D receipt printer. I download the opos drivers from the STAR website and installed them, setup the printer and setup the printer in the RMS. However, I'm getting an unknown error when RMS tries to print. Tried to install the windows driver... but hangs RMS Store Admin and Store POS at startup. Has anyone managed to install one of these printers with RMS 1.2 (with service pack) -- IT/Software Dev Manager 1) Uninstall the windows drivers - you don't want them installed if you are using OPOS. 2) Make sure there are no windows printers or...

merging two worksheets #3
I have two worksheets, one is a catalog of data (fields of party names, party number, date of party and party hosts) - next worksheet contains fields of participants and which party number they signed up for. Is there a way to merge the two sheets to create a 'receipt' for each participant.? ie, The Smith family signed up for cookie decorating, book club and surf-n-turf dinner. Thank you. You might be able to us a vlookup if you have a seperate line per course for the "Smiths"- if you have Smiths then course 1,4,8 it's more difficult i.e you want the info to be s...

excel comment issue
Hi, I'm having a problem viewing comments in all of my Excel 2000 worksheets. Everytime I mouse over the data cell with the comment indicator it comes up black. The only way I can view the comment is selecting edit comment. Any suggestions? Hi Jerry, Searching the archives http://www.mvps.org/dmcritchie/excel/xlnews.htm or directly to: http://groups.google.com/advanced_group_search?q=group:*Excel* is always a good place to start. Not too surprising to find a recent posting <grin> http://google.com/groups?threadm=etAhFlkTEHA.1508%40TK2MSFTNGP11.phx.gbl but if you ...