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