Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formula over 23 sheets (2003 SP2)

    Good evening

    I am trying to create a stock control workbook

    Scenario: I order in and hold stocks of boxes, which I then in turn ship to 23 Countries as and when they tell me they need new supplies of x, y or z packing material (there are at present 43 variants of the packing material)

    What I have done so far is created a TotalStock worksheet that among other columns shows 'Box Type' and Quantity' I have the copied this into 24 other worksheets and named them along the lines of UKHQ, Austria, Belgium, China, Denmark etc. etc. I have manually entered all of the stock that each Country currently holds.

    On the TotalStock worksheet I would now like to total the quantity from each country, If I have a package called BioBox and next to it a Quantity box, say E3 I realise that I could enter a formula adding the quantity from the same cell in each individual worksheet but that would take an awful long time, any ideas how I could speed it up?

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula over 23 sheets (2003 SP2)

    I would use a single sheet for all the data, with an extra column for the country, instead of a separate sheet for each country.

    If you prefer to keep the current setup, you can use a formula like this:
    <code>
    =SUM('UKHQ:Zimbabwe'!E3)
    </code>
    where UKHQ is the first (leftmost) sheet you want to include, and Zimbabwe the last (rightmost) one.

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula over 23 sheets (2003 SP2)

    Thanks Hans

    I have followed your example and typed in =SUM('UKHQ!:Ukraine'!E3) , where UKHQ is the first sheet and Ukraine is the last but it adds an extra Ukraine into the formula =SUM('UKHQ!:[Ukraine]Ukraine'!E3) and returns a #Ref!

    Any idea where I have gone wrong?

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula over 23 sheets (2003 SP2)

    There a superfluous ! after UKHQ.
    The easiest way to create the formula is as follows:

    Select the cell where you want the formula.
    Type an =
    Click on the sheet tab of UKHQ.
    Hold down Shift while you click on the sheet tab of Ukraine.
    (You can release Shift now)
    Click on cell E3
    Press Enter or click the green check mark in the formula bar.

  5. #5
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula over 23 sheets (2003 SP2)

    Thanks Hans

    I got it going by removing the ! that you noticed, try as I might though I could not get the other method working but it would not have it, I shall try again today though

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •