There has to be a simple answer to this only I cant see it.
I have a tab which contains 1700+ rows by 110 weeks worth of data.
On another tab I have a calculator page where:
I am allowing the user to chose a start and end date via drop downs boxes
and a list products.
Against each product, I am displaying the sum of that product.
If I hard code the range it works fine
where SKU2 =Sheet3!$C$12:$C$1739
If I try to use indirect it fails
where PeriodRange = Sheet3!$BC$7:$BM$1739
Formula for PeriodRange:
What am I missing, I have tried quotes in various places.
Martin
I have a tab which contains 1700+ rows by 110 weeks worth of data.
On another tab I have a calculator page where:
I am allowing the user to chose a start and end date via drop downs boxes
and a list products.
Against each product, I am displaying the sum of that product.
If I hard code the range it works fine
Code:
=SUMPRODUCT(--(SKU2=$C11)*Sheet3!$BC$12:$BM$1739)
If I try to use indirect it fails
Code:
=SUMPRODUCT(--(SKU2=$C10)*INDIRECT(PeriodRange))
Formula for PeriodRange:
Code:
"Sheet3!"&ADDRESS(7,MATCH("Week "&Sheet1!F4&" "&Sheet1!G4,$7:$7,0))&":"&ADDRESS(COUNTA($F:$F)+11,MATCH("Week "&Sheet1!F5&" "&Sheet1!G5,$7:$7,0))
Martin