How can I retain an existing Excel formula

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

Output

Accepted answers

kat

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

All comments

kat

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.

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?

r4upadhye

Yes, its possible to retain original excel formulae.

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

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703