# Thread: Formula over 23 sheets (2003 SP2)

1. ## 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

2. ## 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. ## 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

4. ## 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. ## 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

#### Posting Permissions

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