New Contributor

# Possibility to have a more practical formula

Dear All, thank you in advance for your support.

I have this formula which, as you can see, refers to multiple sheets, is there a simpler way of how to get the results I need (Formula Below)

=IF(D5='1'!H2,SUMIF('1'!C23:C48,Payslip!N5,'1'!R23:R48),IF(D5='2'!H2,SUMIF('2'!C23:C48,Payslip!N5,'2'!R23:R48),IF(D5='3'!H2,SUMIF('3'!C23:C48,Payslip!N5,'3'!R23:R48),IF(D5='4'!H2,SUMIF('4'!C23:C48,Payslip!N5,'4'!R23:R48),IF(D5='5'!H2,SUMIF('5'!C23:C48,Payslip!N5,'5'!R23:R48),IF(D5='6'!H2,SUMIF('6'!C23:C48,Payslip!N5,'6'!R23:R48),IF(D5='7'!H2,SUMIF('7'!C23:C48,Payslip!N5,'7'!R23:R48),IF(D5='8'!H2,SUMIF('8'!C23:C48,Payslip!N5,'8'!R23:R48),IF(D5='9'!H2,SUMIF('9'!C23:C48,Payslip!N5,'9'!R23:R48),IF(D5='10'!H2,SUMIF('10'!C23:C48,Payslip!N5,'10'!R23:R48),IF(D5='11'!H2,SUMIF('11'!C23:C48,Payslip!N5,'11'!R23:R48))))))))))))

Thank You once Again

5 Replies

# Re: Possibility to have a more practical formula

@AlbertM1960 Create a range on the same sheet as the cell with the formula, with formulas referring to H2 on the sheets 1 to 11.

For example in F1 ='1'!H2, in F2 ='2'!H2 etc.

You can then use

=SUMIF(INDIRECT("'"&MATCH(D5,F1:F11,0)&"'!C23:C48"),Payslip!N5,INDIRECT("'"&MATCH(D5,F1:F11,0)&"'!R23:R48"))

# Re: Possibility to have a more practical formula

@AlbertM1960 Since the SUMIF seems to repeat based except for the sheet reference, maybe considered consolidating all the tables to one final sourced table. That way the SUMIF can then be updated to SUMIFS, taking into the account the new variable (ie. the month). If you can attach a sample workbook, removing any sensitive data, I can create a sample of what I mean.

# Re: Possibility to have a more practical formula

=SUMIF(INDIRECT("'"&SWITCH(D5,‘1’!H2,1,‘2’!H2,2,‘3’!H2,3,‘4’!H2,4,‘5’!H2,5,‘6’!H2,6,‘7’!H2,7,‘8’!H2,8,‘9’!H2,9,‘10’!H2,10,‘11’!H2,11)&"'!C23:C48,Payslip!N5,'"&SWITCH(D5,‘1’!H2,1,‘2’!H2,2,‘3’!H2,3,‘4’!H2,4,‘5’!H2,5,‘6’!H2,6,‘7’!H2,7,‘8’!H2,8,‘9’!H2,9,‘10’!H2,10,‘11’!H2,11)&"'!R23:R48)"

# Re: Possibility to have a more practical formula

Thank you so much for your answer Steve.  Your formula is much more practical than what I used, but I am looking for something that will do the job even if I add more sheets.  The sample formula I sent is only one of 6 formulas I am using, so if I add a sheet I will always have to modify the six formulas, in other words I need a formula that does not need the Sheet Names, if it is possible.

# Re: Possibility to have a more practical formula

Rather than using a formula I wonder if this helps

Wyn

MVP

UTC+8