SOLVED

CELLS DISPLAY

%3CLINGO-SUB%20id%3D%22lingo-sub-2595064%22%20slang%3D%22en-US%22%3ECELLS%20DISPLAY%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2595064%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20community%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20table%20with%20rows%20WEEK%2C%20DATE%2C%20MATERIAL%2C%20PRICE%20and%20others%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOnly%20the%20first%20cell%20of%20each%20week%20is%20written%2C%20all%20others%20in%20the%20same%20column%20are%20blank%20as%20you%20can%20see%20in%20the%20photo.%3C%2FP%3E%3CP%3EThe%20problem%20is%20that%20when%20I%20put%20on%20the%20filter%20WEEK%20e.g.%20week%2030%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20be%20shown%20all%20the%20rows%20included%20in%20the%20same%20week.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20%2C%20the%20filter%20will%20be%20week%2030%20and%20will%20be%20displayed%20all%20the%20rows%20between%26nbsp%3B%20WEEK%2030%20AND%20WEEK29%3C%2FP%3E%3CP%3EBelow%20week%2030%20and%20above%20week%2029.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20hint%20please%3F%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-right%22%20image-alt%3D%22Screenshot%202021-07-29%20165414.jpg%22%20style%3D%22width%3A%2095px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F299198i53EA3A4F89840E05%2Fimage-size%2Fsmall%3Fv%3Dv2%26amp%3Bpx%3D200%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-07-29%20165414.jpg%22%20alt%3D%22Screenshot%202021-07-29%20165414.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2595064%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2595888%22%20slang%3D%22en-US%22%3ERe%3A%20CELLS%20DISPLAY%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2595888%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%20Of%20course%20you%20are%20right%2C%20thank%20you%20for%20the%20prompt%20answer!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2595084%22%20slang%3D%22en-US%22%3ERe%3A%20CELLS%20DISPLAY%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2595084%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1115654%22%20target%3D%22_blank%22%3E%40G_C-GR%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20wrote%3A%20%26nbsp%3B%3CEM%3EI%20have%20a%20table%20with%20rows%20WEEK%2C%20DATE%2C%20MATERIAL%2C%20PRICE%20and%20others%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3EFor%20the%20sake%20of%20clarity%2C%20those%20are%20%3CU%3E%3CSTRONG%3Ecolumns%3C%2FSTRONG%3E%3C%2FU%3E%2C%20not%20rows.%20Using%20the%20precise%20language%20is%20helpful%2C%20even%20if%20in%20this%20case%20not%20a%20major%20issue.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3EOnly%20the%20first%20cell%20of%20each%20week%20is%20written%2C%20all%20others%20in%20the%20same%20column%20are%20blank%20as%20you%20can%20see%20in%20the%20photo.%3C%2FEM%3E%3C%2FP%3E%3CP%3E%3CEM%3EThe%20problem%20is%20that%20when%20I%20put%20on%20the%20filter%20WEEK%20e.g.%20week%2030%3C%2FEM%3E%3C%2FP%3E%3CP%3E%3CEM%3EI%20want%20to%20be%20shown%20all%20the%20rows%20included%20in%20the%20same%20week.%3C%2FEM%3E%3C%2FP%3E%3CP%3E%3CEM%3EThen%20%2C%20the%20filter%20will%20be%20week%2030%20and%20will%20be%20displayed%20all%20the%20rows%20between%26nbsp%3B%20WEEK%2030%20AND%20WEEK29%3C%2FEM%3E%3C%2FP%3E%3CP%3E%3CEM%3EBelow%20week%2030%20and%20above%20week%2029.%3C%2FEM%3E%3C%2FP%3E%3CP%3E%3CEM%3EAny%20hint%20please%3F%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3EIn%20a%20table%2C%20you%20generally%20don't%20want%20to%20leave%20blank%20cells%20anyway%2C%20but%20especially%20not%20when%20it's%20a%20column%20on%20which%20you're%20going%20to%20be%20sorting%20or%20filtering.%20When%20you%20do%20that%2C%20you're%20denying%20Excel%20the%20ability%20to%20do%20the%20%22heavy%20lifting%22%20for%20you%2C%20instead%20opting%20to%20do%20the%20formatting%20yourself%20%3CEM%3E%3CU%3Emanually%3C%2FU%3E%3C%2FEM%3E.%3C%2FP%3E%3CUL%3E%3CLI%3EIn%20this%20case%2C%20I'd%20recommend%20setting%20it%20up%20so%20the%20%22Week%22%20column%20populates%20itself%20by%20means%20of%20a%20formula%20%3DWEEKNUM(C2)%20copied%20down%20to%20all%20the%20rows.%20Then%20filter%20on%20WEEKNUM.%26nbsp%3B%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello community,

 

I have a table with rows WEEK, DATE, MATERIAL, PRICE and others

 

Only the first cell of each week is written, all others in the same column are blank as you can see in the photo.

The problem is that when I put on the filter WEEK e.g. week 30

 

I want to be shown all the rows included in the same week.

 

Then , the filter will be week 30 and will be displayed all the rows between  WEEK 30 AND WEEK29

Below week 30 and above week 29.

 

Any hint please?

Screenshot 2021-07-29 165414.jpg

2 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

@G_C-GR 

 

You wrote:  I have a table with rows WEEK, DATE, MATERIAL, PRICE and others

 

For the sake of clarity, those are columns, not rows. Using the precise language is helpful, even if in this case not a major issue. 

 

Only the first cell of each week is written, all others in the same column are blank as you can see in the photo.

The problem is that when I put on the filter WEEK e.g. week 30

I want to be shown all the rows included in the same week.

Then , the filter will be week 30 and will be displayed all the rows between  WEEK 30 AND WEEK29

Below week 30 and above week 29.

Any hint please?

 

In a table, you generally don't want to leave blank cells anyway, but especially not when it's a column on which you're going to be sorting or filtering. When you do that, you're denying Excel the ability to do the "heavy lifting" for you, instead opting to do the formatting yourself manually.

  • In this case, I'd recommend setting it up so the "Week" column populates itself by means of a formula =WEEKNUM(C2) copied down to all the rows. Then filter on WEEKNUM. 

 

@mathetes  Of course you are right, thank you for the prompt answer!!