Hello all, wondered if someone could possibly help with this query?!
I'm attempting to perform a sumif function referenced to another worksheet...but...the worksheet name referenced in the sumif changes based on a cell reference!
I've got 12 x data capture sheets called "Capture" 1 through 12 ie Capture 1, Capture 2, Capture 3, etc. (for 12 months of the year)
The Sumif function is used on one worksheet called "All Filters" but all the formula in this one sheet need to be dictated by a user input (in the setup worksheet) so that data from the correct named capture sheet is pulled and calculated in this same sheet and not 12 different ones!
The sumif is supposed to calculate the specified range dependant on a cell on a worksheet called "All Filters". This seemed straight forward at first and the formula evaluates to the correct worksheet and range but then ends up as #ref
The formula i'm using is as follows and i've hammered in quiet a few variants but no joy...please help
=SUMIF(INDIRECT($A$1& "!$B$2:$B$1000"),'All Filters'!C3,INDIRECT($A$1& "!$C$2:$C$1000"))
and in cell A1:
=("Capture "&'Setup'!G33)
This evaluates to a worksheet name used in the sumif function
The ref shows as volatile but when I fiddled with it I was able to get it to show as valid but once you evaluate it would then show as #ref
I've also tried something like this:
SUMIF("Capture "& Setup!G33 &"!$B$2:$B$1000",'All Filters'!C3,"Capture "& Setup!G33 &"!$C$2:$C$1000")
This also didn't work :-(
Please help...
I hope i've explained this well enough as i'm still a novice
Many thanks in advance
Regards
Keith
Bookmarks