Excel overwriting my formulas

Question

Even after turning off "Extend data range formats and formulas," Excel still overwrites my formulas with other formulas added to other cells in the same column. Am I missing another setting, or something?

Your help is very appreciated.

Answer 1

Sukie,

For accuracy's sake:

1. It was not the Power Platform support team that sent me to Excel for help, but a different tech at Mindtree
2. Though, for all I know, it could be the Excel Client that is causing my issues, I am experiencing them in Excel Online

On to your questions:

1. Could you provide the detailed steps about how you used Power Automate Flow to add formulas?

I am gathering 7 pieces of data in one form:

1. Auditing Office
2. Office Being Audited

3-7. ID numbers to identify certain records to be audited

I am taking this data and creating 5 rows in Excel, one for each of the ID numbers; each line gets the same Auditing Office and Office Being Audited data. I am attempting to create a unique ID for each of these lines. Starting out with the larges number recorded, I am adding 1 to it for the row in with the first ID, 2 for the row with the 2nd ID, and so on for all five rows. Obviously, these formulas are very similar, but slightly, and vitally, different. As each formula is added, all previous formulas are overwritten with whatever the last formula is, rendering my work useless.

Each time one of these rows is created, I am creating a duplicate of that row in a table labeled "Log." Doing so records the unique ID, in this case "Data ID," into the log accurately. Interestingly, and in this case, usefully, adding this cell into the Log records teh static number, and does not record the formula, something I wish to learn how to intentionally accomplished for future use.

2. 

3. No, it does not seem to be repeatable from my end. I wonder if, because that horribly rude setting, "Extend data range and formulas," has been turned off in my client, that, in the online version, that setting sticks?

Between rows 9 and 10 there is a visual queue that I do not recognize from the other document, specifically, the double horizontal line between the established table and the new entry in row 10.

I sincerely hope that this helps.

Good luck, Sukie, and thank you for your help.

Answer 2

Hi BenjaminHN,

We appreciate that you spent your precious time providing so detailed information.

We have read your description, it seems that you are experiencing the same issue in Excel Online and Excel client. And based on your description, it seems that you cannot reproduce the same situation with the other files.

May I know if my understanding is right? If so, may I know if you created several columns named Auditing Office, Office Being Audited and numbers in SharePoint list?

If so, since our category focus on Office 365 products, so we may not be proficient with Flow, however, we have also searched a lot and tested it on our side, we created a SharePoint list with several columns named Auditing Office, Office Being Audited and numbers, then we created a Excel workbook in this SharePoint library, and we also created same columns as list columns in SharePoint list.

Then we went to Power Automate and created a Flow, as shown below:

May I know if the steps above are right? If not, to better understand your scenario and test it on our side to check if we can reproduce the same situation as yours, could you provide the screenshot the flow you created?

And if the steps above are not consistent with yours, may I know how you created those columns and provide the full screenshot of Excel table?

In the meantime, you mentioned “ it does not seem to be repeatable from my end. I wonder if, because that horribly rude setting, "Extend data range and formulas," has been turned off in my client, that, in the online version, that setting sticks”, does it mean that this issue cannot be reproduced after turning off that option in Excel client?

We appreciate your patience.

Best Regards,

Sukie