Forum Discussion

Frezen385's avatar
Frezen385
Copper Contributor
Aug 30, 2022

Filter rows from 4 sheets based on column value

Hi there,

 

I'm having a bit of trouble navigating my way around some formulas trying to get this to work and after a few hours have decided to come here seeking help.

I have an excel file with weekly data that comes in about customers, my intent is to create a sheet which will act as a 'monthly overview' of the 4 weeks of the month and bring information together from the 4 'weekly' sheets.

I created a simple example file to show what i'm hoping to achieve.

So here I have information on customers, each row being a different customer. This comes in every week and I'm hoping to look over the 4 dated sheets down bottom and pull together a 'report' for the end of month.

Of course it's most important to me to highlight the customers who are unsatisfied so we can rectify this, so in the 'Report' Sheet to filter rows where 'Customer Satisfaction' Column is 'Sad'.

 

In the 'Report' sheet I've got a named range. The grey area is the 4 weeks which are also the names of the tabs I want to search, grouped into a named range as I thought this would lead to easier referencing in the future when creating further reports it would just be a matter of updating the tab names in the grey area.

Basically, I want to list rows under 'Unhappy Customers' where the 'Customer Satisfaction' was 'Sad' across all 4 of the tabs, as a sort of 'feed' to see how many customers were 'Sad' across the whole month and view the values of their other columns such as 'Revenue' and 'Services'.

Is this at all possible? It feels so close to being doable but I can't get anything to work, the best I've got so far is I can get it to FILTER for a single sheet that I reference, but when I try using the named range INDIRECT reference, it's not working.

I'm trying to avoid VBA at all costs.

Any help much appreciated.

On latest MacOS, if that helps at all.

excel
Excel on Mac
Formulas and Functions
  • dscheikey's avatar
    dscheikey
    Bronze Contributor
    Sep 03, 2022

    Frezen385 

    Hi, I just wanted to know if my solution didn't work for you either? You only responded to Peter's answer.

    • Frezen385's avatar
      Frezen385
      Copper Contributor
      Aug 31, 2022

      PeterBartholomew1 

      Peter, Thank you for your response.

      I have 365 I believe but my Excel won't recognise VSTACK function! I'm on 16.64 macOS version of Excel if that gives any context at all. So that's made the above solution not work for me, even though it looks beautifully straightforward!

      I think I'm just going to change what I'm doing, having no luck finding a reliable solution. Even if I could find a way to pull from each sheet separately, by referencing a cell which contains the individual sheet names, but I think my lack of skill was preventing me from doing that, too.

      Anyways thank you for your suggestion, I'll return to this perhaps once I see VSTACK in my Excel!

  • dscheikey's avatar
    dscheikey
    Bronze Contributor
    Aug 30, 2022

    HiFrezen385, 

    here is my solution:

    =LET(
ma,MAX(BYROW(B2:B5,LAMBDA(in,COUNTA(INDIRECT("'"&in&"'!A:A"))))),
c_1,CHOOSE({1,2,3,4},INDEX(INDIRECT("'"&B2&"'!A2:A"&ma&""),0),INDEX(INDIRECT("'"&B3&"'!A2:A"&ma&""),0),INDEX(INDIRECT("'"&B4&"'!A2:A"&ma&""),0),INDEX(INDIRECT("'"&B5&"'!A2:A"&ma&""),0)),
c_2,INDEX(c_1,MOD(SEQUENCE((ma-1)*4,1,0),ma-1)+1,INT(SEQUENCE((ma-1)*4,1,0)/(ma-1))+1),
s_1,CHOOSE({1,2,3,4},INDEX(INDIRECT("'"&B2&"'!B2:B"&ma&""),0),INDEX(INDIRECT("'"&B3&"'!B2:B"&ma&""),0),INDEX(INDIRECT("'"&B4&"'!B2:B"&ma&""),0),INDEX(INDIRECT("'"&B5&"'!B2:B"&ma&""),0)),
s_2,INDEX(s_1,MOD(SEQUENCE((ma-1)*4,1,0),ma-1)+1,INT(SEQUENCE((ma-1)*4,1,0)/(ma-1))+1),
FILTER(c_2,s_2="Sad",""))

Resources