Data Validation List with sumif function

[omegaboost]

Dear All

Is it possible to make data validation in cell G1 with the data from Invoices number that still have not got the full payment? If from the table on the file (attached), the invoices number that still not have full payment are : 3333, 4444, 5555.
So the data validation list should be : 3333,4444, 5555.

Thank you

Omegaboost

[oeldere]

another approach.

with a pivot table.

See the attached file.

[omegaboost]

Dear oeldere,

Thank you for your reply but that is not what i want. I try to get the list from the table with that condition and populate the data to the drop down in data validation.

Thank you

Omegboost

[FlameRetired]

another approach.

with a pivot table.

See the attached file.

oeldere,

I'd love to see what you've done, but I find that when long file names like 464481d1465144521-data-validation-list-with-sumif-function-data-validation-with-sumif.xlsx are used Excel won't open them. I have no idea why.

Can you help us out?

Thanks,
Dave

[FlameRetired]

@ omegaboost

I am trying to follow the logic flow of what you want and cannot find the connection to the data supplied. Shouldn't invoice 2222 also be included?

Dear All
Is it possible to make data validation in cell G1 with the data from Invoices number that still have not got the full payment?
If from the table, the invoices number that still not have full payment are : 3333, 4444, 5555
so the data validation list should be : 3333,4444, 5555.

Row\Col	A	B	C	D	E
1	Receipt	Invoice	Item	Amount	Payment
2	1212			200	200
3		2222		500
4		3333		600
5		4444		1000
6		5555		800
7	1213	3333			300
8	1214	2222			500
9	1216	4444			600

[Tony Valko]

I am trying to follow the logic flow of what you want and cannot find the connection to the data supplied. Shouldn't invoice 2222 also be included?

Invoice 2222 is paid in full on row 8.

I can get the desired result but it takes the creation of 2 new lists!

[FlameRetired]

I'm guessing yes it should be included but I didn't use SUMIF to do it.

You'll need to generate a current list of Invoice numbers. I did it in column J with this formula array entered in J1 and filled down enough rows to make room for additional output.

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Then made this Dynamic Named Range in Name Manager. I called it TheDD.

Please Login or Register  to view this content.

Now define the drop down list with

Please Login or Register  to view this content.

in Data Validation

Row\Col	A	B	C	D	E	F	G	H	I	J
1	Receipt	Invoice	Item	Amount	Payment		3333			2222
2	1212			200	200					3333
3		2222		500						4444
4		3333		600						5555
5		4444		1000
6		5555		800
7	1213	3333			300
8	1214	2222			500
9	1216	4444			600

[FlameRetired]

Invoice 2222 is paid in full on row 8.

I can get the desired result but it takes the creation of 2 new lists!

Aha! Thank you. I missed that. Will need to re-work my formula.

[Tony Valko]

I hate this place. Why do I hangout here?

I tried posting a solution but the Securi firewall won't let me post it.

[FlameRetired]

Happened to me Friday, too. Then started getting 'Blank' screens here and other forums, too last night.

[Tony Valko]

Let me try it again.

Enter this array formula** in J2:

=IFERROR(INDEX(B:B,SMALL(IF(SUMIF(B$2:B$9,B$2:B$9,E$2:E$9) < SUMIF(B$2:B$9,B$2:B$9,D$2:D$9),ROW(B$2:B$9)),ROWS(J$2:J2))),"")

Copy down until you get blanks. I copied down to J10.

Enter this array formula** in K2:

=IFERROR(INDEX(J$2:J$10,MATCH(0,IF(J2:J10<>"",COUNTIF(K$1:K1,J$2:J$10)),0)),"")

Copy down until you get blanks. I copied down to K10.

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Then, as the source for your drop down list use this formula:

=OFFSET(K2,,,SUMPRODUCT(--(K2:K10<>"")))

[FlameRetired]

I'm missing a 'trick' here, Tony.

This

=IFERROR(INDEX(J$2:J$10,MATCH(0,IF(J2:J10<>"",COUNTIF(K$1:K1,J$2:J$10)),0)),"")

does the same as

=IFERROR(INDEX(J$2:J$10,MATCH(0,COUNTIF(K$1:K1,J$2:J$10),0)),"")

What am I missing?

Dave

[Tony Valko]

If K1 is an empty cell and there are blank cells in Jn:Jn then you'll get something like this in column K:

Data Range

	K
1
2	3333
3	4444
4	5555
5	0
6
7
8
9
10

However, I assume that K1 would be a header related to the data below.

[FlameRetired]

So you do.

Thank you.

[Tony Valko]

---------

[oeldere]

Answer to the question in #4

I'd love to see what you've done, but I find that when long file names like 464481d1465144521-data-validation-list-with-sumif-function-data-validation-with-sumif.xlsx are used Excel won't open them. I have no idea why.

Probably it depends on the version of excel 2007 - 2013.

Please try this (renamed) file instead, and please let me know if you are able to read it.

[Tony Valko]

oeldere,

I'd love to see what you've done, but I find that when long file names like 464481d1465144521-data-validation-list-with-sumif-function-data-validation-with-sumif.xlsx are used Excel won't open them. I have no idea why.

Can't you just rename the file with a much shorter name?

[FlameRetired]

This is when I try to upload. Excel won't accept the file. If there is a way in between here and my computer to rename I am not aware of it.

[FlameRetired]

Answer to the question in #4




Probably it depends on the version of excel 2007 - 2013.

Please try this (renamed) file instead, and please let me know if you are able to read it.

Yup. I can upload that and read it. Much thanks.

Dave

[FlameRetired]

another approach.

with a pivot table.

See the attached file.

That's an interesting idea oeldere. I would have never thought of that.

[FlameRetired]

Can't you just rename the file with a much shorter name?

OK I see what you've saying, now. I've never followed the save option before.

[Tony Valko]

I use Win7.

When I click the link to download a file I get a toolbar that pops up that has a Save As option. Then I can rename the file and it gets saved under that new name.

A file like:

464481d1465144521-data-validation-list-with-sumif-function-data-validation-with-sumif.xlsx

I would rename to:

sumif.xlsx

[FlameRetired]

Yes. I "stumbled" upon that after my 1st response. It never occurred to me to go that route. Thanks, again.

[Tony Valko]

------------

[omegaboost]

Dear All,

Thank you for your help, especially for FlameRetired. Your solution is exact for waht i need.

Best Regards

Omegaboost