Attn: Excel gurus

[BarTopDancer]

I need some formula help please

I am trying to add the sum of 1 row across multiple workbooks.

Something similar to =sum(f4) across 20 worksheets within the same book. I need the sum of all 20 f4s. Does that make sense?

Thanks!

[scaeagles]

try this:

=sum(name of first worksheet!f4:name of last worksheet!f4)

[Drince88]

Actually, I was curious, so I tested - scaeagles' idea didn't quite work, but using help (I searched for "sum across worksheets") and the answer was in the first 5 or so options given) --- =SUM(Sheet1:Sheet3!f4) -- (So close to scaeagles - just without the first f4).

Refer to the same cell or range on multiple sheets
A reference that refers to the same cell or range on multiple sheets is called a 3-D reference.

Click the cell where you want to enter the function.
Type = (equal sign), enter the name of the function, and then type an opening parenthesis.
Click the tab for the first worksheet to be referenced.
Hold down SHIFT and click the tab for the last worksheet to be referenced.
Select the cell or range of cells to be referenced.
Complete the formula, and press ENTER.

(But what happens if you do just the first 19 worksheets in your workbook, and then move the 20th in the middle? May not be a concern for what you're doing, but if it's a possibility - probably should test it)

[Moonliner]

I must defend my title!!

SUM ALL SHEETS

The first one is how we can use the SUM function to Sum, say cell A1, on all Worksheets in your Workbook. With this method, all new sheets that are added to the Workbook are included in the SUM. Here is how;

Add a new Worksheet to you Workbook at the far right. To do this select "Worksheet" under "Insert" on the Worksheet Menu Bar. Then left click on this new Worksheet name tab, holding down the the left mouse button drag to the far right and release.

Name this sheet (double click the name tab) "Spacer" (although any name will do).

With "Spacer" being the active sheet, go to Format>Sheet>Hide.

Repeat steps 1 to 3 above, but this time name the Worksheet "Start" and drag it to the far left.

On the Worksheet you wish to have the result shown on enter: =SUM('Start:Spacer'!A1)

Add any number of new Worksheets, enter some numbers into A1 of any Worksheet and it will be included in the SUM. Move the Worksheets order around, Delete sheets etc and the SUM function still includes all Worksheets.

If you have concerns that a user may unhide/delete/move the Worksheets "Start & "Spacer", go to Tools>Protection>Protect Workbook (ensure "Structure" is checked), supply a password (optional) and click OK.



OK, OK, go I goolged this one. It still counts right?

[BarTopDancer]

Thank you! It worked.

[Kevy Baby]

Originally Posted by Moonliner

I must defend my title!!

You have to defend "Trend Bucker"?

What the buck?!?

[Moonliner]

Originally Posted by Kevy Baby

You have to defend "Trend Bucker"?

What the buck?!?

No you silly rabbit, not that title. This title.