CODEWITHSUNDEEP
exceltraceexcel-formulaoperator-precedence
ysap
ysap

Reputation: 8115

How to trace indirect precedents in Excel?

The Trace Precedents/Dependents buttons in Excel are extremely useful in analyzing the structure of a complex spreadsheet.

I have a sheet comprised of many calls whose precedents are generated programmatically, using reference functions like CELL(), OFFSET() etc., where the arguments to these functions are expressions.

When using the trace buttons, excel won't trace the cells that are not explicitly referred to in the formula.

Is there a way to get the full set of precedents of a specific cell?

Note: searching Google I saw an application in the Office Marketplace that is available for purchase and supposedly does that. I wonder if there is a way of doing so with the provided Excel functionality?

EDIT: here's an example: in cell M5 I have the following formula, which references D5, D13, and G6. G6 is calculated by the OFFSET() function, relative to G4.

=IMSUM($D5,IMPRODUCT($D13,OFFSET($G$4, 2*(ROW()-ROW($G$4)), 0)))

When tracing the cell's precedents, Excel does not show the G6 cell. Instead, it shows the base of the offset, which is G4.

Upvotes: 4

Views: 3496

Answers (1)

Aprillion
Aprillion

Reputation: 22340

a) reverse engineer the whole formula engine of Excel

b) write a VBA parser just for your case - i.e. get the arguments of OFFSET from the formula string and evaluate them

c) don't use volatile formulas if you can avoid it - like
=IMSUM($D5,IMPRODUCT($D13,INDEX($G$4:$G$999, 2*(ROW()-ROW($G$4)) + 1)))

Upvotes: 1

Related Questions