Help with linking columns of multiple sheets

Version: 2004
Operating System: Mac OS X 10.6 (Snow Leopard)
Processor: Intel

I am clueless when it comes to Excel and only use it once a year for taxes, because my accountant likes it. <br><br>Anyway I add in all my expenses on a monthly basis and treat it as a worksheet (i use the Sum function to total things like postage, miles, software, etc). I want to have a final worksheet that shows all 12 months of 2009 (the totals of each spreadsheets and I want to be able to sum a total of any category like postage or mileage in a yearly total. I am able to cut and paste the totals of each month into the final year end worksheet, but when I try to sum the totals of all 12 months, I get a really goofy figure. I assume the sum function has been pasted in with all the data from each individual monthly worksheet. Anyway, I can add my rows manually, but I am bad at using a calculator. is there a function in which I can take the totals of individual worksheets and bring them into a global &quot;totaled: worksheet with all that data &quot;summed&quot; for the end of the year. I hope I am asking my question correctly. <br><br>Cheers
0
nelsonmay
3/11/2010 9:23:54 PM
mac.office.excel 1146 articles. 0 followers. Follow

4 Replies
484 Views

Similar Articles

[PageSpeed] 29

Never "assume" in Excel, you get into too much trouble :-)

Click in the cell where the total appears, and LOOK in the Formula Bar to
see what formula appears there :-)  If the Formula Bar is not displayed, go
to the View menu and turn it on.

Unless you can look to see what formulas you have in your cells, it's not
safe for us to help you any further, because we have no way to see what is
happening.

If you copy a cell, then go to the destination and RIGHT-click, you will see
the "Paste Special" menu item.  Choose that and you will be offered "Paste
Link".  That pastes a formula that copies the entry from the source cell,
and that's what you should be using here, so when you add new entries they
show up in your annual sheet.

We would be delighted to help you with all of this in here, but there's
quite a lot of learning involved if you are a "once a year" user.

Far better, I suggest, to send simply the raw data spreadsheet to your
accountant.  If he likes Excel, he can get what he needs from that in a few
seconds.  So could you, if you knew how, but it might take you several days
or weeks to learn how.  What are you paying the man for?  :-)

Hope this helps


On 12/03/10 8:23 AM, in article 59bb5081.-1@webcrossing.JaKIaxP2ac0,
"nelsonmay@officeformac.com" <nelsonmay@officeformac.com> wrote:

> Version: 2004 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel
> I am clueless when it comes to Excel and only use it once a year for taxes,
> because my accountant likes it.
> 
> Anyway I add in all my expenses on a monthly basis and treat it as a worksheet
> (i use the Sum function to total things like postage, miles, software, etc). I
> want to have a final worksheet that shows all 12 months of 2009 (the totals of
> each spreadsheets and I want to be able to sum a total of any category like
> postage or mileage in a yearly total. I am able to cut and paste the totals of
> each month into the final year end worksheet, but when I try to sum the totals
> of all 12 months, I get a really goofy figure. I assume the sum function has
> been pasted in with all the data from each individual monthly worksheet.
> Anyway, I can add my rows manually, but I am bad at using a calculator. is
> there a function in which I can take the totals of individual worksheets and
> bring them into a global "totaled: worksheet with all that data "summed" for
> the end of the year. I hope I am asking my question correctly.
> 
> Cheers

This email is my business email -- Please do not email me about forum
matters unless you intend to pay!

 -- 

John McGhie, Microsoft MVP (Word, Mac Word); Consultant Technical Writer,
McGhie Information Engineering Pty Ltd, Sydney, Australia.
Ph: +61 (0)4 1209 1410; mailto:john@mcghie.name


0
John
3/12/2010 1:22:25 AM
Thanks John. I actually pay less, because I have everything neat and tidy in a spreadsheet. Most people turn in a box of receipts. I will probably just go the cut and paste route and add with a calculator. I have the weekend to do this. I was just trying to get ahead of the 8 ball. You know, I would love to learn XL, but I am a cinematographer and editor. I can do complicated things, I just don't like long stings of numbers. I will mess up. I will never be a line producer. <br><br>Cheers
0
nelsonmay
3/12/2010 1:49:58 AM
BTW, here is my basic formula =SUM(B1:E1) etc.  I just highlight on my first number and drag to my last number.....while all cells in between are highlighted and press the sigma button. There is my sum.
0
nelsonmay
3/12/2010 1:52:39 AM
Yeah, I'm with you...  Numbers should be left to those people who like that
sort of thing...

OK, where you have =SUM(B1:E1), copy that.

Click where you want the subtotal and choose Edit>Paste Special> "Paste as
Link".

Do that for each one.  Then at the bottom of the column, put another =SUM
statement to add up the totals.

You may also like to play around with a PivotTable.  These are
purpose-designed for what you are trying to do.

Have a look at this:
http://www.wikihow.com/Create-Pivot-Tables-in-Excel

Cheers


On 12/03/10 12:49 PM, in article 59bb5081.1@webcrossing.JaKIaxP2ac0,
"nelsonmay@officeformac.com" <nelsonmay@officeformac.com> wrote:

> Thanks John. I actually pay less, because I have everything neat and tidy in a
> spreadsheet. Most people turn in a box of receipts. I will probably just go
> the cut and paste route and add with a calculator. I have the weekend to do
> this. I was just trying to get ahead of the 8 ball. You know, I would love to
> learn XL, but I am a cinematographer and editor. I can do complicated things,
> I just don't like long stings of numbers. I will mess up. I will never be a
> line producer. 
> 
> Cheers

This email is my business email -- Please do not email me about forum
matters unless you intend to pay!

 -- 

John McGhie, Microsoft MVP (Word, Mac Word); Consultant Technical Writer,
McGhie Information Engineering Pty Ltd, Sydney, Australia.
Ph: +61 (0)4 1209 1410; mailto:john@mcghie.name


0
John
3/12/2010 7:31:58 AM
Reply:

Similar Artilces:

Pls Help!!
Hello i need to check a row for data and if that data is their i need to the check the next row and if the data isnt their then i need to put th data in that range i know how to add the data using VBA but i dont know how to check tha row for the data and if it does exist i need to goto the next row Hope this helps, Jami -- Message posted from http://www.ExcelForum.com for each c in selection if c="mydata" then c="mydata" next c -- Don Guillett SalesAid Software donaldb@281.com "boris2004 >" <<boris2004.12e7s7@excelforum-nospam.com> wrote in me...

Find MAX data in sheet (Cell)
How would one go about finding the cell that contains the MAX info, o say any/all cells that exceeds 8,000 characters/spaces in a Excel page -- confuzedagai ----------------------------------------------------------------------- confuzedagain's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2943 View this thread: http://www.excelforum.com/showthread.php?threadid=49148 1. Highlight the entire document 2. Select Format->Conditional Formatting 3. Select "Formula Is" 4. Input this formula =LEN(A1)>8000 5. Format with something obvious like green bac...

HELP: Handling WM_SHOWWINDOW when window opened SW_SHOWMAXIMIZED
I've verified that the WM_SHOWWINDOW handler OnShowWindow() is not called when ShowWindow is called with SW_SHOWMAXIMIZED. I created a dummy MFC app (doc/view and no doc/view) and reproduced the behavior. So, here is what I have (simplified)... MFC app with no doc/view support. CMyWinApp::InitInstance() { .... pMainFrame->ShowWindow(m_nCmdShow); pMainFrame->UpdateWindow(); .... } CMainFrame::OnShowWindow(...) { // Not called when m_nCmdShow == SW_SHOWMAXIMIZED!! } This seems to be the designed behavior. Does anyone know why? What message can I handle in this case to know when the...

How to find cells with links to other workbooks?
I have a spreadsheet which shows under the edit menu links to other workbooks. I have tried to find where the links are without success. Is there a easy wy of finding such links? Hi you may try http://oaltd.co.uk/MVP/Default.htm and search for Findlink.zip (http://oaltd.co.uk/DLCount/DLCount.asp?file=FindLink.zip) "Bathonian" wrote: > I have a spreadsheet which shows under the edit menu links to other > workbooks. I have tried to find where the links are without success. Is there > a easy wy of finding such links? ...

Do NOT want Shape Objects to automatically link
In Word 2007, I have been trying to create a document using the Shapes and Text Box objects. Each time I try to insert another shape or Text box it automatically links to the one before. So when I move one the other one moves and if I delete one the other one is also deleted. I have looked through the options but cannot find why this is happening. I have Word 2007 installed on another computer and it does not do that. Any suggestions as to why or better yet, how can I stop this from happening? Thanks Karen This is a form of document corruption. See http://word.mvps.org...

to extract multiple values from an array
Hi, I am into Image processing industry, for each job we create unique code in excel, we Process 20 jobs in a day, I want to list all the job Code in "summary of the day sheet" that we complete for the particular day . On Dec 30, 4:56=A0am, ratan h <ratha...@nextgenalbums.com> wrote: > Hi, > > I am into Image processing industry, for each job we create unique > code in excel, we Process 20 jobs in a day, I want to list all the job > Code in "summary of the day sheet" that we complete for the particular > day . Not enough info but, assuming you are...

IF statement help #2
Hi What im trying to do is, Calculate a Cell x say 0.5. If the result is a negative number, sho 0. If it is a positive number, work it out and show the answer. Jus tried, and cant seem to get it right for some reason. I dont want t have to use another cell for no reaon, as I need to present th worksheet This is what I tried, IF((E22*0.5)>0), (E22*0.5), Thank -- RudeYut ----------------------------------------------------------------------- RudeYute's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3295 View this thread: http://www.excelforum.com/showthr...

Am I able to manage multiple email accounts
I have several email accounts including Gmail, HOtmail and corporate. I can't make Entourage mail (2004) list individual accounts or find how to manage profiles. Apple mail works but doesn't allow me to insert a tiff company logo without making it an attachment. Outlook allows for all of the above.<br> Appreciate help > This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3285235088_9822952 Content-type: text/plain; charset="US-ASCII" Content-transfer-encoding: 7bit On 2/7/08 1...

help attaching files
Hi... Using XP Pro and Outlook 2003. For some odd reason, I am unable to attach files when sending emails. Click on the 'paperclip', window opens to the My Doc folder. then when I click on a sub folder to open, nothing happens... finally, a Microsoft Word window opens that it is unable to open.... It is almost as if it is trying to launch Word to open the folder... Help? Thanks! -- B'rgds, Vinnie FWIW, from the Microsoft side a new Knowledge Base Article (KBA) was released on Apr 15th. http://support.microsoft.com/?kbid=918165 It lists two known conflicts so far....

Help, urgent, connection to access database
Hi, all I met a problem about access database connection. I have a program reading data from access database through ODBC. When the access database located on local server, and created ODBC linked to local database,the program runs without problem, but if it is a network shared database, and created ODBC linked to the shared database, the program prompts error: "workgroup file is missing". Yes, I used workgroup file in current database and set correct security. So what's the problem? is problem of the ODBC drive? you should not be using ODBC Access stop...

how do i search an excel sheet for links to another spreadhseet?
Is there a Find function that allows me to look for links in a sheet? When I can't find links, I'll use Bill Manville's FindLink program: http://www.oaltd.co.uk/MVP/Default.htm Flutie99 wrote: > > Is there a Find function that allows me to look for links in a sheet? -- Dave Peterson ...

Combine variable number of columns
I have a single spreadsheet with a list of clients, addresses and their product interests. This table will be used to drive a Mailmerge document. In the document, I want to be able to refer to the products in which they indicated an interest. The problem is that while one client may have identified only one product (one column) others have selected anywhere from two to 170 products - each product is in a separate column. I can join two columns with "&", but when I have an inconsistent number of columns how do I do this efficiently? - I will have to do it for each client, e...

Number to text conversion ......HELP
Dear Friend.. I need help in converting formula calculation ( Number or Currency ) into Text EQ. Result = 2,500.00 converting to .....two thousand five hundred.. Thanks Check http://www.mvps.org/access/modules/mdl0001.htm at "The Access Web" -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Eko Hery" <eko@megaenterprise.com> wrote in message news:BCD8B0CE.1D8%eko@megaenterprise.com... > Dear Friend.. > I need help in converting formula calculation ( Number or Currency ) into > Text > > EQ. Result = 2,500.00 con...

AOL to Outlook 2003, transfer contacts and favorites, help?
Moving from 7 years of AOL service to a new ISP and using Outlook 2003 now. How do I avoid having to copy/paste hundreds of contacts one at a time from my AOL address book to Outlook. Also have lots of 'favorites' on AOL I would like to keep... Jakfrost "Jakfrost" <Jakfrost@discussions.microsoft.com> wrote in message news:924061C2-70C6-45DE-BA7D-9533902A9783@microsoft.com... > Moving from 7 years of AOL service to a new ISP and using Outlook 2003 > now. > How do I avoid having to copy/paste hundreds of contacts one at a time > from > my AOL addres...

Configure single Outlook 2003 Account to try multiple outgoing servers?
Is there a way to configure a single Outlook 2003 account to try different SMTP servers for Outgoing mail? I log in from two different locations, and both are behind firewalls. And from each location I can't get to the other SMTP server to send my mail. To get around this, I've created two Outlook accounts to use. One for each location. But this is not a good solution, because I have to remember to use the correct account, and it's easy to make a mistake. ....When I do, the outgoing mail very quietly sits in my Outbox, and doesn't get sent. People get very upset at me for ...

Compare two Sheets with Conditional Formatting #2
I'm going insane I'm trying to compare two sheets using Walkenbach's approach but for some reason when I use it...certain values don't show differences. Example: Sheet 1 (Range Name is Grid) has 18000 on Cell A1 Sheet 2 (Range Name is OldGrid) has 18000 on Cell A2 So if I change Cell A1 on Sheet 1 to 1 it should show me change but no...it just does nothing. However if I type 133...it shows change. Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=COUNTIF(OldGrid,A1)=0" Selection.FormatConditions(1).Interior.ColorIndex = 40 Help really appreciate...

Argent Help
Hello All Can Any One Tell Me How Can I Get The Number Of Visible Sections In A Report To Use As The Upper Bound Of A Loop Of Sections Because With A Worng Number Of Sections An Error Occured So If There Is Any Way (Using API,...) Any Way Please Tell Me Thanks And Happy New Year For All Rabea Khalil "Developer" <shamijewelerjo@yahoo.com> wrote > Hello All > Can Any One Tell Me How Can I Get The Number Of Visible Sections In A > Report > To Use As The Upper Bound Of A Loop Of Sections Because With A Worng > Number Of Sections An Error Occured > > S...

Inbound email linked in CRM by default
Inbound emails from outside our company are being tracked in CRM by default. For example, Bob@acme.com emails Alice@mycompany.com. That email, before Alice has even opened it, is traced in CRM. I can view it in CRM and Alice is the owner. Alice is using CRM 3 in Outlook 2007. This does not happen all the time. Only certain external users sending email that includes Alice seem to be automatically tracked. If I email Alice from my greg@gmail account, it will not be tracked. However, I can findl little rhyme or reason to the criteria dictating what gets tracked. I have checked Alice's set...

CRM for international companies
Hi, Being an international company, our pricelists are in different currencies. MS CRM stores the currency on the Organization level and not on the Business Unit level, which essentially prevents our use of it. (1) Any suggestions for a way to overcome this problem (besides "translating" the EU, EMEA and US pricelist to one currency, e.g. dollars, or waiting for CRM 2.0) ? (2) I noticed that the database schema contains "Organization ID" for all basic tables. This probably means that multiple organizations can run on the same CRM server. Currently, I can't se...

Create field from append query based on linked table name
Here's the setup: Two linked tables called 'PHD' and 'XANS' bring in daily data from two CSV files. A union table-query puts the common data in both into the same name fields. This table-query is called 'SOLS_DATA_MERGE'. I then created a new table called 'SOLS_MAIN' and I ran an append query called 'SOLS_DATA_APPEND' to append the data in the table-query, 'SOLS_DATA_MERGE' into the new table, 'SOLS_MAIN'. The main reason for this was so that I could assign my data a primary key. Even though I have achieved my goal of merging the da...

many sheets from "master data"
Hi guys, In the last few days, ive posted some questions with some success, bu not being able to completely work though my problem. Given that I a novice/intermediate in relation to VBA, I am having extreme difficult augmenting the code to do what i like. Attached is the file so you can visualize what I am talking about. What I want to do is to put each "page" of data on a new slide. As yo can see, all the data is found on the first page. In column T, you ca see where each new page starts (as indicated by the data and pag number). I having trying tirelessly to get the code su...

Multiple email accounts #11
I use Outlook 2003 with multiple accounts (4 POP accounts and one Exchange account) and I have the following problems: Most of the time I am not connected to the corporate network, so most of the time I have no connection to the Exchange server. Because of this Outlook shows the dissconected icon on the bottom right status bar. Once Outlook says that it is dissconected from the Exchange server it doesn't do the automatic syncs with the POP accounts through the internet (which is indeed available). Is there a way to make Outlook autosync with the POP accounts if it is not conne...

Another CPropertySheet help...
I am using the Wizard mode and I can remove the buttons, but I also want to remove the 3D line control that appears above the buttons. What is the control ID? Thanks!... ...

Multiple Exchange boxes in one domain
Hi all, I have the following setup for my domain: HQ: Server 2003 Exchange 2003 We have a branch office located on another industrial estate 1/2 mile away, and we currently connect to them via WiFi (11Mb) and they are in the same domain and subnet as us. I am looking to add a second exchange box at their site so they can get their mail "locally". When I look at setting it up tho ot seems that exchange will still send the messages across the WiFi due to there only being 1 MX record? Is there a way I can get them to recive their mail directly over their own Broadband link? Also ...

To goup in each month on date column
I am using office 2007 and I have an excel sheet with the following Date OS 2/1/2009 200.30 2/1/2009 300.00 3/2/2009 400.00 5/2/2009 600.00 How I can group the excel to know outstanding in each month. To group January os and February os and so on.... Please help with thanks and Regards Try =SUMPRODUCT((TEXT(A2:A10,"mmyyyy")="022009")*(B2:B10)) with the date in cell C1 =SUMPRODUCT((TEXT(A2:A10,"mmyyyy")=TEXT(C1,"mmyyyy")*(B2:B10)) -- Jacob "pol" wrote: > I ...