Mar 17 2021 06:48 AM
Mar 17 2021 06:48 AM
I have a problem that is causing me some distress...
I have a table (Email Address, Domain, Product, Assigned by <person>?). The first 3 columns are extracts from existing data. The fourth column is a data entry column for specific individuals to select which products they have assigned to give email addresses. To make data entry simpler, I want to use slicers to filter the data to selected domains and/or products for the data entry user.
I have a range using UNIQUE() to extract a list of products to enable the data entry user to see the list of products and assigned totals for the products. The user will see essentially a two-column table with Product and Count of product columns, where the count is products shown as assigned by the user only (assignments made by others are not included).
For the list, my goal is to have the assigned product count change dynamically (i.e. Setting an "Y" entry for "Bob" assigned? will update the count) for the user following the slicer values, which eliminates pivot tables without vba behind to autorefresh with each entry (kludgy and time-consuming in and of itself).
Any thoughts on how to accomplish this would be greatly appreciated...
Mar 17 2021 07:09 AM
Mar 17 2021 07:12 AM
Mar 17 2021 07:48 AM
If I understood correctly first 3 columns of the table are returned by Power Query and 4th one is added manually. If so they are not in sync, as soon as you refresh/update Power Query Y/N will be against another rows.
Assuming email addresses could be used as unique ID you may sync columns loading first 3 columns, add 4th ones, query resulting table again and merge with initial one. All shall be done within one query. With table for slicers could be done by one more query.
If above assumption is wrong desirably to have bit more details, better the sample file.
Mar 17 2021 09:35 AM
Mar 17 2021 10:03 AMSolution
Perhaps you may use this approach SUMIF Visible Cells - Excel Tips - MrExcel Publishing
Mar 18 2021 05:30 AM