Filter rows from 4 sheets based on column value

[Frezen385]

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.

[#2]

Hi@Frezen385, 

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",""))

[#3]

@Frezen385 

If you have the most up-to-date version of 365 and are familiar with 3D named ranges, the following should work

= LET(stacked, VSTACK(weeklyData),
  FILTER(stacked, CHOOSECOLS(stacked,2)="sad"))

[#4]

@Peter Bartholomew 

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!

[#5]

@Frezen385 

So far VSTACK is only for Office Insiders (Beta and Preview channels).

[#6]

@Frezen385 

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