Input Excel tab containing Formula

Hello!

I've come across a bunch of excel files where I need to read only one Tab (called "Output")

However the data in each cells in the tab I need to read the data from are all linked to an another tab in forms of formula looking like this :

=Alteryx!A2 or =Output!G2 (or again =Calc!N18*1)

The issue is that when I first input my data into Alteryx, I only have Null vallues

Only way to make Alteryx read the real value, is to open the Excel file, then close while saving.

Edit : here is a pic of my data

Issue is that I have 100 + files so I was wondering if there was any way around it

Thanks !

Capture.PNG

Capture 2.PNG

Tips and Tricks

Best Practices

Accepted answers

derekbelyea

Have you investigated going upstream in the data flow? It may be worth considering whether the values you need are elsewhere in the spreadsheet in a form that you can extract. Sometimes it just makes sense to remove some or all of the logic in the Excel template and re-build it in Alteryx. In the extreme you can think about removing Excel entirely from the equation.

KaneG

OK, so let me see if I have this straight...

Alteryx creates a tab in each spreadsheet called 'Alteryx'
There is then Macros in Excel that perform calculations and produce data in a tab called 'Output'
You then want Alteryx to read in this tab 'Output'

If the above is correct, then Excel will have to be opened somehow for those Macros to be triggered and the data saved. You could maybe script that in a Batch File, however it seems like a long way around...

As @derekbelyea mentioned, can you go upstream, i.e. what happens in the Macros? Can that be replicated in Alteryx? I'm sure it can, it just depends on whether the Macros access data that is locked down.

Kane

derekbelyea

Yes, that is what I was suggesting. Replicate the Excel logic in an Alteryx workflow.

Since you have these Excel spreadsheets already you can use them to validate and debug your replacement workflows.

Based on what you have revealed this will likely be the most robust solution and the least work for you (other than you may have to spend some time learning additional functionality in Alteryx - which is not a bad thing.)

KaneG

Yes, formula tools will definitely be used, but you may also need to use some other tools like summarize to perform any aggregations.

All comments

derekbelyea

Hakimipous

@derekbelyea

Thanks for the reply !

The values I need are in that specific sheet because they are the results of calculus made across all other sheets. They are in a way unique.

Also removing Excel from the equation is also not an option, as my data depends on macros and calculations performed on Excel

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703