Sum(Indirect())

Hello,

I have a worksheet that has the user ask how many stages they need on “Stage1” worksheet and then stage1 worksheet is copied.

I have a formula on another worksheet “MaterialCalculation” where I am trying to sum(indirect()) the amount of materials needed. I created this function when I had asked for 30 stages but when I tried it again and used 6 stages I got the #REF! error.

I have been working on this a while so any help would be greatly appreciated! Thank you!

=SUMIF(INDIRECT(“Stage1!$C$6:$C$18”),$A5,INDIRECT(“Stage1!$E$6:$E$18”))+SUMIF(INDIRECT(“Stage2!$C$6:$C$18”),$A5,INDIRECT(“Stage2!$E$6:$E$18”))+SUMIF(INDIRECT(“Stage3!$C$6:$C$18”),$A5,INDIRECT(“Stage3!$E$6:$E$18”))+SUMIF(INDIRECT(“Stage4!$C$6:$C$18”),$A5,INDIRECT(“Stage4!$E$6:$E$18”))+SUMIF(INDIRECT(“Stage5!$C$6:$C$18”),$A5,INDIRECT(“Stage5!$E$6:$E$18”))+SUMIF(INDIRECT(“Stage6!$C$6:$C$18”),$A5,INDIRECT(“Stage6!$E$6:$E$18”))+SUMIF(INDIRECT(“Stage7!$C$6:$C$18”),$A5,INDIRECT(“Stage7!$E$6:$E$18”))+SUMIF(INDIRECT(“Stage8!$C$6:$C$18”),$A5,INDIRECT(“Stage8!$E$6:$E$18”))+SUMIF(INDIRECT(“Stage9!$C$6:$C$18”),$A5,INDIRECT(“Stage9!$E$6:$E$18”))+SUMIF(INDIRECT(“Stage10!$C$6:$C$18”),$A5,INDIRECT(“Stage10!$E$6:$E$18”))+SUMIF(INDIRECT(“Stage11!$C$6:$C$18”),$A5,INDIRECT(“Stage11!$E$6:$E$18”))+SUMIF(INDIRECT(“Stage12!$C$6:$C$18”),$A5,INDIRECT(“Stage12!$E$6:$E$18”))+SUMIF(INDIRECT(“Stage13!$C$6:$C$18”),$A5,INDIRECT(“Stage13!$E$6:$E$18”))+SUMIF(INDIRECT(“Stage14!$C$6:$C$18”),$A5,INDIRECT(“Stage14!$E$6:$E$18”))+SUMIF(INDIRECT(“Stage15!$C$6:$C$18”),$A5,INDIRECT(“Stage15!$E$6:$E$18”))+SUMIF(INDIRECT(“Stage16!$C$6:$C$18”),$A5,INDIRECT(“Stage16!$E$6:$E$18”))+SUMIF(INDIRECT(“Stage17!$C$6:$C$18”),$A5,INDIRECT(“Stage17!$E$6:$E$18”))+SUMIF(INDIRECT(“Stage18!$C$6:$C$18”),$A5,INDIRECT(“Stage18!$E$6:$E$18”))+SUMIF(INDIRECT(“Stage19!$C$6:$C$18”),$A5,INDIRECT(“Stage19!$E$6:$E$18”))+SUMIF(INDIRECT(“Stage20!$C$6:$C$18”),$A5,INDIRECT(“Stage20!$E$6:$E$18”))+SUMIF(INDIRECT(“Stage21!$C$6:$C$18”),$A5,INDIRECT(“Stage21!$E$6:$E$18”))+SUMIF(INDIRECT(“Stage22!$C$6:$C$18”),$A5,INDIRECT(“Stage22!$E$6:$E$18”))+SUMIF(INDIRECT(“Stage23!$C$6:$C$18”),$A5,INDIRECT(“Stage23!$E$6:$E$18”))+SUMIF(INDIRECT(“Stage24!$C$6:$C$18”),$A5,INDIRECT(“Stage24!$E$6:$E$18”))+SUMIF(INDIRECT(“Stage25!$C$6:$C$18”),$A5,INDIRECT(“Stage25!$E$6:$E$18”))+SUMIF(INDIRECT(“Stage26!$C$6:$C$18”),$A5,INDIRECT(“Stage26!$E$6:$E$18”))+SUMIF(INDIRECT(“Stage27!$C$6:$C$18”),$A5,INDIRECT(“Stage27!$E$6:$E$18”))+SUMIF(INDIRECT(“Stage28!$C$6:$C$18”),$A5,INDIRECT(“Stage28!$E$6:$E$18”))+SUMIF(INDIRECT(“Stage29!$C$6:$C$18”),$A5,INDIRECT(“Stage29!$E$6:$E$18”))+SUMIF(INDIRECT(“Stage30!$C$6:$C$18”),$A5,INDIRECT(“Stage30!$E$6:$E$18”))

By: Rose Currie

Leave a Reply

Your email address will not be published. Required fields are marked *