cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CharlesTEA
New Member

When my flow tries to pull data from a formula cell in Excel it is generating null value error

I have built a simple flow, my first ever, to automate part of our admin user access request process.

 

basicflow.png

 

When a new user requests admin access, an email hits my inbox. My flow grabs that email, parses the relevant parts, and adds a row to a OneDrive Excel spreadsheet. The sheet the flow feeds has a number of formula columns, mainly concatenate, which I want to use in my automated reply, but it seems I can't. This flow terminates at the Initialize CurrentAdminEmail step with this error.

Screenshot_2021-06-23 Run History Power Automate.png

The cell that is supposed to feed into that step is an INDEX-MATCH formula that finds other admin users at that organization from another sheet. Near as I can figure, when this flow adds a new row, the formulas don't calculate, which means when it looks in that cell, it's empty. What's the fix here? I haven't found a "Make Excel calculate formulas" step.

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions

Many months later, my flow is working exactly as I envisioned. I had a conversation with someone at Microsoft where I described how I intended for Power Automate and Excel to work together, and his first reaction was to point out much of what I was relying on Excel to do I could be doing in Power Automate, particularly all the Concatenate and Index-Match functions. So instead of using a compose step to grab the contents of a cell containing an Index-Match formula, I used a Get a Row step to grab the data I need from the sheet directly using a key value I extract from the request email. Then instead of building up the auto reply text using a bunch of Concatenate and If functions in Excel, I am using control steps in my flow and just arranging the appropriate text and variables directly in the Send an Email step at the end.

 

It's been a long(ish) journey, all of which was kicked off by this video,

https://www.youtube.com/watch?v=OrCs36S3w3w. It's a great intro, and it got me to a nice in between place where I could copy and paste manually from Excel into reply emails I would send manually. This saved a lot of time, but I couldn't make it to the finish line without stepping out of my Excel comfort zone. Ultimately, the work I did creating that spreadsheet that autofills based on the request emails I get isn't even necessary unless I want a record of all the requests I get. Power Automate can parse everything and handle all lookups and logic branching.

 

Lots of fun and frustrations here. I'm looking forward to my next Power Automate project.

View solution in original post

4 REPLIES 4
tom_riha
Super User
Super User

Hello @CharlesTEA ,

the error message tells you, that in the "Initialize_CurrentAdminEmail" action you're using a last(...) expression, but the input of the last(...) expression is empty, no value enters the expression. Try to check what value should enter the expression, it probably doesn't contain the value you expect.



[ If I have answered your question, please Accept the post as a solution. ]
[ If you like my response, please give it a Thumbs Up. ]

[ I also blog about Power Automate solutions even for non-IT people. ]

Ideally, it should be a string of one or more email addresses, but when the flow adds a row in the table, it doesn't add data to that cell. Instead, there is a formula in the spreadsheet for that column in the table that pulls data from another sheet using an Index-Match function. I want the flow to grab whatever the result of that function is. When I look at the spreadsheet, that column is full all the way down. Every new row has something in that column, but when I try to grab it in my flow, it's null.

 

Last(...) is probably the wrong expression here. I want the full contents of the cell, but that cell is a formula and I specifically want the result of the formula.

Hello @CharlesTEA ,

check in the run history the output of the 'List rows present in a table' action, if it doesn't contain the data from the column with formula, then it's not sent to Power Automate and you can't use it in a flow.



[ If I have answered your question, please Accept the post as a solution. ]
[ If you like my response, please give it a Thumbs Up. ]

[ I also blog about Power Automate solutions even for non-IT people. ]

Many months later, my flow is working exactly as I envisioned. I had a conversation with someone at Microsoft where I described how I intended for Power Automate and Excel to work together, and his first reaction was to point out much of what I was relying on Excel to do I could be doing in Power Automate, particularly all the Concatenate and Index-Match functions. So instead of using a compose step to grab the contents of a cell containing an Index-Match formula, I used a Get a Row step to grab the data I need from the sheet directly using a key value I extract from the request email. Then instead of building up the auto reply text using a bunch of Concatenate and If functions in Excel, I am using control steps in my flow and just arranging the appropriate text and variables directly in the Send an Email step at the end.

 

It's been a long(ish) journey, all of which was kicked off by this video,

https://www.youtube.com/watch?v=OrCs36S3w3w. It's a great intro, and it got me to a nice in between place where I could copy and paste manually from Excel into reply emails I would send manually. This saved a lot of time, but I couldn't make it to the finish line without stepping out of my Excel comfort zone. Ultimately, the work I did creating that spreadsheet that autofills based on the request emails I get isn't even necessary unless I want a record of all the requests I get. Power Automate can parse everything and handle all lookups and logic branching.

 

Lots of fun and frustrations here. I'm looking forward to my next Power Automate project.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Power Automate Designer Feedback_carousel.jpg

Help make Flow Design easier

Are you new to designing flows? What is your biggest struggle with Power Automate Designer? Help us make it more user friendly!

Top Solution Authors
Top Kudoed Authors
Users online (1,389)