Sumproduct with data validation show all

[robcarr]

Hi,

I am using this formula

=SUMPRODUCT((OFFSET(Data!$D$2,0,0,COUNTA(Data!$D$2:$D$4000))=$A5)*(OFFSET(Data!$E$2,0,0,COUNTA(Data!$E$2:$E$4000))=$B$2)*(OFFSET(Data!$G$2,0,0,COUNTA(Data!$G$2:$G$4000))=C$4))

to get the data based on a selection within a data validation cell (b2)

how can i get a show all option within data validation to give me all the info.

I have tried a * within the validation, but all values come up 0.

any ideas.



Hope this is of use, Rob.

 

[GlennUK]

Can you exaplin what you think your SUMPRODUCT will produce?



Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[robcarr]

all values,#

i have done it now by using an if and then altering the sumproduct if the "if" is true.

Hope this is of use, Rob.

 

[GlennUK]

Oh right, I think I understand what you were after now.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[SkipVought]

As far as I can see, that's simply identical to...
[tt]
=SUMPRODUCT((Data!$D$2:$D$4000=$A5)*(Data!$E$2:$E$4000))=$B$2)*(Data!$G$2:$G$4000))=C$4))
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[robcarr]

not quiet what i was after, here is my way of doing this.

=IF($B$1="",SUMPRODUCT((OFFSET(Data!$D$2,0,0,COUNTA(Data!$D$2:$D$4000))=$A4)*(OFFSET(Data!$G$2,0,0,COUNTA(Data!$G$2:$G$4000))=C$3)),SUMPRODUCT((OFFSET(Data!$D$2,0,0,COUNTA(Data!$D$2:$D$4000))=$A4)*(OFFSET(Data!$E$2,0,0,COUNTA(Data!$E$2:$E$4000))=$B$1)*(OFFSET(Data!$G$2,0,0,COUNTA(Data!$G$2:$G$4000))=C$3)))

the offset if to account for expanding rows within the data table.

b2 has a data validation so when is select a specific item from the list it updates the formulas with the desired results, what i wanted was a wild card entry in the data validation to show all values from my data table.



Hope this is of use, Rob.

 

[SkipVought]

Your posted code had NO OFFSET!

1. Use the SECOND argument to change the offset and

2. Use the FOURTH argument to change the number or rows within that subset of data.

Usually you'll use the MATCH funciton to get the offset and the COUNTIF fucntion to get the rows.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

please post a sample of data and the CRITERIA values represented by your formula INCLUDING the value you expect the formula to return.

I think that you're making this FAR TOO COMPLICATED.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[robcarr]

i cant post sample data - not allowed client security.

(OFFSET(Data!$D$2,0,0,COUNTA(Data!$D$2:$D$4000))=$A4)

this part finds the correct number of rows - well it always has when i use it and gives me the desired rows.

And it works and does exactly what I wanted it to do, I was just hoping to have a show all values open within the data validation to stop me having to use an if statement with the sumproduct.
my data validation uses a range of values I put a "*" as the top value and was wanting this to represent all values within the range, the first post formula didnt work with a star, so I removed the star and left the data validation cell blank and now the second code/formula works and shows me all items.


this formula you provided didnt work

=SUMPRODUCT((Data!$D$2:$D$4000=$A5)*(Data!$E$2*(Data!$G$2:$G$4000))=C$4))


i need to alter it to show

=SUMPRODUCT((Data!$D$2:$D$4000=$A5)*(Data!$E$2:$E$4000)=$B$2)*(Data!$G$2:$G$4000))=C$4))

and then it shows up a #value!, and I have always presumed this because of the range lengths(whenever i change your formula to the exact length of the data it works), my data currently only goes down to 338 rows, i added the offset part of my formula to account for ever expanding range.





Hope this is of use, Rob.

 

[SkipVought]

cant post sample data

Your loss! Client security???

Example of SAMPLE DATA...
[tt]
Name Amount PostDate
ClientA 1234 4/1/2010
ClientA 5678 4/5/2010
ClientB 1234 4/1/2010
ClientC 5678 4/5/2010
[/tt]

and then it shows up a #value!, and I have always presumed this because of the range lengths(whenever i change your formula to the exact length of the data it works), my data currently only goes down to 338 rows, i added the offset part of my formula to account for ever expanding range.

"the range lengthens." Then use DYNAMIC Named Ranges faq68-1331. Also using the Data > Create - Create List works well to dynamically chnage the named range as yo add rows. DELETE ALL THE ROWS CONTANING UNUSED DATA WITH NAs IN THEM!!!!!!! This is the height of worksheet design laziness!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[robcarr]

I am glad to see you have plenty of time on your hands to query everything I mention.

not sure what you are going about here
DELETE ALL THE ROWS CONTANING UNUSED DATA WITH NAs IN THEM!!!!!!! This is the height of worksheet design laziness!

I dont have any cells with Nas in them and it still shows #value!, the spreadhseet is as required by the client, I have to work with what i have got.

my solution works and the client is satisfied, for my own learning I wanted to see if you can get a data validation with a show all option, you see to keep ignoring this request and only pick holes in other parts.

You mention about dynamic named ranges, isnt my formula doing that already!!!!, just becuase i dont use a named range within the formula and actually use the formula that a dynamic named range uses in my formula doesn't mean it is a bad thing, i prefer to see the formula instead of going into define names to see what the named range is using.



Hope this is of use, Rob.

 

[SkipVought]

If you will not provide a posted example that is representative of your data and the values that you are using in your criteria, you may continue to languish.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[robcarr]

thanks again for a post that doesnt help me.

Hope this is of use, Rob.