Answered

Email link with the Excel data that contains formula

(0) Share

Report

Posted on by Younggu

Hi,

I am Younggu from Korea. I have been using “Power Automate” since February last year and it upgraded my workflow, but I had one problem I couldn’t solve.

My question is “Is it possible to use the excel data, calculated with formulas, at the Power Automate?”

I designed a flow like below.

Get a response via Microsoft Form -> Add a row into a table of Excel -> Send an email via outlook with the calculated data of the Excel sheet.

I want to use the formula, (vlookup), but when I embed the dynamic contents into the email template, then it becomes blank.

Categories:

Using connectors

I have the same question (0)

All responses (5)

Answers (1)

Gareth1 308 on at

Like (1)

Report
Copy link

Link copied!

Hi @Younggu
You should be able to retrieve the value from the formula results
If you have a table and the column is set to automatically calculated in Excel when you add a new row tot he table it will copy the vlookup down to the new row

Once the row has been added you can then use the List Rows present in a table to return the rows and then using a compose action to return the last row in the table. This will return the entire row including the results of the vlookup

In the example below I have the Salesperson ID column set as a VLOOKUP and once the new row is added I can retrieve the results

Also if there was a unique value in each row, you could use the Get a Row action instead of having to list all the rows and finding the last one

Are you able to share a screenshot of how you are trying to do it

Was this reply helpful? Yes No
Younggu 10 on at

Like (0)

Report
Copy link

Link copied!

Hi Gareth,

Thank you for your help.

I followed your example, but the Flow sent me multiple emails as I use the vlookuped data as an email address.

Thus, I changed the Flow according to your second advice, but it seems I do not know how to set the 'Get a row' properly.

Could you kindly advise me, please?

Was this reply helpful? Yes No
Verified answer

Gareth1 308 on at

Like (1)

Report
Copy link

Link copied!

In order to use the Get a Row action you would need to have a unique value in one of the columns for each row.

I tried using the submitted date however Excel may change the format of the date depending on the settings for excel. I was able to get around this by creating a key value for the row when adding a new row, then when I used the Get a row action I was able to pass this in and get the correct row

When creating the key I add a string to the formatted date to prevent issues with leading zeros

This should only return one result so you shouldn't need to perform the compose action with Last

Was this reply helpful? Yes No
v-alzhan-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @Younggu ,

For the multiple emails, you maybe add a dynamic content from the Get response details action.

You should add a expression as below in the To field:

outputs('Compose')?['Price']

and refer to screenshot shot below to create the flow:

Best regards,

Alice

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Was this reply helpful? Yes No
Younggu 10 on at

Like (0)

Report
Copy link

Link copied!

Hi again Gareth,

Please receive my bow. It's working well now.

Was this reply helpful? Yes No