Forum Discussion
Solved
Tricky? Excel365 problem
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...
Perhaps you may use this approach SUMIF Visible Cells - Excel Tips - MrExcel Publishing
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)...
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....Perhaps you may use this approach SUMIF Visible Cells - Excel Tips - MrExcel Publishing
- 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!