SOLVED

Self populating data sheets

%3CLINGO-SUB%20id%3D%22lingo-sub-2128148%22%20slang%3D%22en-US%22%3ESelf%20populating%20data%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2128148%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20wondered%20if%20it%20is%20possible%20to%20populate%20a%20sheet%20dependent%20on%20cell%20values%20in%20another%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20the%20sample%20sheet%20I%20have%20designed%20is%20self%20explanatory.%26nbsp%3B%20The%20answer%20if%20there%20is%20one%20would%20be%20an%20enormous%20help%20in%20a%20bigger%20project%20I%20am%20working%20on.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIdeally%20I%20would%20like%20to%20use%20formula%20but%20it%20may%20be%20that%20VBA%20is%20the%20only%20way.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance%20for%20any%20help%20you%20can%20offer.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPaul%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2128148%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2128202%22%20slang%3D%22en-US%22%3ERe%3A%20Self%20populating%20data%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2128202%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F963230%22%20target%3D%22_blank%22%3E%40Paul_wfp%3C%2FA%3E%26nbsp%3BAre%20you%20on%20MS365%20with%20access%20to%20the%20FILTER%20function%3F%20If%20so%2C%20please%20see%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2128302%22%20slang%3D%22en-US%22%3ERe%3A%20Self%20populating%20data%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2128302%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3ETotally%20superb%20thank%20you%20so%20much.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi,

 

I wondered if it is possible to populate a sheet dependent on cell values in another sheet.

 

I hope the sample sheet I have designed is self explanatory.  The answer if there is one would be an enormous help in a bigger project I am working on.

 

Ideally I would like to use formula but it may be that VBA is the only way.

 

Thank you in advance for any help you can offer.

 

Paul

6 Replies
best response confirmed by Paul_wfp (Occasional Contributor)
Solution

@Paul_wfp Are you on MS365 with access to the FILTER function? If so, please see attached.

@Riny_van_EekelenTotally superb thank you so much.

@Paul_wfp Good to hear it worked!

@Riny_van_EekelenRiny I have applied it to my actual data and it is superb , such a powerful tool.  I have also enjoyed trying to understand the formula. 

 

Many thanks again.

 

Paul

@Paul_wfp You are welcome!! It works even better if you apply the FILTER function to structured tables. Then you don't have to worry about redefining the range to filter from every time. If you transform the entire data range in the STOCK sheet to a structured table (Ctrl-T), the formula would look like this:

 

=FILTER(FILTER(Table1,Table1[Order required]="Yes",""),{1,1,0,0,0,0,0,1,1,1,0,0})

 

Add rows to the table and the FILTER will still work as "Table1" and the column "[Order required]" will automatically expand.

@Riny_van_EekelenAgain, fabulous advice and help totally understand.

 

Have as nice a weekend as possible in Covid times.

 

Paul