Unanswered

add calculated columns to excel file on onedrive business

(1) Share

Report

Posted on by Brucla1968

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 🙂

Categories:

General topics

I have the same question (0)

All responses (3)

Answers (0)

ScottShearer 25,270 Most Valuable Professional on at

Like (0)

Report

@Brucla1968

The short answer is probably. You can certainly use Power Automate to make calculations but Excel can do much more than Power Automate. So, it is likely that you can do the calcs.

Your issue will be identifying the row to be updated.

Can you tell me if the edits to Excel are always done from a Power App?

Was this reply helpful? Yes No
Brucla1968 70 on at

Like (0)

Report

Hi @ScottShearer , thanks. Probabily I did not explain properly my problem. The way I used (excel file with tables and formulas in the table's columns) for me is ok as solution. With Power Automate I already have a flow which idenfies and copies right rows, not problems in this way.
Problem is: tables in excel file on onedrive lose their auto-filling property and when a new row is automatically added from external questionnaire using Zapier sometimes I find only data but columns with formulas are missing (auto-filling did not work). I try to renew tables and it works for a while, then stop working.
For this reason I thought to switch from excel formulas to fill cells with Power Automate flow

Was this reply helpful? Yes No
Brucla1968 70 on at

Like (1)

Report

I do not want to go out of topic and I will try again to solve problem of auto-filling formulas in excel tables on onedrive business, but here the problem. One row was added automatically but formulas are misssing. I solved one time simply deleting table format and renew table (same name, same size) but there is no sense to do that.
If it's a nightmare to manage cells filling with Power Automate I will try to fix problem in different way

Was this reply helpful? Yes No