SOLVED

Copy Worksheet

%3CLINGO-SUB%20id%3D%22lingo-sub-1299217%22%20slang%3D%22en-US%22%3ECopy%20Worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1299217%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20workbook%20with%20a%20few%20of%20worksheets.%26nbsp%3B%20The%20%22primary%22%20worksheet%2C%20named%20WS-A%2C%20gets%20it's%20data%20from%26nbsp%3B%20MS%20Forms%20attached%20via%20power%20automate.%26nbsp%3B%20%26nbsp%3BI%20need%20another%20worksheet%2C%20WS-B%2C%20to%20pull%20all%20of%20the%20data%20from%20WS-A%20but%20I%20also%20need%20WS-B%20to%20have%20additional%20columns%20for%20filtering%2C%20etc.%26nbsp%3B%20What%20is%20the%20best%20way%20to%20make%20sure%20WS-B%20is%20always%20up%20to%20date%20with%20WS-A%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1299217%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20Desktop%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1299339%22%20slang%3D%22en-US%22%3ERe%3A%20Copy%20Worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1299339%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239483%22%20target%3D%22_blank%22%3E%40Rob%20Nunley%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20could%20link%20the%20cells%20in%20WS-B%20to%20WS-A.%20You%20can%20even%20account%20for%20having%20the%20formula%20in%20place%20for%20more%20rows%20to%20be%20prepared%20for%20more%20data%20in%20the%20future%20(instead%20of%20returning%20a%200%2C%20it%20would%20look%20empty)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20you%20have%20an%20estimated%20volume%20you%20expect%20for%20the%20data%3F%26nbsp%3B%3C%2FP%3E%3CP%3EOnce%20you%20establish%20your%20link%2C%20you%20can%20add%20columns%20any%20place%20you%20want%20for%20the%20extra%20filters%20you%20want%20to%20include.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample%20formula%20on%20Worksheet%20WS-B%20cell%20A2%3A%3C%2FP%3E%3CP%3E%3DIF(ISBLANK('WS-A'!A2)%2C%22%22%2C(('WS-A'!A2)))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1312362%22%20slang%3D%22en-US%22%3ERe%3A%20Copy%20Worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1312362%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239483%22%20target%3D%22_blank%22%3E%40Rob%20Nunley%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20both%20cases%20if%20Excel%20file%20is%20linked%20to%20Microsoft%20Forms%20directly%20or%20updated%20by%20Microsoft%20Flow%20you%20may%20add%20additional%20columns%20to%20main%20table%2C%20do%20some%20calculations%20in%20it%2C%20sort%20the%20table%20etc.%20Flow%20and%20Forms%20don't%20care%2C%20they%20only%20add%20new%20record%20to%20the%20table%20filling%20with%20data%20main%20columns%20and%20ignore%20the%20rest.%20Assuming%20Excel%20file%20is%20on%20Sharepoint%20site%20or%20on%20OneDrive.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1313655%22%20slang%3D%22en-US%22%3ERe%3A%20Copy%20Worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1313655%22%20slang%3D%22en-US%22%3E%3CP%3Ethanks%20for%20the%20suggestions.%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F616575%22%20target%3D%22_blank%22%3E%40HCole718%3C%2FA%3E.%26nbsp%3B%20I%20ended%20up%20using%20Power%20Query%20to%20copy%20the%20data%20and%20keep%20it%20updated%20on%20the%20addition%20worksheets.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1313694%22%20slang%3D%22en-US%22%3ERe%3A%20Copy%20Worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1313694%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239483%22%20target%3D%22_blank%22%3E%40Rob%20Nunley%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20only%20point%20with%20Power%20Query%20is%20not%20to%20forget%20to%20refresh%20it%20in%20time%2C%20it's%20not%20synced%20with%20Form%20update.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

I have a workbook with a few of worksheets.  The "primary" worksheet, named WS-A, gets it's data from  MS Forms attached via power automate.   I need another worksheet, WS-B, to pull all of the data from WS-A but I also need WS-B to have additional columns for filtering, etc.  What is the best way to make sure WS-B is always up to date with WS-A?

 

Thanks!

4 Replies
Highlighted

Hi @Rob Nunley 

 

You could link the cells in WS-B to WS-A. You can even account for having the formula in place for more rows to be prepared for more data in the future (instead of returning a 0, it would look empty)

 

Do you have an estimated volume you expect for the data? 

Once you establish your link, you can add columns any place you want for the extra filters you want to include.

 

Example formula on Worksheet WS-B cell A2:

=IF(ISBLANK('WS-A'!A2),"",(('WS-A'!A2)))

Highlighted

@Rob Nunley 

In both cases if Excel file is linked to Microsoft Forms directly or updated by Microsoft Flow you may add additional columns to main table, do some calculations in it, sort the table etc. Flow and Forms don't care, they only add new record to the table filling with data main columns and ignore the rest. Assuming Excel file is on Sharepoint site or on OneDrive.

Highlighted
Best Response confirmed by Rob Nunley (Occasional Contributor)
Solution

thanks for the suggestions. @Sergei Baklan @HCole718.  I ended up using Power Query to copy the data and keep it updated on the addition worksheets.

Highlighted

@Rob Nunley 

The only point with Power Query is not to forget to refresh it in time, it's not synced with Form update.