Forum Discussion
Forms + Excel + Rolling 12 + Live updates
After our company reorganization, we are having to build tools we had before that we no longer have access to.
Once a month, all departments are required to submit data about their performance. This data is then used in month to date (MTD), year to date (YTD) and Rolling 12 month (R12) reports for each department, product group and as a company overall.
We would like to create a form for data entry from the departments that populates a live spreadsheet. We then need that data to populate a KPI type tab where we select the location and time period to display without having to copy data from the Form data collection spreadsheet to paste in the sheet where the calculations are performed.
I have spent almost two weeks trying to pull this all together, searching discussion boards and watching many YT videos but have not been able to figure out what even would be the best product to do this in, let alone how to do it.
Any feedback is appreciated.
2 Replies
fantom71 in my view the best way to do this is with Power Apps for the front end screens with either SharePoint or Dataverse as the datasource. You can perform calculations in Power Apps and have tabs for MTD, YTD and Rolling etc, you can do searches & filtering etc. As peiyezhu indicated, you might also need some flows in Power Automate to send the data to Excel or Power BI although if you are using SharePoint then you could use Patch commands to save data directly into the relevant SharePoint lists.
Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP (and classic 1967 Morris Traveller driver)We then need that data to populate a KPI type tab where we select the location and time period to display without having to copy data from the Form data collection spreadsheet to paste in the sheet where the calculations are performed
As far as I know, Ms Forms do not provide custom report function.
Of course,you can use Power Automate or build your own online Form with PHP etc. to achieve your task automately.
