Apr 10 2020 04:29 PM
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!
Apr 10 2020 08:40 PM
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)))
Apr 16 2020 03:54 AM
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.
Apr 16 2020 10:41 AM
Solutionthanks for the suggestions. @Sergei Baklan @HCole718. I ended up using Power Query to copy the data and keep it updated on the addition worksheets.
Apr 16 2020 10:54 AM
The only point with Power Query is not to forget to refresh it in time, it's not synced with Form update.
Apr 16 2020 10:41 AM
Solutionthanks for the suggestions. @Sergei Baklan @HCole718. I ended up using Power Query to copy the data and keep it updated on the addition worksheets.