Rule for Dropdown > If the selected user already posted the value to database, then do not show in dropdown

[The Godfather]

Hi,

I am a total beginner in VBA. I have an excel with two sheets as "form" and "database" (actually more but others are not relevant).

In the Form;
1) Users type their username, password, and select the week.
2) In the orange part, users type other stuff (to all 3 rows).
3) When submitted, all are transferred into database.


In the Database (the whole sheet is actually a table); usernames (Form sheet Cell "F5") are stored in C Column, week (Form sheet Cell "F7") is stored in D Column, and the other orange stuff is stored in other columns.


I need the dropdown list (Form sheet cell "F7") only to show the weeks which are not previously posted into database by the same user (which is typed into Form sheet cell "F5"), to prevent users posting other data using the same week. (For instance, user2 should not post anything again for Week 12, since he already posted as seen in database screenshot).

Could you please help me on how to do it? (Preferrably in VBA instead of data validation).

Thanks for reading.

 

[Skybluekid]

Try this, bit messy but the Filter Function does not work in Data Validation

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Record No Sub Record No		Week	Other Data	Other Data	Other Data	Other Data
2	1	user1	Week 12	irrelevant	irrelevant	irrelevant	irrelevant			User			Week 1		Week 1
3	1	user2	Week 12	irrelevant	irrelevant	irrelevant	irrelevant			user1			Week 2		Week 2
4	1	user3	Week 12	irrelevant	irrelevant	irrelevant	irrelevant			Week No.			Week 3		Week 3
5	2	user2	Week 12	irrelevant	irrelevant	irrelevant	irrelevant						Week 4		Week 4
6	2	user2	Week 12	irrelevant	irrelevant	irrelevant	irrelevant						Week 5		Week 5
7	2	user2	Week 12	irrelevant	irrelevant	irrelevant	irrelevant						Week 6		Week 6
8	3	user1	Week 13	irrelevant	irrelevant	irrelevant	irrelevant						Week 7		Week 7
9	3	user1	Week 13	irrelevant	irrelevant	irrelevant	irrelevant						Week 8		Week 8
10	3	user1	Week 13	irrelevant	irrelevant	irrelevant	irrelevant						Week 9		Week 9
11													Week 10		Week 10
12													Week 11		Week 11
13													Week 12	user1	Week 14
14													Week 13	user1	Week 15
15													Week 14		Week 16
16													Week 15		Week 17
17													Week 16		Week 18
18													Week 17		Week 19
19													Week 18		Week 20
20													Week 19		Week 21
21													Week 20		Week 22
22													Week 21		Week 23
23													Week 22		Week 24
24													Week 23		Week 25
25													Week 24		Week 26
26													Week 25		Week 27
27													Week 26		Week 28
28													Week 27		Week 29
29													Week 28		Week 30
30													Week 29		Week 31
31													Week 30		Week 32
32													Week 31		Week 33
33													Week 32		Week 34
34													Week 33		Week 35
35													Week 34		Week 36
36													Week 35		Week 37
37													Week 36		Week 38
38													Week 37		Week 39
39													Week 38		Week 40
40													Week 39		Week 41
41													Week 40		Week 42
42													Week 41		Week 43
43													Week 42		Week 44
44													Week 43		Week 45
45													Week 44		Week 46
46													Week 45		Week 47
47													Week 46		Week 48
48													Week 47		Week 49
49													Week 48		Week 50
50													Week 49		Week 51
51													Week 50		Week 52
52													Week 51
53													Week 52
Sheet1

Cell Formulas
Range	Formula
O2:O51	O2	=FILTER($M$2:$M$53,$N$2:$N$53<>$J$3,"")
N2:N53	N2	=IFERROR(INDEX($B$2:$B$10,MATCH($J$3&M2,$B$2:$B$10&$C$2:$C$10,0)),"")
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
J5	List	=$O$2#

 

[awoohaw]

Can you create a filtered list and name, and then reference that filtered list?
Like this:
I had an error, reposting

 

[awoohaw]

i had an error sorry

 

[awoohaw]

Okay, here is a mini workbook where a subset of a list is excluded from the data validation drop down.
This will require re-sizing of the data validation list from time to time. Additionally, this does not include the process to add the selected name and week to the database list It just builds the data validation list.

Book2
	A	B	C	D	E	F	G	H
1	Week Selection	User		FilteredWeekSelection		UserAlready
2	Week01	John		Week01		Week04
3	Week02			Week02		Week05	The Database Data
4	Week03	User Chose:		Week03		Week08	UserName	WeekChosen
5	Week04	Week03		Week06		Week18	Jane	Week01
6	Week05			Week07		Week21	John	Week04
7	Week06			Week09			John	Week05
8	Week07			Week10			Sally	Week06
9	Week08			Week11			John	Week08
10	Week09			Week12			Sally	Week08
11	Week10			Week13			Sally	Week09
12	Week11			Week14			Jane	Week13
13	Week12			Week15			Jane	Week14
14	Week13			Week16			Sally	Week14
15	Week14			Week17			Jane	Week17
16	Week15			Week19			Sally	Week17
17	Week16			Week20			Jane	Week18
18	Week17			Week22			John	Week18
19	Week18						John	Week21
20	Week19
21	Week20
22	Week21
23	Week22
24
Sheet1

Cell Formulas
Range	Formula
D2:D18	D2	=FILTER($A$2:$A$23,MMULT(--(TRANSPOSE(F2#)=A2:A23),SEQUENCE(ROWS(F2#),1,1,0))=0)
F2:F6	F2	=SORT(FILTER($H$5:$H$19,$G$5:$G$19=B2),1,1)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
B5	List	=FilteredWeekSelection

 

[The Godfather]

Thank you Skybluekid and awoohaw. I tried to implement both solutions, I believe awoohaw's solution also fits more to my needs.
However when I type =filter, or =sort Excel responds "the function is not valid".

Is it possible to solve it with some kind of index.match?
As in; there is already a weeklist defined somewere in my worksheet (as both of you suggested in your solutions); from Week 1 to Week 12.
When user opens dropdown, the dropdown will look at tle weeklist first, then look database and if the username & week matches in data, only show remaining weeks for that user.

Thanks.

 

[awoohaw]

are you doing this on a 365 or 2016 machine? it those functions will not work on 2016.
it can prob be done another way. but will take time to build that formula

 

[The Godfather]

are you doing this on a 365 or 2016 machine? it those functions will not work on 2016.
it can prob be done another way. but will take time to build that formula

Hi awoohaw, me and everyone in my organization uses MS Office Prof. Plus 2016. Then it is clear why we cannot use these functions. Thanks.

I will try to make another veryhidden sheet in my excel, where it will match username&week in data, and then a dynamic list showing the remaining weeks for that user, and then in the "Forms" sheet the user will be able to select only those remaining weeks in dropdown data validation.

 

[awoohaw]

OK. I see that you have 365 listed as one of your versions. That is why I asked. But, you gotta play with the toys you have!