I have a table used for data input. The possible values look up the values from other tables (Group, User, Permission). The Group table and Name table are dynamic and created at the time. The permission table is static
I need to be able to check that the data entry table when finished includes all the possible groups at least once, all the names at least once and each group has all the possible permissions at least once or a user that has the permission ALL.
see attached.
What would be the best approach for this and how?
Hi Alison,
There isn't a workbook attached.
Mynda
oops! here it is
Hi Alison,
This isn't really a data validation issue.
I'd use Power query to create the 'desired table' and then compare that to the finished table. e.g. for Group/User combinations, check that all users are present in a group in the FinalTable using a Left Anti merge, where the Users table is first and the Final Table is second. See example attached - UsersWithoutGroups.
You can use a similar technique for the groups that don't have all permissions covered. This is a little more complicated, but I'll leave that for you to work out. It will be a good challenge for you.
Mynda
Thanks. As this is a user form Is there a way of putting a button on the sheet to refresh this the results table of the power query?
I imagine so. You'd have to write some VBA code to refresh the query via a button.
Could you give me an idea how that would look please. I can write VBA in Access. Excel appears to be different ….
No, sorry. I don't cover VBA.
