How can I retain an existing Excel formula

[carl_steinhilber]

I have a Master Excel workbook. It already has rows of data. One of the columns is an Excel formula based on other cell values in the same row.

We use this workbook throughout the week, making live updates in which that Excel formula is critical.

But then, at the end of every week, we receive a separate .csv of data that contains updates to the master data. Some rows are added, some rows are updated, and some rows are simply duplicates of rows already in the Master workbook.

I'm trying to use Alteryx to open both data sources, and merge the new data into the Master, update and dedupe, then resave the Master workbook.

The problem is, I can't seem to retain the existing Excel formula. It's either wiped out, or it's saved as a string.

Is there a way to keep (or even rewrite) the Excel formula in that column so it's a dynamic calculation when saving back out to the Master workbook?

I've found some references in other posts that writing the formula as a string and then saving the file as .csv solves the problem, but a) that's not really an option in our environment, and b) I tested it and it didn't work anyway.

Any other ideas?

Thanks!

-Carl

[kat · Accepted solution]

Hi @carl_steinhilber

Alteryx wouldn't be able to deal with the formulas. I also think that most workarounds would be very clunky and not maintainable.

Whenever I'm faced with a situation in which I need to retain parts of an Excel workbook, I just create a data tab in the workbook and output my data there. Then use formulas to reference this. Works quite well. You can also go a step further and use commands to save the workbook with a new name (i.e. changing the date) before you write your data.

Hope this helps.

Kat

[carl_steinhilber]

Thanks kat,

I like the idea of writing to a data worksheet... but wouldn't this still suffer from the same issue? Alteryx is still opening the file and resaving it. Meaning it's still in charge of writing back out the original formula. Why is it that Alteryx can handle that, but not if it's on the same worksheet as I'm writing to?

[kat]

@carl_steinhilber

If all you're done is writing to a workbook, it doesn't have to first read it in. Therefore you don't have the issue. The other thing is that Alteryx works tab by tab. So if you have one data tab that's in 'Alteryx' format you can read and write this with Alteryx without messing up the rest of your workbook.

Hope that clears things up.

Kat

[r4upadhye]

Yes, its possible to retain original excel formulae.

You can choose to Preserve Formatting on Overwrite functionality to achieve it.

[Emmanuel_G]

Thank you for this trick.

But I don't have it on my Alteryx Interface( My version is 2021.2 ).

Have a good day.

[allwynthomas24]

Hello @carl_steinhilber , @Emmanuel_G , @r4upadhye , @kat
Hope y'all are doing good.

Y'all can use the workflow available in the below link which I had uploaded on the Alteryx Knowledge Base few months ago. This solution workflow will let you Retain / Preserve the Formulas which existed in the original Excel File (Master Workbook) after passing it through the Alteryx Workflow. Kindly make the necessary modifications as per your requirements. Please like and accept it as the solution if you found it helpful.

Retain Excel Formula After Running The Excel File Through Alteryx Workflow 

Regards,
Allwyn Thomas