Help needed for sum / range calculation from one worksheet to another

[ballst]

Hi all,

My excel 2003 in easy steps book didn't have the answer for this one and my web search didn't throw up anything similar, I have tried to modify what I have tracked down, but without success, hence my post.



I'm therefore looking for some help with a sum which calculates data from one worksheet, but displays the result in another.

The attached example has data in the two worksheets July2015 and MonthStats. What I am trying to achieve is that a sum within the monthly stat column references the data in the July2015 worksheet to return the relevant figures.

So in my example in cell B12 of the MonthStats worksheet the sum would calculate how many Fire case types there were on 01 July in the July2015 worksheet. This sum would need to reference columns C & E which deal with type and quantity, so the answer for 01 July would be 3.

I then also need a similar sum for each Case Officer, so in Cell B3 in MonthStats the answer would be two.

My other sticking point is the sheet is used by folks, with less excel skills than even me, so would welcome any suggestions as to how to make the necessary monthly updating of formulas, pain free.

Thanks for reading, hope my explanation is clear, had some trouble posting....

[macropod]

See attached. The only real change to your workbook that's needed is to have a space between the month name & year on the 'July 2015' worksheet.

Note that there's now a bunch of formulae in row 11 on the 'MonthStats' sheet referencing the 'July 2015' worksheet's name for date calculation purposes. If you're going to have formulae referencing other sheets, you'll need to change those formulae to reference the other sheets or calculate the dates in some other way.

Once you have whatever you want for 'No Jurisdiction / Transferred' sorted out, I suggest you lock the 'MonthStats' sheet to prevent erroneous changes.

[ballst]

Macropod,

A quick 'thank you' for your help and assistance. I cannot begin to get my head around what is going on in row 11, but I will endeavour to at some point!!

I'm guessing the formula changes can be carried out with a find and replace, substituting month for month, and I'll play around with that at my end.

Thanks again,

Ballst

[ballst]

Macropod et al,

I've incorporated the formulas into my actual spreadsheet, but for some darn reason the formulas will not work or recognise one particular row.

I'm afraid I can't really post the sheet (data protection issues) which I understand might make this totally useless.

The formula =DATEVALUE(COLUMN()-1&" "&MID(CELL("FILENAME",'July 2015'!$A$1),FIND("]",CELL("FILENAME",'July 2015'!$A$1))+1,LEN(CELL("FILENAME",'July 2015'!$A$1))-FIND("]",CELL("FILENAME",'July 2015'!$A$1)))) is correct in each cell it appears in

The row which will not work is Row 15 which has the following formula =SUMPRODUCT(('July 2015'!$I$2:$I$199=$A15)*('July 2015'!$H$2:$H$199=I$21),'July 2015'!$E$2:$E$199) and again everything appears to be correct and consistent with other rows.

The result should be returning a total for 'Tom', but refuses to do so, I've changed the name, inserted a new row etc, but to no avail.

Am I missing something totally obvious???

A very puzzled Ballst...

[macropod]

If the problem is just for one row, check that you don't have any extraneous spaces after the name on either sheet (e.g. 'Tom ' instead of 'Tom'). You can have however many rows you need above the 'Totals' row on the 'MonthStats' sheet.

Do note, though, that if you simply add new rows above the 'Totals' row on the 'MonthStats' sheet, the additional rows won't get included in the stats. That's because the formula in that row only sums rows 3:9. Try adding the new rows between rows 3 & 9, instead.

[ballst]

I've managed to resolve the problem, it was something with the word Tom it didn't like, kind of odd as the validation list was running from the same info, but a name change from Tom to Thom and back again fixed things!!!

Thanks again, Ballst