Excel question

Discussion in 'OT Technology' started by victimizati0n, Sep 5, 2009.

  1. victimizati0n

    victimizati0n New Member

    Joined:
    Jun 16, 2005
    Messages:
    47,046
    Likes Received:
    0
    Hey guys.. i forget a lot of excel stuff, but im making a spreadsheet and im wondering if anyone can help me:

    Im making a spreadsheet with 5 worksheets inside of it

    what i would like to do is link all of the spreadsheets in a "grand total" worksheet so i can total the 5 worksheets i am making, but i would also like to make it where if i add a new row/column to my worksheets, it will update the "grand total" one and have it automatically show up

    is this even possible? I have linked stuff in excel before, but that is when i took a computer class back in highschool, so i forget how to do everything other than basic formulas and formatting

    thanks :wavey:
     
  2. victimizati0n

    victimizati0n New Member

    Joined:
    Jun 16, 2005
    Messages:
    47,046
    Likes Received:
    0
    ok, i was able to do a copy/paste link to give me what i want (somewhat) but i ran into a problem..

    if i add in a new row, it wont show up in my grand total worksheet, it just changes the formulas.. for instance if i had info in cell A:6 then added a new row in i will have a blank A:6 and the info that was already there will be pushed down to A:7.. well in my grand total worksheet, it will just skip the new blank A:6 row and change all of the formulas
     
  3. hvy55chvy

    hvy55chvy OT Supporter

    Joined:
    Jan 6, 2005
    Messages:
    2,483
    Likes Received:
    3
    Location:
    California
    im no excel guru, but here is 1 solution to what you are asking. I dont know your exact spread sheet layout so im going to keep formula cell target simple, 1 column and 1000 rows.

    create 6 sheets in a work book named...

    "GrandTotal"
    "Sheet1"
    "Sheet2"
    "Sheet3"
    "Sheet4"
    "Sheet5"

    On the GrandTotal sheet paste the following formula into B1, B2, B3, B4 and B5...

    =SUM(Sheet1!C1:C1000)

    starting in B2 change the number following "Sheet" to the 4 remaining sheet numbers, 2 through 5. So you will have these 5 different formulas

    B1 "=SUM(Sheet1!C1:C1000)"
    B2 "=SUM(Sheet2!C1:C1000)"
    B3 "=SUM(Sheet3!C1:C1000)"
    B4 "=SUM(Sheet4!C1:C1000)"
    B5 "=SUM(Sheet5!C1:C1000)"



    Then below these 5 formulas cells create a 6th formula that Sums those 5 cells.

    B6) =SUM(B1:B5)


    Now any number put on Sheets 1 through 5, in column C1 though C1000 will be summed and shown in the corresponding cell on the GrandTotal sheet. Then the 5 sheet total formulas will be summed and shown in B6.

    The trick here is not to have formulas that looks for added entries or anything fancy like that, but rather just pre-assign a large swath of cells that you anticipate could be filled with new entries.

    Say one day you find that on Sheet2 you have 1020 rows of data. The last 20 rows will not be summed on the Grand Total sheet. Since you expect hundreds more rows of data in C column you could just change

    B2) "=SUM(Sheet2!C1:C1000)"

    to

    B2) "=SUM(Sheet2!C1:C2000)"

    hope this makes sense. Hard to explain this crap typing it out like this.
     
  4. victimizati0n

    victimizati0n New Member

    Joined:
    Jun 16, 2005
    Messages:
    47,046
    Likes Received:
    0
    Thanks for the help, but when i put those in, its not adding all of my data, i wanted to basically make a HUGE worksheet that consisted of all the other ones also

    basically, instead of printing out 5 different worksheets, i want to print the grand total one so it will just be one long spreadsheet.

    im sorry if this isnt making sense, but im not an expert in excel
     
  5. hvy55chvy

    hvy55chvy OT Supporter

    Joined:
    Jan 6, 2005
    Messages:
    2,483
    Likes Received:
    3
    Location:
    California
    Only way i think I could help you is if i could see your workbook, or you create a blank template to show me specifically what you need.
     
  6. victimizati0n

    victimizati0n New Member

    Joined:
    Jun 16, 2005
    Messages:
    47,046
    Likes Received:
    0
    do you have an email?

    i can send it to you if you want

    edit* pm me info
     
  7. Coottie

    Coottie BOOMER......SOONER OT Supporter

    Joined:
    Jun 6, 2006
    Messages:
    32,407
    Likes Received:
    0
    Location:
    OKC
    One way I do this, is to have "extra" cells in my summation formulas.

    So I have cells B9 - B30 that have data that I want to sum. I create my formula from B8 - B31. Then when I add a row, my totals will adjust accordingly....assuming I don't us absolute references in my summation, it will update with the new data.

    So on my data entry sheets, I have summation totals that I then reference on my 'grand total' spreadsheet. But I can do the same thing without the individual summation totals on the data sheets.

    The same idea applies to columns.
     

Share This Page