Cell reference being changed to a specific criteria in IF formula...HELP!

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:

=3DSUMPRODUCT(IF('Offshore Pipelay 2'!$D$3:$D$1000=3D$E$10,0&IF('Offshore
Pipelay 2'!$O$3:$O$1000=3D$C15,0))+COUNTIFS('Offshore Pipelay 2'!$V$3:$V
$1000,">=3D"&$M$10,'Offshore Pipelay 2'!$V$3:$V$1000,"<=3D"&$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 =93Offshore Pipelay 2=94
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:

=3DSUMPRODUCT(IF('Offshore Pipelay 2'!$D$3:$D$1000=3D$E$10,0&IF('Offshore
Pipelay 2'!$O$3:$O$1000=3D$C15,0))+COUNTIFS('Offshore Pipelay 2'!$V$3:$V
$1000,">=3D"&$M$10,'Offshore Pipelay 2'!$V$3:$V$1000,"<=3D"&$O$10))
This shows that the name of the worksheet & its range seems to be
picked up as the words Offshore Pipelay and not =93Offshore Pipelay
2+Range=94??? 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:

=3DSUMPRODUCT(IF(TRUE,0&IF('=94Detailed Design=94=3D=92Procurement=94,0))
+COUNTIFS('Offshore Pipelay 2'!$V$3:$V$1000,">=3D"&$M$10,'Offshore
Pipelay 2'!$V$3:$V$1000,"<=3D"&$O$10))

This obviously doesn=92t work as =93Detailed Design=94 is not an exact matc=
h
of =93 Procurement=94!!!  Why is the worksheet reference getting changed
to an entry within the Column and not remaining as a range? I think
I=92ve found where the issue is but I have no idea how to solve it!
Questions questions questions!

Uh really need some help!
0
1/18/2012 7:58:02 AM
excel 39879 articles. 2 followers. Follow

0 Replies
344 Views

Similar Articles

[PageSpeed] 7

Reply:

Similar Artilces: