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!

3 REPLIES 3
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. ]

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Users online (3,044)