How to trace an formula from the excel cell
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@Ashwin_99 are you talking about Excel Trace Dependents and Trace Precedents?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
 
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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....?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
 
