SOLVED

Tricky? Excel365 problem

Copper Contributor

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...

 

 

6 Replies
Hello,
There are some cube formullas to get values from a pivot table, However if you are using Table slicers there is no way to get the slicer value or assign cell for binding its value.
Right...never understood the why's and wherefore's behind that one, but I'm trying to use something to workaround (wondering about using OFFSET to get the values)...

@CharlieOt 

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.

Hi Sergei,
Thanks for the response.
Sync isn't an issue, the query is only run for the initial setup. Running an additional query would eliminate the dynamic results for the user...Unfortunately, I can't give a full sample file, but the .png should provide the scenario...
Then the user places a "Y" in the assigned column, it should dynamically update the count for the product. If I could find the first visible row in the table, I could use offset with the visible row reference to generate either a subtotal or aggregate function set of values on the visible rows and combine it with sumproduct to create what I want, but getting the first visible row is a real sticking point....
best response confirmed by CharlieOt (Copper Contributor)
Solution
Good morning Sergei...
That was the path I was heading down...I had reviewed Leila Gharani's example...but I found another example that finished it for me....I needed Offset using table nomenclature and found a reference using it...that makes things work.
The only thing I've still got outstanding is the starting cell for Offset...I can make it work using an explicit cell reference, but trying to make it dynamic and portable is failing...Interestingly if I try to find the first cell using the Address function in coordination with the Row and Column function, "Row" returns a 1x1 array for the row number (I used Row(TableName[[#Headers],[Column Name])+1 to get the first data row in the table) which blows up the Offset function. For now I'll just leave the manual cell reference until I can figure out how to get the 1x1 array to a discrete value for the Address function. That would be an interesting question for Messrs. Girvin and Jelen! As an aside, I've used Mike Girvin's YouTube channel to solve tons of problems in the past and they're always helpful...it was just the extra piece for table nomenclature that was missing for me.
I was also overthinking the problem...I kept getting stuck on needing the first visible row of one column for part of the sumproduct criteria, where changing to a different criteria column was all I needed)...
All in all, it produces a very elegant (albeit somewhat complex) solution. It was my first use of offset, but it's definitely a function to hang onto when you need it....
MS...if anyone is reading; it would be great if you could use regular table slicer values in formulas just like you can with pivot tables...that would have reduced my task to a 5-minute exercise rather than a 2 day trip through Function-Land!
1 best response

Accepted Solutions
best response confirmed by CharlieOt (Copper Contributor)