Update Next Row in Excel Table

Like (0) Share

Report

Posted on 27 Jun 2022 18:00:17 by Mike44

266

I have an Excel table with 3 columns - Index, Quote, and Display Turn. I'm trying to create a flow that sends the 'Quote' of the day via email to staff. The index column are numbers 1 through 184, quote column contains the quotes and display turn contains yes or no. My thinking is that all rows in 'Display Turn' are set to 'no' except one and then to update the next row in the column to yes and the current to no at the end of the flow process. I can get everything to work except updating the next row to 'no'. Below are pics of my flow. Initially, I was thinking if there was a way to get the Index and increment it by one to update the next row that would solve my problem, but I don't know how to do that. Basically, I'm missing the last step (e.g., updating the next row) because I can't figure out how to do it. Note: if there is a better way, I'm all for it.

I have the same question (0)

All responses (2)

Answers (2)

Verified answer

v-liwei-msft on 29 Jun 2022 at 10:02:25

Like (0)

Report

Re: Update Next Row in Excel Table

Hi @Mike44 ,

I have made a test for your reference:

1.You can enter DisplayTurn eq 'yes' in the filter query in List rows present in a table to filter the rows where DisplayTurn is equal to yes

2.Add a compose to view the index of the row whose DisplayTurn is yes.

3.Add update a row, use the Index column in the key column, and enter the formula add(int(outputs('Compose')), 1) in the key value. This will find the line next to the line where DisplayTurn is yes and change DisplayTurn to yes.
4. Add another update a row, use the Index column in the key column, enter the index returned by compose in the key value, and change DisplayTurn to no.

Best Regards,
Levi

Was this reply helpful? Yes No
Verified answer

eliotcole 4,271 Moderator on 27 Jun 2022 at 18:22:43

Like (0)

Report
Re: Update Next Row in Excel Table

With the greatest respect, @Mike44, a calculator (Excel) is the wrong place for this data, and you'll have a *much* easier time handling it within a SharePoint or Microsoft List. 😉

Either way, my advice is the same, rename the Display_Turn column to quoteSent, and only send the top 1 result of those that have not been sent, then amend that result to having been sent.

This is a purposefully general answer, and not specific to your exact field names and values ... so think openly about constructing it. 🙂

To do this you just need to tap the 'Show advance options' on the Sheet/List and:

Filter Query the results by quoteSent eq No

Order By Index

Top Count 1

This will return one result, which you can then send on, and update that row/item to change the quoteSent to Yes.

The next time you run it, it won't return the same result.

( the actual value of quoteSent can be whatever you want it to be, a true boolean, or whatever, just make sure that you're reacting to it and changing it appropriately )

Was this reply helpful? Yes No