add or update excel sheet that had excel formulas

[GJeanes1]

Hello All! 

I am still new to using power automate, so bear with me. 

I have an Excel table that includes formulas to extract certain data from cells. 

I also have a Form that has been connected to the Excel sheet using Power Automate to "add a row" which does so but my formulas are not carried over to the new row of responses. 

I need it to create a new row of responses while still inputting the formulas, how can I create this?

[Nived_Nambiar]

Hi @GJeanes1 

I have tried a basic example simmilar to your scenario, it is working fine for me.

See below

I have created a small table which has three columns : Number 1, Number 2 and Sum

I have created a sum formula for sum column which takes two numbers Number 1 and Number 2 as input and place the sum of Number 1 and Number 2 in Sum column

Now i created a sample form which ask for user input for number 1 and number 2.

A simple flow is designed which takes the response from forms and add a row to table (fill the values of column Number 1 and Number 2)

 see the flow 

Now when i input two number values in form and submit it, it add a row of data to the excel table like below 🙂

Forumula is auto populating here !

Hope this  helps !

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[GJeanes1]

So in my data, I have the time submitted on forms to receive its own cell/column, and then just "=[@[Column name]]" and have a column for "time" to just read the time. 
I am also using Vlookup to find specific census tracts and match them with their respective neighborhoods.
below are the tests I ran to which nothing autopopulated.

[Nived_Nambiar]

Hi @GJeanes1 

May be i am still unclear on what you said 

Could you explain me what is the formula you have used?

Looking at the excel, formula result looks like #N/A , so the check datatype of cells used , it may effect the formula output 🙂

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[GJeanes1]

@Nived_Nambiar I appreciate your patience

to clarify for example, in the neighborhoods section: I have the respondents answer which census tract they are in. So for one column, I have census tract, the next column I have is neighborhood which reads another sheet

=INDEX(FORMULAS!A$13:A$152, MATCH([@[Census Tract]], FORMULAS!B$13:B$152, 0))

 The issue is when a form is submitted, a new row is populated with the information of the form without all the formulas such as the neighborhood example. 
How can I get power automate to produce a new line of submitted responses while maintaining certain excel formulas?

[Nived_Nambiar]

Hi @GJeanes1 

It seems to be strange when trying manually would give correct result but when trying via powerautomate gives different result 

So for the above formula, I think exact match may not be happening so that would be the reason by it is returning empty ?

Did you check the value which flow has pasted, does it have any spaces at end or beginning ?

Kindly check and let me know !

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[GJeanes1]

@Nived_Nambiar 

I figured it out. As I mentioned I'm still a novice with Power Automate so I did not know that you can add Excel formulas into Power Automate selections, I have it set up so the cells that were returning blank, now have my formulas within them.

[Nived_Nambiar]

Hi @GJeanes1 

I did not get your point, could you explain it more better ?

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[lizbiz520]

What do you mean? I'm having this same issue. Are you saying I can type my formula into the Power Automate field and it'll post into my excel table as a formula and perform the correct functions?

[GJeanes1]

@lizbiz520 Yes! So after lots of trial and error, that is the solution I have found. Simply type in what Excel formula so for my case it is a =Vlookup("with the correct array and fields"). I simply put that formula into the designated spot in power automate and it should render correctly.

[lizbiz520]

Ahhh yes, it worked! Thank you sooooo much. I was able to condense my form by half by auto-populating some of the values. This was a great quick fix solution, thank you again!