Showing results for 
Search instead for 
Did you mean: 
Skilled Sharer
Skilled Sharer

Referencing a cell, using a formula, given a static column value and generic row expression

 I need a formula that will generate a cell address by matching a static column value with a dynamic row value
I am using Forms/Flow to add rows to an Excel Sheet. Based on a location being selected, I want to enter an email address into a column. Because this formula is being entered into a cell as the row is created, I can't simply drag the formula down the column.

Solution 1 that did not work: Accessing the value of the location (from Forms response) and conditionally adding an email address straight into the correct column using this expression in Flow: 
=IF(first(triggerBody()?['Which office's Office Manager is the Auditor?'])= "office1","","")
This entered the correct email address, but broke functionality that populated the rest of the data into Excel, in that, the rest of the data entered into the Excel sheet directly from the form, was not entered. Removing this formula returned the functionality. From what I have read, this is the solution 

Solution 2 that did not work: Entering a formula into the email column cell that conditionally enters the email address based on the value in the adjacent Excel cell. The problem here is that, because I am creating a row when I enter this data into Excel, I both:
A) don't know what row this data will appear on, and
B) can't drag formulas to populate the cells dynamically from within Excel.
Here are two examples of formulas I tried that did not work, but might help me communicate my issue:
Thank you.
Helper I
Helper I

Hi - i'm running into a simmilar issue as this.  Have you had any success?

Skilled Sharer
Skilled Sharer

I honestly don't completely remember this particular issue. My instincts are that I tried Offset(), but that evaluates constantly, so supremely slows Excel performance. 


I believe the solution that worked involved using a Filter block. Filter has greatly improved performance in all my flows, and I wish I had learned about it way earlier. Filter the table for Location = "Location 1." This will leave you with one row with that location's details and accessing the email from there is easy.


Best Practice
After escalating a related issue through the Microsoft Support world, I was told that the solution for how to add formulas dynamically into Excel sheets is, don't do it. Basically, I was trained away from the approach described above using Flow to add formulas to Excel cells. The Best Practices approach is to put all your logic in Excel or all your logic into Flow, but don't mix; I still do, of course, but, in dire situations where I don't have the time to research the right way, I cheat, but carefully.


On Cheating (referenced from previous line)
If you put formulas into your Excel sheet, you are going to want to make the calculated values static. The way I have done this is to, at the end of the flow, grab each row and over-write it with itself. Sounds useless, right? Well, what happens is, Flow reads the value of the Excel cell and not the underlying formula so, when it writes back to the cell what it read, it over-writes the formula with the static value.

Helpful resources

Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Users online (2,689)