Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What does the double dash or "--" do when using it in a function? (and other questions)
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What does the double dash or "--" do when using it in a function? (and other questions)
"CanoAko" wrote in message ups.com... 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What does the double dash or "--" do when using it in a function? (and other questions)
Bob Phillips wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What does the double dash or "--" do when using it in a function? (and other questions)
Why not?
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "CanoAko" wrote in message oups.com... Bob Phillips wrote: 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What does the double dash or "--" do when using it in a function? (and other questions)
Bob Phillips wrote: 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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What does the double dash or "--" do when using it in a function? (and other questions)
Write a macro to do it.
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "CanoAko" wrote in message oups.com... Bob Phillips wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|