SUMIF over multiple spreadsheets

[Mark234]

Hi,

I would like to use the following formula over 30+ worksheets in th
same workbook but I do not want to have to repeat sum if over and ove
again.

I have tried my own research and most people suggest sumif wit
sumproduct and indirect but I cannot get my head round it.

This is the formula I am using at the moment:

=(SUMIF('SMITH S'!V$4:V$22,C5,'SMITH S'!W$4:W$22))

'Smith S' is an employee name and the other sheets are all named in tha
format. I have created a list and defined a name for it called 'tabs'.
understand you need to do this for the indirect function?

Can some body reply with the formula I need to use that includes m
other 30 worksheets please

 

[Spencer101]

Hi

I would like to use the following formula over 30+ worksheets in th
same workbook but I do not want to have to repeat sum if over and ove
again

I have tried my own research and most people suggest sumif wit
sumproduct and indirect but I cannot get my head round it

This is the formula I am using at the moment

=(SUMIF('SMITH S'!V$4:V$22,C5,'SMITH S'!W$4:W$22)

'Smith S' is an employee name and the other sheets are all named in tha
format. I have created a list and defined a name for it called 'tabs'.
understand you need to do this for the indirect function

Can some body reply with the formula I need to use that includes m
other 30 worksheets please

Hi

Assuming "Smith S" is recorded in cell A7 (amend this to suit), th
formula would be

=SUMIF(INDIRECT(A7&\"!V$4:V$22\"),C5,INDIRECT(A7&\"!W$4:W$22\")
HOWEVER, you cannot use spaces in the tab names. So you would have t
have "Smith_S" or "SmithS" or "Smith.S" or something similar

As for needing to define a range name for INDIRECT to work.... Never ha
to do that. You just need the tab names in a list that you will refe
to

 

[Mark234]

Hi

Assuming "Smith S" is recorded in cell A7 (amend this to suit), th
formula would be

=SUMIF(INDIRECT(A7&\"!V$4:V$22\"),C5,INDIRECT(A7&\"!W$4:W$22\")
HOWEVER, you cannot use spaces in the tab names. So you would have t
have "Smith_S" or "SmithS" or "Smith.S" or something similar

As for needing to define a range name for INDIRECT to work.... Never ha
to do that. You just need the tab names in a list that you will refe
to

Hey

Thanks for replying - I tried your advice but I am getting the REF erro
- can you take a look at the attached please and see where I am goin
wrong

I would like the formula on the 'SUMMARY' tab to the right of the
digit codes...where I started my original SUMIF formula

+-------------------------------------------------------------------
|Filename: exp 1.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=446
+-------------------------------------------------------------------

 

[Spencer101]

Hey

Thanks for replying - I tried your advice but I am getting the REF erro
- can you take a look at the attached please and see where I am goin
wrong?

I would like the formula on the 'SUMMARY' tab to the right of the
digit codes...where I started my original SUMIF formula.

Hi Mark,

I'm not 100% sure I understand what you're trying to do on your summar
sheet. Could you explain a little more?

Spencer

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Vacuum Sealed]

Hi Mark

IMO your layout is wrong.

I would be more inclined to have 1 sheet to record all the entries in
columns, the mega benefit of this is that you dont have to worry about
complex formulas branching across several sheets.

Date | Emp | Expense | Amount | VAT


You could then look at a matrix grid for your Summary Sheet.

You could also have another sheet which you could use for one-off
employee search | View | Print on a Monthly/Annual basis....

This would mean you will eliminate the need for multiple tabs for each
employee, which would expand & contract due to attrition.

I would be happy to redo your existing and send it to you for your perusal.

Cheers
HTH
Mick.