DSum Criteria Issue

[rpaulson]

Hello all,

I have a list of data with salesperson and qty. (Excel 2003)

I am using dsum and it was working great until I add a new sales man who happened to be the son of another salesman.

example.
when I just had Bill Gates his total (DSUM) was fine.
now I added Bill Gates Jr.
since then Bill Gates totals now include Bill Gates AND Bill Gates Jr.
FYI - Bill Gates Jr. totals are correct.
anyone have a fix?

Ross

 

[pgc01]

Hi Ross

If you write in the criteria

Bill Gates

it will match anything starting with Bill Gates. To match only exactly Bill Gates, write in the criteria instead the formula

="=Bill Gates"

 

[rpaulson]

I see that putting the ="=Bill Gates" fixes the issue.

Unfortunately my list of names is a drop down of about 200 people from a list on another sheet.
Is there another way to "Fix" this issue?

Ross

 

[pgc01]

Try getting the name from your dropdown, like:

="="&Sheet2!A1

 

[rpaulson]

I'm getting there. Now trying to write the criteria with VBA

this works

Range("R2") = "=""=Bill Gates"""

But I can't get it when I use a cell referance

Range("R2") = "=""=" & RANGE("A1") &"""

any one have the syntax I need with all these = signs and " going on?

 

[pgc01]

Almost, try:

Range("R2").Formula = "=""=" & Range("A1") & """"

 

[rpaulson]

It appears to be working now. Thanks

 

[rpaulson]

It works fine unless I have an entry with a quote in it.

Ex. if A1 is 1/2" Socket then I get an error in the code.
Any work around for this?

Thanks to all,

Ross

 

[pgc01]

Hi Again

Since to specify a double quote inside a doublequoted string you have to double it, try:

Range("R2").Formula = "=""=" & Replace(Range("A1"), """", """""") & """"

( 22 " in the statement )

 

[rpaulson]

thanks again