How to trace an formula from the excel cell

[Ashwin_99]

lets say 

I have a cell which shows a value 

The cell containins a formula 

So i have to document it in another excel file the cell reference and cell value and cell formula 

And then in next hop i will track the formula it it says take the sum of cell a3 and b3 so now i will track those cells .

Same then will go to cell a3 and documents cell reference cell value and formula in it same goes for b3. And futther 

This is what i do which is manual work

Is it possible in alteryx 

[Tam]

@Ashwin_99 are you talking about Excel Trace Dependents and Trace Precedents? 

[danilang]

Hi @Ashwin_99 

An excel file is just a zip archive with a .xlsx extension.  Take sample file, change the extension to .zip and the you can open it with 7zip or a similar tool. 

 The formulas show up as <f> elements in the {sheetname}.xml file.

To read this Alteryx set up your input tool to read the .xlsx file as as a zip file and set the File in Archive to be \xl\worksheets\sheet1.xml.  From then on you can treat sheet as a xml file and use the xml tools to extract the information you need.  The attached sample extracts the f child element to give

Of course this is just the start since you'll now have to build the logic to trace the formula tree

Dan

 

[Ashwin_99]

Hi,

 Thank you for your response,

Its working fine i am getting the formula

But not getting the sheet name and cell name in the same row 

So its quite difficult to find which formula belongs to which sheet and which cell

Is there any solution for this....?

[danilang]

Hi @Ashwin_99 

The sheet name is the name of xml file that you read in the input tool.  

If you change the Specific Child Name from "f" to "c" 

 

you'll get the cell names on the same row as the formula.  

Parsing the entire excel file is not a trivial process and will involve macros to loop through the sheets and a detailed knowledge of how to use the XML parse tool.  A good place to learn the latter is the Weekly challenge called A Symphony of Parsing Tools. 

Dan