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

[CharlesTEA]

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

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.

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!

[tom_riha]

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.

[CharlesTEA]

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.

[tom_riha]

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.

[CharlesTEA · Accepted solution]

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.