Hello. I have a excel file on onedrive business. In this file I have 3 tables (in 3 worksheets). In the first 2 tables I automatically collect data from external questionnaires. These two tables are "linked" each other with one field (column) called "IDunique" which is exclusive in both tables, not repeated. In the first table (Table1) I have several added columns with formulas. Also in Table2 I have several calculated columns. Besides in Table1 I also get data from 2nd table Table2 (all the columns of 2nd table excluded those equals) using Index and Match excel functions to get data where I used "IDunique" to identify right row in the 2nd table. In this way in the Table1 finally I have both data collected from questionnaire in first Table1, data collected from questionnaire in Table2 and several calculated columns.
In Table3 I copy (using a PowerAutomate flow) all data from Table1 (only data, includes data from calculated columns). I need Table3 because it is datasource for a PowerApps canvas, which does not accept excel tables with formulas.
Now, after several tests I cannot rid of problems to manage excel tables. Sometimes (not clear in what conditions) when a new row of data from questionnaire inquiry is added then auto-filling formulas does not work and formulas in new row are missing. Sometimes not, but it's too tricky.
So, I wonder if there is a way to use Power Automate to add data calculated in those columns where actually I am using formulas.
Each time excel file on onedrive is modified the flow runs. The flow should filter those records (rows) in Table1 and Table2 where columns which need calculation are empty. For such rows a calculation (which uses data from other columns) for each row should be acted.
Is it possible? consider that, because of company policy, I cannot get advantage in using Script (disabled).
Thanks 🙂
