SUMPRODUCT

[mar050703]

I have the formula in an excel cell:

=IF(SUMPRODUCT(($F3=Sname)*(Sfrom<=G$2)*(Sto>=+G$2))+IF(WEEKDAY(G$2,2)>5,2,0)=1,"A","")[code]

The named ranges Sname, Sfrom and Sto are all relating to another sheet.  This gives an error #NUM!.

Whenever I have the named ranges on the same sheet it works, why is this?

Please can anyone help.

I could not see any clues from reading the previous posts to do with this function.

 

[xlbo]

You may want to post this in the MSOffice forum rather than this one

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[xlbo]

...but it sounds like you have sheet level names

if you your sheet in which the data resides is called Sheet1 then try:

IF(SUMPRODUCT(($F3=Sheet1!Sname)*(Sheet1!Sfrom<=G$2)*(Sheet1!Sto>=+G$2))+IF(WEEKDAY(G$2,2)>5,2,0)=1,"A","")

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question