I recently became an electrician and am trying to build myself a stock management system in excel.
I have it 99% complete, but am struggling with one little part.
I have been using a Sumproduct query to summarise information in one spreadsheet called Materials
=Sumproduct(--($k$5:$k$1001="P10001"),--$F$2:$F$1001)
The formulae calculates the cost of the materials used in job "P10001" - It works prefectly.
Final step I want to use the formulae on a different sheet called "Summary" within the same workbook but cannot crack where to put the Materials! reference and just get error messages.
e.g. =Materials!(Sumproduct(--($k$5:$k$1001="P10001"),--$F$2:$F$1001))
or
=Sumproduct(--(Materials!($k$5:$k$1001="P10001")),(--Materials!$F$2:$F$1001))
Can anyone provide me with the correct formulae please?
Many Thanks & Regards
Sparkgirl
I have it 99% complete, but am struggling with one little part.
I have been using a Sumproduct query to summarise information in one spreadsheet called Materials
=Sumproduct(--($k$5:$k$1001="P10001"),--$F$2:$F$1001)
The formulae calculates the cost of the materials used in job "P10001" - It works prefectly.
Final step I want to use the formulae on a different sheet called "Summary" within the same workbook but cannot crack where to put the Materials! reference and just get error messages.
e.g. =Materials!(Sumproduct(--($k$5:$k$1001="P10001"),--$F$2:$F$1001))
or
=Sumproduct(--(Materials!($k$5:$k$1001="P10001")),(--Materials!$F$2:$F$1001))
Can anyone provide me with the correct formulae please?
Many Thanks & Regards
Sparkgirl