Copy formulas in a OneDrive Excel using Office 365 Excel package does not change the formula dynamically

Hi @RuchirAgarwal 

You can use the simulate key strokes to copy the formula and to move around GoTo cell.

@RuchirAgarwal ,

You can use the Set Cell Formula action with the required formula for this within a loop. For each iteration, increment the row number value.

Hi @Zaid Chougle,

Keystrokes would not work with Office 365 Excel commands as we do not open the excel in this case.

Hi @Padmakumar, 

Thanks for replying. But we have lots of complicated formulas that are there in an existing excel from the Business. Now if we do it this way, we will have to identify which are dynamic ones and update row numbers accordingly which might be an error prone task. Probably, will have to store all the formulas in a config file stating the dynamic values and replace those at runtime & paste. However this would have a downside that the business cannot change the formulas on their own in the spreadsheet. Probably will have to live with that.

Thanks,

Ruchir Agarwal

HI @RuchirAgarwal ,

You can decide where you need to keep the formula part. But according to your requirement, this is the best option.

Could you please confirm whether you are working on a single Excel file which already contains all formulas, or are you creating a new file during each BOT execution?

Dear @RuchirAgarwal,

When you are copying and pasting formulas in Excel using the Office 365 Excel package in Automation Anywhere, the formulas will not be updated dynamically like they would be when you do it manually. This is because the package commands are designed to replicate the actions you would take when working with Excel manually, rather than providing more advanced functionality.

To achieve the behavior you are looking for, where the formulas are updated dynamically based on the cell they are pasted into, you will need to use a different approach. One possible solution would be to use the "Get Cell Value" and "Set Cell Value" commands to read the value of the source cell and then update the formula in the destination cell accordingly. This would involve writing a custom script or logic to process the formula and update it as needed, rather than simply copying and pasting it.

Another option would be to use a different type of automation tool that is better suited to working with formulas and other complex Excel features. For example, you could use a tool that provides a higher-level API or other advanced features for working with Excel, which would allow you to manipulate formulas and other elements more easily and dynamically. This would require more advanced knowledge and skills, however, and would not be possible using the Office 365 Excel package alone.

Regards,

Hi @Andoni , Converting the spreadsheet to a table makes things a lot easier. The formulas are copied automatically whenever data is added to a new row.