Use Cell Reference to Define Sum Ranges Across Different Sheets

**jwhite0720** · 06-28-2012, 03:25 PM

I thought I had a complex if(sum(offset... formula all figured out and working fine until I needed to add a new range, and I received the dreaded nesting limitation error. What I'm currently doing is using a data validation drop down on the first page of a model to define which sheet should be referenced for any given cell throughout the model. An example of the current formula is =IF(Start!$C$3=Start!$F$4,SUMIF('1416'!$C$10:$C$1000,"*Hrly*",OFFSET('1416'!$AF$10,0,MATCH(D114,'1416'!$D$8:$O$8,0)-1,100,1)). I've currently got 5 Start!$C$3=Start!$F$* that dictates which sheet the sumif will reference and I need to add another and I can't seem to get it to work, even though I'm really only looking at 6 nested formulas. I'm hoping there is an easier way out there to change the references to which sheet is to be summed. Also, putting all the sheets together is not an option, as each department kicks out info on a separate tab due to the way my reporting is structured. Below is what I would like for the formula to look like if the nesting wasn't an issue. (Trying to add the italicized '1460' section).

Thanks so much in advance for anyone's help!!!

=IF(Start!$C$3=Start!$F$4,SUMIF('1416'!$C$10:$C$1000,"*Hrly*",OFFSET('1416'!$AF$10,0,MATCH(D114,'1416'!$D$8:$O$8,0)-1,100,1)),IF(Start!$C$3=Start!$F$5,SUMIF('1418'!$C$10:$C$1000,"*Hrly*",OFFSET('1418'!$AF$10,0,MATCH(D114,'1418'!$D$8:$O$8,0)-1,100,1)),IF(Start!$C$3=Start!$F$6,SUMIF('1419'!$C$10:$C$1000,"*Hrly*",OFFSET('1419'!$AF$10,0,MATCH(D114,'1419'!$D$8:$O$8,0)-1,100,1)),IF(Start!$C$3=Start!$F$7,SUMIF('1460'!$C$10:$C$1000,"*Hrly*",OFFSET('1460'!$AF$10,0,MATCH(D114,'1460'!$D$8:$O$8,0)-1,100,1)),IF(Start!$C$3=Start!$F$8,SUMIF('1424'!$C$10:$C$1000,"*Hrly*",OFFSET('1424'!$AF$10,0,MATCH(D114,'1424'!$D$8:$O$8,0)-1,100,1)),IF(Start!$C$3=Start!$F$9,SUMIF('1452'!$C$10:$C$1000,"*Hrly*",OFFSET('1452'!$AF$10,0,MATCH(D114,'1452'!$D$8:$O$8,0)-1,100,1)))))))