An interesting Unique ID issue specific to Flow/Excel connection

[BenDonahue]

On a monthly bases I will be using one Form to collect up to 5 pieces of data, each of which will have its own row in Excel table MasterDataCollection.

I can easily create a formula that will always increment for the Unique ID:

=IF(ISNUMBER(OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),-1,0)), OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),-1,0) + 1, 1)

And the five rows added create these IDs:

 

After creating this row, I am adding this same data to a Log sheet, then, in subsequent months, I can reference MAX(Log!A:A) to grab the value of the last unique ID, and increment that to continue the Unique IDs. When I add this same data to the Log, of course the above formula will simply take the value of the row above, and add 1 to it. This will not work, as I am capturing all data entries for this row on new rows, to help with tracking down shenanigans.

 

I tried getting and referencing the row just added to MasterDataCollection, but that grabs the formula, and not the value, producing these results:

 

Failed solutions I tried:

1. As the row added will always be the last one, maybe I can reference the cell with something like

"MasterDataCollection!A(MAX(MasterDataCollection!A:A))"

This, of course didn't work.

2. In Flow, I tried using the Expression Editor to add dynamic content and an integer. That didn't work.

I am open to all solutions. My instincts tell me that, if I can reference the value of that calculation of a cell, I can (probably) run with the rest. 

Your help is greatly appreciated.

[yashag2255]

Hi @BenDonahue 

You can try to use the action List Rows in the Table and then use the length function to get the number of rows returned and then increment that by 1 for each new row. 

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[BenDonahue]

The question them becomes, how do I increment the row number without using a function in that cell? The problem I am facing on this is, when I add a function that increments by 1, then that formula gets added to the Log sheet, and produces a different number.

My instincts tell me that I will need to do this with an expression, as that will add a static number to the cell and not a function.

Or, am I missing your point?

Thank you for your help, so far.