L
LCTECH001
Can anyone possibly help with the following?
I have the following set of data:
Row No Column C Column E Column M
Column O
9 Package ID Package Reporting Date Reporting Date + 30days
10 2.0 Offshore Pipelay 18 Jan 2012 17 Feb 2012
11
12
13 Phase (Cell Ref: C13) Due in 30 days (Cell
Ref:M13)
14
15 Detailed Design Formula
16 Procurement
17 Fabrication
18 Installation
19 Pre-commissioning
20 Commissioning
21 Offshore Pipelay
22 Operations
The formula in Cell Ref: M15 is as shown below:
=SUMPRODUCT(IF('Offshore Pipelay 2'!$D$3:$D$1000=$E$10,0&IF('Offshore
Pipelay 2'!$O$3:$O$1000=$C15,0))+COUNTIFS('Offshore Pipelay 2'!$V$3:$V
$1000,">="&$M$10,'Offshore Pipelay 2'!$V$3:$V$1000,"<="&$O$10))
Where: 'Offshore Pipelay 2' is the name of the worksheet containing
the main data where Column D contains duplicates of 10 possible
entries (Package).Column O contains duplicates of 8 possible entries
(Phase) and Column V contains various dates.
I want the formula to look within the worksheet “Offshore Pipelay 2”
and find the various criteria from the table shown previously within
it and then count all those which fall in between the date criteria.
The formula seems to work giving me a number of 3 which I have checked
by manually counting the entries which match all the criteria. My
problem is when I copy the formula down to search for the next Phase
which is the only variable which changes I get a #VALUE! error?
I looked at the formula using Evaluate Formula which gave the
following info:
Evaluation:
=SUMPRODUCT(IF('Offshore Pipelay 2'!$D$3:$D$1000=$E$10,0&IF('Offshore
Pipelay 2'!$O$3:$O$1000=$C15,0))+COUNTIFS('Offshore Pipelay 2'!$V$3:$V
$1000,">="&$M$10,'Offshore Pipelay 2'!$V$3:$V$1000,"<="&$O$10))
This shows that the name of the worksheet & its range seems to be
picked up as the words Offshore Pipelay and not “Offshore Pipelay
2+Range”??? How can it be read as this and not retained as a list of
cell entries? The criteria is correctly labelled as the word Offshore
Pipelay as per the table so the reason it is working at this stage is
because all 53 values in Column D is Offshore Pipelay.
When I evaluate further for the criteria in $C16 which is the word
Procurement the evaluation shows the following:
Evaluation:
=SUMPRODUCT(IF(TRUE,0&IF('”Detailed Design”=’Procurement”,0))
+COUNTIFS('Offshore Pipelay 2'!$V$3:$V$1000,">="&$M$10,'Offshore
Pipelay 2'!$V$3:$V$1000,"<="&$O$10))
This obviously doesn’t work as “Detailed Design” is not an exact match
of “ Procurement”!!! Why is the worksheet reference getting changed
to an entry within the Column and not remaining as a range? I think
I’ve found where the issue is but I have no idea how to solve it!
Questions questions questions!
Uh really need some help!
I have the following set of data:
Row No Column C Column E Column M
Column O
9 Package ID Package Reporting Date Reporting Date + 30days
10 2.0 Offshore Pipelay 18 Jan 2012 17 Feb 2012
11
12
13 Phase (Cell Ref: C13) Due in 30 days (Cell
Ref:M13)
14
15 Detailed Design Formula
16 Procurement
17 Fabrication
18 Installation
19 Pre-commissioning
20 Commissioning
21 Offshore Pipelay
22 Operations
The formula in Cell Ref: M15 is as shown below:
=SUMPRODUCT(IF('Offshore Pipelay 2'!$D$3:$D$1000=$E$10,0&IF('Offshore
Pipelay 2'!$O$3:$O$1000=$C15,0))+COUNTIFS('Offshore Pipelay 2'!$V$3:$V
$1000,">="&$M$10,'Offshore Pipelay 2'!$V$3:$V$1000,"<="&$O$10))
Where: 'Offshore Pipelay 2' is the name of the worksheet containing
the main data where Column D contains duplicates of 10 possible
entries (Package).Column O contains duplicates of 8 possible entries
(Phase) and Column V contains various dates.
I want the formula to look within the worksheet “Offshore Pipelay 2”
and find the various criteria from the table shown previously within
it and then count all those which fall in between the date criteria.
The formula seems to work giving me a number of 3 which I have checked
by manually counting the entries which match all the criteria. My
problem is when I copy the formula down to search for the next Phase
which is the only variable which changes I get a #VALUE! error?
I looked at the formula using Evaluate Formula which gave the
following info:
Evaluation:
=SUMPRODUCT(IF('Offshore Pipelay 2'!$D$3:$D$1000=$E$10,0&IF('Offshore
Pipelay 2'!$O$3:$O$1000=$C15,0))+COUNTIFS('Offshore Pipelay 2'!$V$3:$V
$1000,">="&$M$10,'Offshore Pipelay 2'!$V$3:$V$1000,"<="&$O$10))
This shows that the name of the worksheet & its range seems to be
picked up as the words Offshore Pipelay and not “Offshore Pipelay
2+Range”??? How can it be read as this and not retained as a list of
cell entries? The criteria is correctly labelled as the word Offshore
Pipelay as per the table so the reason it is working at this stage is
because all 53 values in Column D is Offshore Pipelay.
When I evaluate further for the criteria in $C16 which is the word
Procurement the evaluation shows the following:
Evaluation:
=SUMPRODUCT(IF(TRUE,0&IF('”Detailed Design”=’Procurement”,0))
+COUNTIFS('Offshore Pipelay 2'!$V$3:$V$1000,">="&$M$10,'Offshore
Pipelay 2'!$V$3:$V$1000,"<="&$O$10))
This obviously doesn’t work as “Detailed Design” is not an exact match
of “ Procurement”!!! Why is the worksheet reference getting changed
to an entry within the Column and not remaining as a range? I think
I’ve found where the issue is but I have no idea how to solve it!
Questions questions questions!
Uh really need some help!