Summing accross sheets

Hi there. Yes, this is perfectly do-able. I've created a dynamic range (imaginatively called LIST) on sheet1 which drives the whole thing. Just add sheet names to the list and it'll expand to cope.

See attached sheet.

Originally Posted by Glenn Kennedy

Hi there. Yes, this is perfectly do-able. I've created a dynamic range (imaginatively called LIST) on sheet1 which drives the whole thing. Just add sheet names to the list and it'll expand to cope.

See attached sheet.

Hi Glen,
I think the op is asking for Overall Result Cost and Hours total from each sheet which are in the last columns
Like Sum=(North!AF7,Central!AH7) and sum(North!AG7,Central!AI7)

Hi.

You've posted a macro-enabled workbook though in the Excel General subforum. Are you looking specifically for a VBA solution to this?

Regards

nflsales, you're probably right... I didn't go that far over...

Pauldaddyadams, is it intentional that the total columns on the two sheets are in different columns (AF & AG in North; AH & AI in central). If they can be harmonised, a simple modification of my solution will work.

Originally Posted by pauldaddyadams

Hi Glen

thanks for taking a look at this.

Silva has posted before me but they are correct, it would need to be the total column if possible, not just column M and L

Is it ok for you, if the totals columns brings after Column K, Then it will be unique for all the sheets and your problem will be solved.

What is the problem if the totals columns brings after Column K like attached file

I can't think of any way to do this... IF the position of the totals column can be standardised (ae.g. as nflsales suggested) after column K - it's easy. Otherwise, i don't immediately know what to do.

Originally Posted by pauldaddyadams

Hi,


The obvious thing for me to do is to change and fix the total column on each sheet but this isn’t possible.

Why is that? You would simplify things considerably if your total row was always on the same row above the data. I'm constantly surprised when I see totals rows at the foot of tables especially when different sheets have different numbers of rows.

This is reminiscent of the old pencil and paper days (pre 1978 !) when totals were traditionally at the bottom. Now we're in a digital age this makes no sense whatsoever and I always wonder why spreadsheets continue to reflect this.

This can be done even if the totals are in different columns in each worksheet.

However, apologies for asking this, though I would kindly ask that you re-attach your workbook with several, manually-calculated desired results added, just so I know what I'm aiming for.

Regards

Thanks.

A few questions:

1) In each of the sheets in question, although the column to be referenced may be different, will it nevertheless always be headed "Overall Result"? And will these headers, in all sheets, appear in merged cells in rows 3-5?

2) Do the calculations need to match on the Network Name? In your workbook it would appear that this data is always in the same row in each sheet: "Test 1", for example, is in row 7 in all of the Summary, North and Central tabs. If this is the case with all such strings then no matching will be necessary.

3) It may be necessary to set the formulas to calculate over a number of rows which is sufficiently large to cover all sheets involved. Can you give a suitably (though obviously not unnecessarily) large upper bound on this number?

Regards

Originally Posted by pauldaddyadams

Hi,

1. Yes - it will always be called overall result and the merged cells will be the same.
2. The names will always be the same in each sheet. e.g. Test1 will always be in the same row of each sheet.
3. I do not understand point 3?

Paul

Apologies for not being clear. Basically, what is the largest row number containing data you ever envisage having across all of your sheets?

Regards

Thanks.

First go to Name Manager (Formulas tab) and define:

Name: Sheets
Refers to: ={"North","Central"}

(Or whatever happen to be the relevant sheet names in question.)

The formula in cell L7 of the Summary tab is then:

=SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheets&"'!"&ROWS($1:7)&":"&ROWS($1:7)),INDIRECT("'"&Sheets&"'!3:3"),"Overall Result",INDIRECT("'"&Sheets&"'!6:6"),"Cost"))

Copy down as required.

The formula is identical for cell M7, apart from "Cost" at the end becoming "Hours".

Note that the 7 in the ROWS($1:7) part is designed so that the formula in row 7 of the Summary sheet references that row in each of the sheets in question. As this formula is copied down, this part becomes, successively:

ROWS($1:8) (and so the formula references row 8 in each of the sheets in question)

ROWS($1:9) (and so the formula references row 9 in each of the sheets in question)

etc., which was made possible by your confirming that the Network Name will always be in the same row in all of the sheets, including Summary.

My question re an upper row limit in the end turned out not to be a concern using this set-up.

Regards

You're welcome!

Edit: thanks for the PM - tried to reply but your inbox is full!

Cheers