What does the double dash or "--" do when using it in a function? (and other questions)

[CanoAko]

This is a really hard thing to do a search on, so I'm posting it here
to hopefully be enlightened.

1) What does the double dash "--" do in a function?
2) Specifically, what does it do in this function:
=OFFSET(Y!$B$2,,,SUMPRODUCT(--(Y!$B$2:$B$2000<>"")))

3) Final and related question, I want to have a drop down menu where
the options change based on the field to the left of the menu.
Date Name Account #
7/26/06 Joe 123456
7/26/06 Dan 654321
7/30/06 Joe 987654

I would like to have the account number pull from a long list of
account numbers like this...
Joe 123456
Joe 987654
Dan 654321
Dan 456789
Joe 555555

and have the drop down menu give me the option...
Row1 7/26/06 Joe (123465 / 987654 / 555555)
Row2 7/26/06 Dan (654321 / 456789)
Row3 7/30/06 Joe (123465 / 987654 / 555555)

so I can select which account number I want to use.

Any ideas?

 

[Bob Phillips]

This is a really hard thing to do a search on, so I'm posting it here
to hopefully be enlightened.

1) What does the double dash "--" do in a function?

See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation

2) Specifically, what does it do in this function:
=OFFSET(Y!$B$2,,,SUMPRODUCT(--(Y!$B$2:$B$2000<>"")))

The test for Y!$B$2:$B$2000<>"" returns an array of TRUE/FALSE values, --
coerces this to an array of 1/0 values which SP sums and passes to OFFSET to
determine how many rows are included

3) Final and related question, I want to have a drop down menu where
the options change based on the field to the left of the menu.
Date Name Account #
7/26/06 Joe 123456
7/26/06 Dan 654321
7/30/06 Joe 987654

I would like to have the account number pull from a long list of
account numbers like this...
Joe 123456
Joe 987654
Dan 654321
Dan 456789
Joe 555555

and have the drop down menu give me the option...
Row1 7/26/06 Joe (123465 / 987654 / 555555)
Row2 7/26/06 Dan (654321 / 456789)
Row3 7/30/06 Joe (123465 / 987654 / 555555)

so I can select which account number I want to use.

Create separate lists of accounts for each person, give them a range name of
say Joe, Dan etc. and in the DV use a List type with values of =Joe, =Dan as
appropriate.

 

[CanoAko]

Create separate lists of accounts for each person, give them a range name of
say Joe, Dan etc. and in the DV use a List type with values of =Joe, =Dan as
appropriate.

That doesn't really work when there are 100+ accounts. It would work
with a small list, though.

 

[Bob Phillips]

Why not?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[CanoAko]

Why not?

Well, because it wouldn't save me any time if I had to do it for each
new account we had to set up. It would be easier to manually look it
up on the table. It isn't really scalable to how the sheet will be
used. Eventually we could have thousands and that is just not going to
work, even though I actually could write a macro to do it. I'm sure
there must be a way to do it with a formula in the Data Validation
window.

 

[Bob Phillips]

Write a macro to do it.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)