Sumproduct Question

=SUMPRODUCT(($E$25:$E$2000=4)*($F$25:$F$2000=6.5)*(P25:P2000))

This above formula achieves the desired result.  However, when  I add
a fourth column (below) I get a Value error.

I think I'm close...any ideas?

=SUMPRODUCT(($E$25:$E$2000=4)*($F$25:$F$2000=6.5)*(P25:P2000)*($M$25:$M$2000))
0
JohnP26 (33)
1/10/2008 12:28:04 AM
excel 39879 articles. 2 followers. Follow

17 Replies
1442 Views

Similar Articles

[PageSpeed] 40

First, I'd watch those addresses.  I bet you want to use $P$25:$P$2000, too.

Second, do you  have any text values in M25:M2000--or any errors in that range?

If you have some text, you can use a different formula to ignore text:

=SUMPRODUCT(--($E$25:$E$2000=4),
            --($F$25:$F$2000=6.5),
            $P$25:$P$2000,
            $M$25:$M$2000)

Adjust the ranges to match--but you can't use whole columns (except in xl2007). 

=sumproduct() likes to work with numbers.  The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

===
ps.  When you're looking for text or errors, remember to look in hidden rows,
too.




JP wrote:
> 
> =SUMPRODUCT(($E$25:$E$2000=4)*($F$25:$F$2000=6.5)*(P25:P2000))
> 
> This above formula achieves the desired result.  However, when  I add
> a fourth column (below) I get a Value error.
> 
> I think I'm close...any ideas?
> 
> =SUMPRODUCT(($E$25:$E$2000=4)*($F$25:$F$2000=6.5)*(P25:P2000)*($M$25:$M$2000))

-- 

Dave Peterson
0
petersod (12005)
1/10/2008 12:37:27 AM
On Wed, 09 Jan 2008 18:37:27 -0600, Dave Peterson
<petersod@verizonXSPAM.net> wrote:

>First, I'd watch those addresses.  I bet you want to use $P$25:$P$2000, too.

No.  It was correct.
>
>Second, do you  have any text values in M25:M2000--or any errors in that range?

None.
>
>If you have some text, you can use a different formula to ignore text:
>
>=SUMPRODUCT(--($E$25:$E$2000=4),
>            --($F$25:$F$2000=6.5),
>            $P$25:$P$2000,
>            $M$25:$M$2000)

Doing it this way doesn't return the value error, but it gives
different answers.  I tallied the results by hand and for some reason
they are off.




>Adjust the ranges to match--but you can't use whole columns (except in xl2007). 
>
>=sumproduct() likes to work with numbers.  The -- stuff changes trues and falses
>to 1's and 0's.
>
>Bob Phillips explains =sumproduct() in much more detail here:
>http://www.xldynamic.com/source/xld.SUMPRODUCT.html
>
>And J.E. McGimpsey has some notes at:
>http://mcgimpsey.com/excel/formulae/doubleneg.html
>
>===
>ps.  When you're looking for text or errors, remember to look in hidden rows,
>too.
>
>
>
>
>JP wrote:
>> 
>> =SUMPRODUCT(($E$25:$E$2000=4)*($F$25:$F$2000=6.5)*(P25:P2000))
>> 
>> This above formula achieves the desired result.  However, when  I add
>> a fourth column (below) I get a Value error.
>> 
>> I think I'm close...any ideas?
>> 
>> =SUMPRODUCT(($E$25:$E$2000=4)*($F$25:$F$2000=6.5)*(P25:P2000)*($M$25:$M$2000))

0
JohnP26 (33)
1/10/2008 10:20:32 AM
Are you sure that you don't have a #VALUE in one of the cells?

-- 
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"JP" <JohnP26@msn.com> wrote in message 
news:n3sbo35gad0vla65c36d8d0pn5057tvt7d@4ax.com...
> On Wed, 09 Jan 2008 18:37:27 -0600, Dave Peterson
> <petersod@verizonXSPAM.net> wrote:
>
>>First, I'd watch those addresses.  I bet you want to use $P$25:$P$2000, 
>>too.
>
> No.  It was correct.
>>
>>Second, do you  have any text values in M25:M2000--or any errors in that 
>>range?
>
> None.
>>
>>If you have some text, you can use a different formula to ignore text:
>>
>>=SUMPRODUCT(--($E$25:$E$2000=4),
>>            --($F$25:$F$2000=6.5),
>>            $P$25:$P$2000,
>>            $M$25:$M$2000)
>
> Doing it this way doesn't return the value error, but it gives
> different answers.  I tallied the results by hand and for some reason
> they are off.
>
>
>
>
>>Adjust the ranges to match--but you can't use whole columns (except in 
>>xl2007).
>>
>>=sumproduct() likes to work with numbers.  The -- stuff changes trues and 
>>falses
>>to 1's and 0's.
>>
>>Bob Phillips explains =sumproduct() in much more detail here:
>>http://www.xldynamic.com/source/xld.SUMPRODUCT.html
>>
>>And J.E. McGimpsey has some notes at:
>>http://mcgimpsey.com/excel/formulae/doubleneg.html
>>
>>===
>>ps.  When you're looking for text or errors, remember to look in hidden 
>>rows,
>>too.
>>
>>
>>
>>
>>JP wrote:
>>>
>>> =SUMPRODUCT(($E$25:$E$2000=4)*($F$25:$F$2000=6.5)*(P25:P2000))
>>>
>>> This above formula achieves the desired result.  However, when  I add
>>> a fourth column (below) I get a Value error.
>>>
>>> I think I'm close...any ideas?
>>>
>>> =SUMPRODUCT(($E$25:$E$2000=4)*($F$25:$F$2000=6.5)*(P25:P2000)*($M$25:$M$2000))
> 


0
bob.NGs1 (1661)
1/10/2008 11:46:52 AM
Yes I'm quite sure.  I checked manually, and also many other
computations are used with those columns of data and they return
proper answers.

On Thu, 10 Jan 2008 11:46:52 -0000, "Bob Phillips"
<bob.NGs@somewhere.com> wrote:

>Are you sure that you don't have a #VALUE in one of the cells?

0
JohnP26 (33)
1/10/2008 2:09:13 PM
If you put:
=counta(m25:m2000)
in an empty cell
and 
=count(m25:m2000)
in another 

What do these formulas return?



JP wrote:
> 
> On Wed, 09 Jan 2008 18:37:27 -0600, Dave Peterson
> <petersod@verizonXSPAM.net> wrote:
> 
> >First, I'd watch those addresses.  I bet you want to use $P$25:$P$2000, too.
> 
> No.  It was correct.
> >
> >Second, do you  have any text values in M25:M2000--or any errors in that range?
> 
> None.
> >
> >If you have some text, you can use a different formula to ignore text:
> >
> >=SUMPRODUCT(--($E$25:$E$2000=4),
> >            --($F$25:$F$2000=6.5),
> >            $P$25:$P$2000,
> >            $M$25:$M$2000)
> 
> Doing it this way doesn't return the value error, but it gives
> different answers.  I tallied the results by hand and for some reason
> they are off.
> 
> >Adjust the ranges to match--but you can't use whole columns (except in xl2007).
> >
> >=sumproduct() likes to work with numbers.  The -- stuff changes trues and falses
> >to 1's and 0's.
> >
> >Bob Phillips explains =sumproduct() in much more detail here:
> >http://www.xldynamic.com/source/xld.SUMPRODUCT.html
> >
> >And J.E. McGimpsey has some notes at:
> >http://mcgimpsey.com/excel/formulae/doubleneg.html
> >
> >===
> >ps.  When you're looking for text or errors, remember to look in hidden rows,
> >too.
> >
> >
> >
> >
> >JP wrote:
> >>
> >> =SUMPRODUCT(($E$25:$E$2000=4)*($F$25:$F$2000=6.5)*(P25:P2000))
> >>
> >> This above formula achieves the desired result.  However, when  I add
> >> a fourth column (below) I get a Value error.
> >>
> >> I think I'm close...any ideas?
> >>
> >> =SUMPRODUCT(($E$25:$E$2000=4)*($F$25:$F$2000=6.5)*(P25:P2000)*($M$25:$M$2000))

-- 

Dave Peterson
0
petersod (12005)
1/10/2008 2:41:57 PM
Select that range
Edit|goto|special
Check constants and errors (and uncheck everything else)

Then try the same thing with formulas and errors.

If you have the data filtered, make sure you show all the rows.

JP wrote:
> 
> Yes I'm quite sure.  I checked manually, and also many other
> computations are used with those columns of data and they return
> proper answers.
> 
> On Thu, 10 Jan 2008 11:46:52 -0000, "Bob Phillips"
> <bob.NGs@somewhere.com> wrote:
> 
> >Are you sure that you don't have a #VALUE in one of the cells?

-- 

Dave Peterson
0
petersod (12005)
1/10/2008 2:51:19 PM
I did both and got an answer of "no cells were found."

On Thu, 10 Jan 2008 08:51:19 -0600, Dave Peterson
<petersod@verizonXSPAM.net> wrote:

>Select that range
>Edit|goto|special
>Check constants and errors (and uncheck everything else)
>
>Then try the same thing with formulas and errors.
>
>If you have the data filtered, make sure you show all the rows.
>
>JP wrote:
>> 
>> Yes I'm quite sure.  I checked manually, and also many other
>> computations are used with those columns of data and they return
>> proper answers.
>> 
>> On Thu, 10 Jan 2008 11:46:52 -0000, "Bob Phillips"
>> <bob.NGs@somewhere.com> wrote:
>> 
>> >Are you sure that you don't have a #VALUE in one of the cells?

0
JohnP26 (33)
1/10/2008 9:46:57 PM
221, and 118 respectively.

On Thu, 10 Jan 2008 08:41:57 -0600, Dave Peterson
<petersod@verizonXSPAM.net> wrote:

>=counta(m25:m2000)

0
JohnP26 (33)
1/10/2008 9:48:30 PM
That means that there are 3 values that are not numbers.

=counta() returns the number of cells with something in them.
=count() returns the number of cells with numbers in them.

Select the range
edit|goto|special
and look for numbers or text or blanks

Look in values or formulas or both.

JP wrote:
> 
> 221, and 118 respectively.
> 
> On Thu, 10 Jan 2008 08:41:57 -0600, Dave Peterson
> <petersod@verizonXSPAM.net> wrote:
> 
> >=counta(m25:m2000)

-- 

Dave Peterson
0
petersod (12005)
1/10/2008 10:06:06 PM
OK, checked all of that out.  This is the formula I have in the fist
cell. 

 =IF(I26="","",I26-H26)

I drag it down the column.  For every new day that data is entered I
skip a space.  I couldn't find the three cells.



On Thu, 10 Jan 2008 16:06:06 -0600, Dave Peterson
<petersod@verizonXSPAM.net> wrote:

>That means that there are 3 values that are not numbers.
>
>=counta() returns the number of cells with something in them.
>=count() returns the number of cells with numbers in them.
>
>Select the range
>edit|goto|special
>and look for numbers or text or blanks
>
>Look in values or formulas or both.
>
>JP wrote:
>> 
>> 221, and 118 respectively.
>> 
>> On Thu, 10 Jan 2008 08:41:57 -0600, Dave Peterson
>> <petersod@verizonXSPAM.net> wrote:
>> 
>> >=counta(m25:m2000)

0
JohnP26 (33)
1/10/2008 11:01:12 PM
Do you have the worksheet filtered?

Maybe your error or text value is in one of the hidden rows???



JP wrote:
> 
> OK, checked all of that out.  This is the formula I have in the fist
> cell.
> 
>  =IF(I26="","",I26-H26)
> 
> I drag it down the column.  For every new day that data is entered I
> skip a space.  I couldn't find the three cells.
> 
> On Thu, 10 Jan 2008 16:06:06 -0600, Dave Peterson
> <petersod@verizonXSPAM.net> wrote:
> 
> >That means that there are 3 values that are not numbers.
> >
> >=counta() returns the number of cells with something in them.
> >=count() returns the number of cells with numbers in them.
> >
> >Select the range
> >edit|goto|special
> >and look for numbers or text or blanks
> >
> >Look in values or formulas or both.
> >
> >JP wrote:
> >>
> >> 221, and 118 respectively.
> >>
> >> On Thu, 10 Jan 2008 08:41:57 -0600, Dave Peterson
> >> <petersod@verizonXSPAM.net> wrote:
> >>
> >> >=counta(m25:m2000)

-- 

Dave Peterson
0
petersod (12005)
1/10/2008 11:43:56 PM
You *should not* use the asterisk form of Sumproduct() when you have your 
calculating column(s) populated by formulas that may return zero length 
strings (nulls - "" ), as you *do* have!
These returns are considered TEXT, and will generate the #Value! error when 
Column I is empty, causing a null ( "" ) return.in Column M.

The asterisk form will calculate text numbers, as long as they look like 
numbers, but will error out on all other text values that are non-numeric.
The unary form (suggested by Dave), will by-pass text *AND* numeric text, 
and just calculate "true" numbers.

So, if your calculations are different using Dave's suggestion, I would 
guess that you have *both* numeric and text numbers present in your 
datalist.

If you would/could revise your formula to:

=IF(I26="",0,I26-H26)

then you could continue using the asterisk form, since zero would replace 
the text null ( "" ).

You might try it and post back with results.
-- 
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"JP" <JohnP26@msn.com> wrote in message 
news:7n8do3duhaec4llvhueelurr7kg6dc10ea@4ax.com...
> OK, checked all of that out.  This is the formula I have in the fist
> cell.
>
> =IF(I26="","",I26-H26)
>
> I drag it down the column.  For every new day that data is entered I
> skip a space.  I couldn't find the three cells.
>
>
>
> On Thu, 10 Jan 2008 16:06:06 -0600, Dave Peterson
> <petersod@verizonXSPAM.net> wrote:
>
>>That means that there are 3 values that are not numbers.
>>
>>=counta() returns the number of cells with something in them.
>>=count() returns the number of cells with numbers in them.
>>
>>Select the range
>>edit|goto|special
>>and look for numbers or text or blanks
>>
>>Look in values or formulas or both.
>>
>>JP wrote:
>>>
>>> 221, and 118 respectively.
>>>
>>> On Thu, 10 Jan 2008 08:41:57 -0600, Dave Peterson
>>> <petersod@verizonXSPAM.net> wrote:
>>>
>>> >=counta(m25:m2000)
> 


0
ragdyer1 (4060)
1/11/2008 1:34:12 AM
Not sure I understand all of it, but it worked.  CountA and Count both
return 289, so I guess that did the trick.  The only problem now is
that $0.00 shows up in the skipped rows, so I guess I'll have to use
condiditional formatting to get rid of them.

Appreciate the advice.  Thanks a lot.

On Thu, 10 Jan 2008 17:34:12 -0800, "RagDyer" <ragdyer@cutoutmsn.com>
wrote:

>You *should not* use the asterisk form of Sumproduct() when you have your 
>calculating column(s) populated by formulas that may return zero length 
>strings (nulls - "" ), as you *do* have!
>These returns are considered TEXT, and will generate the #Value! error when 
>Column I is empty, causing a null ( "" ) return.in Column M.
>
>The asterisk form will calculate text numbers, as long as they look like 
>numbers, but will error out on all other text values that are non-numeric.
>The unary form (suggested by Dave), will by-pass text *AND* numeric text, 
>and just calculate "true" numbers.
>
>So, if your calculations are different using Dave's suggestion, I would 
>guess that you have *both* numeric and text numbers present in your 
>datalist.
>
>If you would/could revise your formula to:
>
>=IF(I26="",0,I26-H26)
>
>then you could continue using the asterisk form, since zero would replace 
>the text null ( "" ).
>
>You might try it and post back with results.

0
JohnP26 (33)
1/11/2008 3:43:06 PM
Found a gliltch.  Now this formula in a column two columns over no
longer works.  

=IF(M28="","",IF(M29="",SUM(M$26:M28)-SUM(O$26:O27),""))

On Fri, 11 Jan 2008 07:43:06 -0800, JP <JohnP26@msn.com> wrote:

>Not sure I understand all of it, but it worked.  CountA and Count both
>return 289, so I guess that did the trick.  The only problem now is
>that $0.00 shows up in the skipped rows, so I guess I'll have to use
>condiditional formatting to get rid of them.
>
>Appreciate the advice.  Thanks a lot.
>
>On Thu, 10 Jan 2008 17:34:12 -0800, "RagDyer" <ragdyer@cutoutmsn.com>
>wrote:
>
>>You *should not* use the asterisk form of Sumproduct() when you have your 
>>calculating column(s) populated by formulas that may return zero length 
>>strings (nulls - "" ), as you *do* have!
>>These returns are considered TEXT, and will generate the #Value! error when 
>>Column I is empty, causing a null ( "" ) return.in Column M.
>>
>>The asterisk form will calculate text numbers, as long as they look like 
>>numbers, but will error out on all other text values that are non-numeric.
>>The unary form (suggested by Dave), will by-pass text *AND* numeric text, 
>>and just calculate "true" numbers.
>>
>>So, if your calculations are different using Dave's suggestion, I would 
>>guess that you have *both* numeric and text numbers present in your 
>>datalist.
>>
>>If you would/could revise your formula to:
>>
>>=IF(I26="",0,I26-H26)
>>
>>then you could continue using the asterisk form, since zero would replace 
>>the text null ( "" ).
>>
>>You might try it and post back with results.

0
JohnP26 (33)
1/11/2008 7:54:54 PM
Look, if data is to be keyed in, or imported in, the asterisk form of 
Sumproduct is the way to go ... warns you of mistakes ... BUT ... if you're 
populating your calculating columns with formulas returning nulls, you 
*should* be using the unary form, as Dave suggested.

Now that you've revised your formula to return zeroes, compare both forms of 
the function and see if they both return the same result.

If they don't, that means that you have true numbers, and text that looks 
like numbers, in your datalist.
That's not a good thing to have happening, at any time!

You can use a 'helper" column, and reference it to any questionable columns, 
to see if all your numbers are numeric numbers.

Simply use this  in any vacant column:

=Isnumber(M26)
and drag it down to see if every row returns a TRUE.

If you see a FALSE, that's the row(s) to fix!

On the other hand, if you insist on using the asterisk form, and you're 
going to use CF to fix the display, then you'll have to revise *all* your 
other formulas to match the revised value of your calculating columns.

FROM:
=IF(M28="","",IF(M29="",SUM(M$26:M28)-SUM(O$26:O27),""))

TO:
=IF(M28=0,0,IF(M29=0,SUM(M$26:M28)-SUM(O$26:O27),0))

It *may* become a nightmare trying to change everything around.

Your choice!
-- 
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"JP" <JohnP26@msn.com> wrote in message 
news:caifo3l1rrjkj4rjf7ldjdh8d6ctdghul8@4ax.com...
> Found a gliltch.  Now this formula in a column two columns over no
> longer works.
>
> =IF(M28="","",IF(M29="",SUM(M$26:M28)-SUM(O$26:O27),""))
>
> On Fri, 11 Jan 2008 07:43:06 -0800, JP <JohnP26@msn.com> wrote:
>
>>Not sure I understand all of it, but it worked.  CountA and Count both
>>return 289, so I guess that did the trick.  The only problem now is
>>that $0.00 shows up in the skipped rows, so I guess I'll have to use
>>condiditional formatting to get rid of them.
>>
>>Appreciate the advice.  Thanks a lot.
>>
>>On Thu, 10 Jan 2008 17:34:12 -0800, "RagDyer" <ragdyer@cutoutmsn.com>
>>wrote:
>>
>>>You *should not* use the asterisk form of Sumproduct() when you have your
>>>calculating column(s) populated by formulas that may return zero length
>>>strings (nulls - "" ), as you *do* have!
>>>These returns are considered TEXT, and will generate the #Value! error 
>>>when
>>>Column I is empty, causing a null ( "" ) return.in Column M.
>>>
>>>The asterisk form will calculate text numbers, as long as they look like
>>>numbers, but will error out on all other text values that are 
>>>non-numeric.
>>>The unary form (suggested by Dave), will by-pass text *AND* numeric text,
>>>and just calculate "true" numbers.
>>>
>>>So, if your calculations are different using Dave's suggestion, I would
>>>guess that you have *both* numeric and text numbers present in your
>>>datalist.
>>>
>>>If you would/could revise your formula to:
>>>
>>>=IF(I26="",0,I26-H26)
>>>
>>>then you could continue using the asterisk form, since zero would replace
>>>the text null ( "" ).
>>>
>>>You might try it and post back with results.
> 


0
ragdyer1 (4060)
1/11/2008 8:24:02 PM
I had to change the formula in three columns as you suggested to make
everything work.  Thanks for your help.

On Fri, 11 Jan 2008 12:24:02 -0800, "RagDyer" <ragdyer@cutoutmsn.com>
wrote:

>Look, if data is to be keyed in, or imported in, the asterisk form of 
>Sumproduct is the way to go ... warns you of mistakes ... BUT ... if you're 
>populating your calculating columns with formulas returning nulls, you 
>*should* be using the unary form, as Dave suggested.
>
>Now that you've revised your formula to return zeroes, compare both forms of 
>the function and see if they both return the same result.
>
>If they don't, that means that you have true numbers, and text that looks 
>like numbers, in your datalist.
>That's not a good thing to have happening, at any time!
>
>You can use a 'helper" column, and reference it to any questionable columns, 
>to see if all your numbers are numeric numbers.
>
>Simply use this  in any vacant column:
>
>=Isnumber(M26)
>and drag it down to see if every row returns a TRUE.
>
>If you see a FALSE, that's the row(s) to fix!
>
>On the other hand, if you insist on using the asterisk form, and you're 
>going to use CF to fix the display, then you'll have to revise *all* your 
>other formulas to match the revised value of your calculating columns.
>
>FROM:
>=IF(M28="","",IF(M29="",SUM(M$26:M28)-SUM(O$26:O27),""))
>
>TO:
>=IF(M28=0,0,IF(M29=0,SUM(M$26:M28)-SUM(O$26:O27),0))
>
>It *may* become a nightmare trying to change everything around.
>
>Your choice!

0
JohnP26 (33)
1/11/2008 11:37:22 PM
You're very welcome, and thank you for the feed-back.
-- 
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"JP" <JohnP26@msn.com> wrote in message 
news:ocvfo3h70u8qsd3i5mrugsant6o35029ut@4ax.com...
>I had to change the formula in three columns as you suggested to make
> everything work.  Thanks for your help.
>
> On Fri, 11 Jan 2008 12:24:02 -0800, "RagDyer" <ragdyer@cutoutmsn.com>
> wrote:
>
>>Look, if data is to be keyed in, or imported in, the asterisk form of
>>Sumproduct is the way to go ... warns you of mistakes ... BUT ... if 
>>you're
>>populating your calculating columns with formulas returning nulls, you
>>*should* be using the unary form, as Dave suggested.
>>
>>Now that you've revised your formula to return zeroes, compare both forms 
>>of
>>the function and see if they both return the same result.
>>
>>If they don't, that means that you have true numbers, and text that looks
>>like numbers, in your datalist.
>>That's not a good thing to have happening, at any time!
>>
>>You can use a 'helper" column, and reference it to any questionable 
>>columns,
>>to see if all your numbers are numeric numbers.
>>
>>Simply use this  in any vacant column:
>>
>>=Isnumber(M26)
>>and drag it down to see if every row returns a TRUE.
>>
>>If you see a FALSE, that's the row(s) to fix!
>>
>>On the other hand, if you insist on using the asterisk form, and you're
>>going to use CF to fix the display, then you'll have to revise *all* your
>>other formulas to match the revised value of your calculating columns.
>>
>>FROM:
>>=IF(M28="","",IF(M29="",SUM(M$26:M28)-SUM(O$26:O27),""))
>>
>>TO:
>>=IF(M28=0,0,IF(M29=0,SUM(M$26:M28)-SUM(O$26:O27),0))
>>
>>It *may* become a nightmare trying to change everything around.
>>
>>Your choice!
> 


0
ragdyer1 (4060)
1/12/2008 12:34:02 AM
Reply:

Similar Artilces:

Another Access Question
Hi Everyone, Is it possible to place the front end (forms) of an Access Databse on a web page, so that users can log on via the net and enter data into the forms. I am guessing that the front end would have to stand alone but not sure ! Any advice would be great Thanks will07 <will07@discussions.microsoft.com> wrote: >Is it possible to place the front end (forms) of an Access Databse on a web >page, so that users can log on via the net and enter data into the forms. I >am guessing that the front end would have to stand alone but not sure ! Can't be done using...

look up question #2
I have a list of people with their system rights in the next column by group. Each person may have more than one type of right in the list as follows John Smith Edit Joe Doe Submit Cathy Dory Edit John Smith Submit As you can see above, John Smith has both edit and submit rights on this list. What I am looking for is a formula where I can look down the list of names and find all instances of John Smith and then it can tell me if John Smith ever has edit rights or ever has submit rights. What I would like it to do is return a 1 if he has edit rights a...

Yet another Lotus macro question
I have for the most part converted to Excel but have a simple macro that on the surface cannot be converted. The Lotus macro used a database, template and list of branch names. The macro itself started with the name of the first branch, copied it to the template, sucked in the data for that branch and then printed the report. Then it went down to next name on the list and repeated the process until it reached the bottom of the list. In Excel it seems to indicate I have to repeat the steps for each and every branch on the list and not loop back through a 8 line macro that refers to the ...

question about spam
How is it that spam sent to one mailbox will end up in another not mentioned in the "to" address. In other words how is it that spammers get mail destined to Bob in Jim's mailbox? In news:y9CIg.1194$Cq4.62@newssvr25.news.prodigy.net, Mostro <ovelozpleasenospammingme@glasnospampleasefloss.com> typed: > How is it that spam sent to one mailbox will end up in another not > mentioned in the "to" address. In other words how is it that > spammers get mail destined to Bob in Jim's mailbox? The BCC field. It can be used for evil as well as good. ...

NMHDR question about hwndFrom
the documentation says that the hwndFrom attribute is the window handle to the control sending a message. I have a toolbar sitting on a property page. the toolbar is sending the message. Am I correct in assuming that the hwndFrom is the property page? I am having a problem obtaining the window handle of the property page. From the toolbar, I am calling CWnd* pOwner = GetOwner(); fh.hwndFrom = pWnd->GetSafeHwnd(); When I run the debugger pOwner correctly has the value CMyNotesPP, which is the parent window. When I step through the next line hwndFrom has a value in it but when I click th...

Datasheet question
Is it possible to show a Form Header on a Datasheet form ? Thanks Not that I know of, but you could emulate it by creating a new form with a form header (unbound - no rowsource) and place a subform in that form displayed as datasheetview. Maurice "Phil Hunt" wrote: > Is it possible to show a Form Header on a Datasheet form ? > > Thanks > > > No but you can fake it by putting the datasheet form in a subform = control on an unbound main form - Put whatever you'd like on the main = form. --=20 Joan Wild Microsoft Access MVP "Phil Hunt" &l...

CDC::GetTextExtent question
Hi, I have a function that is to resize a status bar text pane when setting text dynamically. GetTextExtent() seems to think the text is almost twice as wide as it actually is so it leaves too much white space. How come? This is my func.: BOOL CMainFrame::SetStatusText(UINT nID, LPCTSTR lpszText) { int index = m_wndStatusBar.CommandToIndex(nID); if (index < 0) return FALSE; UINT nIDRet, nStyle; int cxWidth; m_wndStatusBar.GetPaneInfo(index, nIDRet, nStyle, cxWidth); ASSERT( nID == nIDRet ); CSize sz(cxWidth, 0); CDC* pDC = m_wndStatusBar.GetDC(); if (pDC) { sz = pDC->...

newbie question on multi-dimensional array
Hi all, I have a real lame question about multi-dimensional array operation. Suppose I have defined a two-dimensional array "Array(10,10)". If I want to use the Excel function "Average" to compute the average value of Column, say 10, of that 2D array, is there any way to realize it? I tried "Application.Average(Array(:,10))", but apparently the Excel doesn't like that. (You can see I am a Matlab guy, so please don't laugh at me:) ) Any comments are extremely welcome. -- sammus ------------------------------------------------------------------------...

Microsoft Outlook 2003 tasks view question
In Microsoft Outlook 2003, I am able to define a customized view of each column, under the Tasks section. Is it possible to share the same customized view of the Tasks with other users? In other words, when other users open a shared view of a Task, I want them to see the same column structure as I see. If it is possible to propagate or share my defined view of columns, can someone describe the procedure? Thanks kindly. Joseph There is not an easy "copy this to everyone" command but its possible. When you create the view, use the option to share it with everyone. When they vie...

Sumproduct.......
I have a problem surrounding sumproduct and setting criteria. I need to sumproduct based on row criteria '19:19'. The value for the row criteria is set in an other cell 'E23'. So sum of row 9 and row 17 where 19 is equal to E23. =SUMPRODUCT((OFFSET(A9,,MATCH(E23,19:19,0)-1,(OFFSET(A17,,MATCH(E23,19:19,0)+1)/1)))) Any help greatly appreciated -- Kstalker ------------------------------------------------------------------------ Kstalker's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24699 View this thread: http://www.excelforum.com/showthr...

A few small questions
I have an Excel document with many tab (actually it look like a database, before any other thing no I can't move it to Access (corporate reasons). I also have a restriction to avoid using macro!!!! First is there a way for footer or header information to include info from cells in the speadsheet. Second you can define info to repeate at the top, what about at the bottom of the page. Third the sheet that serve for report can have up to 8 pages, can the printing zone be define by a cell value, so that page x of y (the y part be other than 8). I have the database part in a sheet bu...

formula question #7
Is there a formula that can change the color of cells if revenues drop three months in a row and if so what should the formula be? Hi, Assuming that your data is in row 1, columns A,B,C, with C being the latest data, Try using conditional formatting in cell C1 with the "Formula Is" =IF(AND(C1<B1,B1<A1),1,0) HTH >-----Original Message----- >Is there a formula that can change the color of cells if >revenues drop three months in a row and if so what should >the formula be? >. > Formula only needs =AND(D1<C1,C1<B1,B1<A1) you don't need t...

sumproduct in UDF
Hello all, Thanks for your help, I am stumped. I am using excel 2003 so cannot use the countifs. I have been pouring through the discussion site to try to solve my problem. I am trying to create a UDF that can count the number of instances when two criteria are met. To accomplish this I am trying to use the sumproduct function with the dashes and am aware of the difficulties that it creates. For example, there is a data set where a salesperson has several transactions and each is rated a score 1 through 4 on a number of questions. I want the UDF to return the number of times...

Layout Question Please
Outlook 2003 When I am in the Monthly calendar view I have three small month calendars in the left hand pane which is fine. If I click the folders button on the left, the folders pane shows on the left with the full calendar view on the right which is how I want it. The problem is that in the left pane on top of the folder list it still shows on of those small calendars which takes up room from my folder list and I cannot see all of the folders. How do I configure it so the left pane is all the folder list and does not include the small calendar? Thanks in advance... Bob I don't th...

Question on "Value" function
In trying to use Excel to help solve a mathematical puzzle, I would like to calculate the result of applying the mathematical operator in one cell to the numbers in two other cells. Example: A1 contains 2, B1 contains +, C1 contains 3. In D1, create a formula that will give the result of 2 + 3, i.e. 5. I have tried (among many other attempts) =VALUE(CONCATENATE(A1, B1, C1)), but it gives me a #VALUE! error. What am I missing? Hi this is not possible without using VBA. try the following UDF: Public Function my_calculate(op1 As Range, operand As Range, op2 As Range) my_calculate = Ap...

xml question?
what is the equivalent of this in C# ? foreach (XmlNode xmlConfigNode in xmlConfigDoc["configuration"].ChildNodes) Thansk Sorry I made a mistake here is what I want to convert to C# For Each Node In XmlDocument.Item("configuration").Item("appSettings") "Ray5531" <Ray5531@microsoft.com> wrote in message news:elPIiezOFHA.3408@TK2MSFTNGP14.phx.gbl... > what is the equivalent of this in C# ? > > foreach (XmlNode xmlConfigNode in > xmlConfigDoc["configuration"].ChildNodes) > > > > Thansk > > Hi Ray,...

Back Order Question
Hi, Have a back order question. Normal back orders are working right. In that if you have 10 and the order is for 15. During the lineitem transtaction it will put 5 in the backorder field automatically. What we need is if the customer orders 15 and we have 20, but only ship 10. We want the BO field to automatically fill with 5. Is this possible. Thanks in advance. Dexter Southerland It depends on how you fulfill and if you have cancel and bo enabled in the document types. If you have these enabled and are using Use Separate Fulfillment, you can select 10 to ship and set 5 to BO. T...

Dynamics GP 10 Reporting Question
I heard reporting in GP 10 is upgraded. Does anyone know if it will be anything like Rockton Software Dynamics Report Manager where you can send any report to like Sql reporting services or crystal? ------=_NextPart_0001_70733CB7 Content-Type: text/plain Content-Transfer-Encoding: 7bit I haven't heard anything at all that reporting in 10.0 will be changed. While I guess it's possible, I would assume for now that there won't be any big changes in general. I would expect more MS Office & SQL integration in the next version for sure, but I can't see everything rewrit...

Array range error question
When i try to redim my array i get an out of range error. can someone tel me why? Cells A1..A10 are filled with strings. thanks much Sub Get_names_array() Dim names_array() As Variant 'dynmanic Dim j As Long ReDim names_array(5, 1) j = 0 Range("A1").Select For i = 1 To 10 names_array(j, 0) = ActiveCell.Value names_array(j, 1) = ActiveCell.Address arraylimitFirst = UBound(names_array, 1) 'returns current dimension of 1st array limit, init = 5 If j >= arraylimitFirst Then ReDim Preserve names_array(10, 1) ' << RANGE ERROR ...

Newbie Remote Assistant and Desktop Questions
i Folks: Developing C++ on VS 2008 Pro, XP Pro, Win32. I have a client that recently upgraded from Vista to Windows 7 Ultimate, 32 bit, on his laptop. I recommended he get Ultimate so I could run Remote Desktop over the Internet. This is before I actually tried using Remote Desktop. After getting Remote Desktop up, I see that I really want, to help my client, is Remote Assistant. Both Remote Desktop and Remote Assistant are now running fine with two XP PCs on my LAN, using my Internet address, to connect. I've cobbled together port numbers requiring...

Just a quick question
I want to be able to average a range of cells, some of these cells are 0, and those are the ones I don't to include in on the calculation. Brian, If your data is in "B5:D14"... =SUM(B5:D14)/COUNTIF(B5:D14,">0") Regards, Jim Cone San Francisco, CA "Brian Shafer" <brian.shafer123@insightbb.com> wrote in message news:u5UCb.386665$275.1243012@attbi_s53... > I want to be able to average a range of cells, some of these cells are 0, > and those are the ones I don't to include in on the calculation. > > Brian, If your range actually...

=CELL("filename") function related question
Is there a function in Excel that only picks up the sheet name in a workbook? I realised that =CELL("filename") function provides the full path, workbook & the worksheet information. Thanks in advance for any help & suggestions. Take a look here: http://www.mcgimpsey.com/excel/formulae/cell_function.html In article <1105122023.529944.145500@z14g2000cwz.googlegroups.com>, "wfcpyc" <wfcpyc@yahoo.com.au> wrote: > Is there a function in Excel that only picks up the sheet name in a > workbook? I realised that =CELL("filename") fun...

Licensing Questions
I'm building an iSCSI host for a client of mine, and was wondering what the license requirements would be. Here is he question, If I install my iSCSI host software (in this case StarWind) on a windows 2003 Platform, what kind of license do I need, ie do I only need the OS license or do I also need CLIs. Since no actual "User" will be accessing this Server Directly (they might be using a VMDK stored on the host LUN, but that would be via my VMware servers... basiclly, I'm using the iSCSI host, to host disk space for 2 or 3 VMWare ESXi hosts, hosting 2 or 3 VMs which...

GP Report Modifier Question (version 9.0)
On our invoice report I changed the format of a custom field to a custom format definition (that I named BB2 with 2 decimal places and show the currency symbol). Now our Inventory History Sales Summary Report prints all of the Total Quantities for the item with a currency symbol also, why is that? Is your Inventory History Sales Summary Report modified? Have you opened the field to check and make sure no format has been assigned to it? ...

Disaster Recovery Question!
I'm sort of new at this. I have 1 server running 2003 as DC/DNS/DHCP and I have another server running Exchange 2003. I want to create an off site disaster recover network. I know I can create the connection with a VPN. I know I can do a backup DC and a secondary DNS. My questions is can I create a backup Exchange 2003 Server? Is this possible? I know DC/DNS are small enough that it can easily be updated on the secondary server. How do you guys set up a secondary Exchange 2003 server? I hope you guys can give me an answer. Thanks! Phil If you mean real-time replication ...