I'm working on a somewhat complicated but ultimately straightforward business process that:
1. Takes user input from MS Forms and writes that data to an Excel workbook stored on SharePoint, using a Power Automate Flow
2. Runs calculations on the submitted data to populate specific cells in a specific worksheet within the destination workbook (still using Power Automate)
I have created a Flow in Power Automate to organize how the data is written to the workbook using the "Add a Row" function. The Flow works -- I have tested it dozens of times, including completing the form many times to test the whole process. However, where I am consistently stuck is in getting my Excel formulae to function consistently and accurately.
The formulae are, in some cases, very basic and in others much more complex, but they work. The problem is, I can't get them to work for each new row that Power Automate writes into the workbook.
For example, two of the questions in the form ask for a number to be input into a text field by the user. When the numbers are written into the spreadsheet, I run a simple calculation on each figure that divides it by a standard value to arrive at a percentage. This calculation works, but ONLY on the first row. All subsequent rows added to the spreadsheet continue calculating based on the values entered the first time in the first row (in this case, Row 2).
In other words, if the first row of my table is Row 2 (because Row 1 contains headers, the formula looks like this:
=IF($F2=0,0,($F2/29.06))
If more data is submitted from the form, and I go to, say, Row 4, the formula is exactly the same -- it has not updated $F2 to $F4, so it is still calculating the values in Row 2, e.g., $F2. Here is a partial illustration of how the formulae are embedded in the Flow:
The second type of formula issue is slightly more complicated. For this one, I created a step in the Flow that adds a row to a worksheet table "Index." For "Index," I want to capture one piece of data entered into the form -- the value for "Country" -- and then run calculations on the rows of data received for that submission. (The Flow writes the form data to several different tables in different worksheets.) These calculations run at the end of the Flow, and are intended to analyze the most recent row in the table and calculate values based on the answer provided using VLOOKUP.
Here is an example of one of these:
=((VLOOKUP(AccessEquity!$B2,TelcoProviderValues,2,FALSE)
+IFNA(VLOOKUP(AccessEquity!$C2,WBAScoringValues,2,FALSE),0)
+VLOOKUP(AccessEquity!$D2,PercentageValuesLowHigh,2,FALSE)
+VLOOKUP(AccessEquity!$E2,PercentageValuesLowHigh,2,FALSE)
+IF(AND(AccessEquity!$G2>=0,$G2<=25.99),1,IF(AND($G2>=26,$G2<=50.99),2,IF(AND($G2>=51,$G2<=75.99),3,IF(AND($G2>=76,$G2<=100),4))))
+IF(AND(AccessEquity!$I2>=0,$I2<=25.99),1,IF(AND($I2>=26,$I2<=50.99),2,IF(AND($I2>=51,$I2<=75.99),3,IF(AND($I2>=76,$I2<=100),4))))
+IF(AND(AccessEquity!$K2>=0,$K2<=25.99),1,IF(AND($K2>=26,$K2<=50.99),2,IF(AND($K2>=51,$K2<=75.99),3,IF(AND($K2>=76,$K2<=100),4))))
+IF(AND(AccessEquity!$L2>=0,$L2<=25.99),1,IF(AND($L2>=26,$L2<=50.99),2,IF(AND($L2>=51,$L2<=75.99),3,IF(AND($L2>=76,$L2<=100),4))))
+IF(AND(AccessEquity!$M2>=0,$M2<=25.99),1,IF(AND($M2>=26,$M2<=50.99),2,IF(AND($M2>=51,$M2<=75.99),3,IF(AND($M2>=76,$M2<=100),4))))
+VLOOKUP(AccessEquity!$J2,PercentageValuesLowHigh,2,FALSE)
+VLOOKUP(AccessEquity!$N2,PercentageValuesLowHigh,2,FALSE)
+VLOOKUP(AccessEquity!$O2,PercentageValuesLowHigh,2,FALSE)
+VLOOKUP(AccessEquity!$P2,PercentageValuesLowHigh,2,FALSE)
+VLOOKUP(AccessEquity!$Q2,EquityValues,2,FALSE)
+VLOOKUP(AccessEquity!$T2,ReliabilityValues,2,FALSE)
+VLOOKUP(AccessEquity!$V2,AffordabilityValues,2,FALSE))/60)*4
As you can see, I'm using VLOOKUP to take the given response in the specified cells in a sheet named "AccessEquity", and then apply a numeric value to them based on value tables stored elsewhere in the workbook. These values are combined with IF statements to calculate values based on ranges for some responses, and then applying a score to them. Finally, I add them all together, divide by 60 and multiply that value by 4. The answers should be anywhere from 1 to 4, out to two decimal places.
I have embedded this formula (and the others) in the "Add a Row" functions in the Flow. Here is a partial illustration:
This calculation works successfully on the FIRST ROW (in this case, Row 2), and displays the calculated value in the appropriate cell. However, subsequent data submissions fail because the row number in the formula doesn't update. If I submit a second set of data, the formula in the first row (Row 2), changes to $B1 for some reason, and the newest submission (Row 3) becomes, e.g., $B2. Subsequent submissions also are, e.g., $B2, and do not increment to, e.g., $B3, $B4, $B5, etc., as in the previous example.
I have tried embedding the formulae in the "Add a Row" function in the Flow, as above, and it works, but only the first time.
Embedding the formulae in the destination workbook itself doesn't work because we are adding a row. I have tried using "Update a Row" for the final step, where the formula is embedded in the worksheet and I'm only updating the row to add the value for "Country" but that failed -- the Flow didn't work at all -- so if that is the correct approach I'm doing something wrong with "Update a Row" but I have no idea what.
So, is my approach completely wrong for what I am trying to do? Or am I making some kind of elementary mistake? Or neither?
6
Share
Report
All responses (
Answers (
