cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
Helper V

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","EmailForThat@Office.org","")
 
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:
=IF(H+(ROW())="LOCATION 1","EMAIL ADDRESS 1","")
=IF(H(ROW())="LOCATION 1","EMAIL ADDRESS 1","")
 
Thank you.
0 REPLIES 0

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Top Solution Authors
Top Kudoed Authors
Users online (7,296)