VB Macro to jump to cell referenced in INDEX function

[shadydeals]

Hi --

I am using the INDEX function to pull values from a named range on another tab with matching row and column headings.

Example formula is:
=INDEX(DebtOther,MATCH($A12,INDEX(DebtOther,,1),FALSE),MATCH(CM$2,INDEX(DebtOther,1,),FALSE))

Where:
DebtOther = named range on another tab
$A12 = row item to match (ex. Account Receivable)
CM$2 = column item to match (ex. a date)

When others are auditing the spreadsheet, it is difficult to find the value the formula is referencing, so I am hoping to add a macro to quickly take a person to the cell in question.

Notes:
1. sometimes there are multiple references... ex. index(...) - index(...). In this case, I want to isolate and jump to the first index(...) function.
2. sometimes there is a cell reference or constant before the first index(...) reference... ex. $B$8 * index(...). I want to ignore $B$8 in this example.

**I also want to do the same with an HLOOKUP, but I think I can figure that out if someone can help with the INDEX function above.

Thank you!

 

[adam087]

Have you tried looking at Excel's built in 'Trace Precedents' function - it's on the 'Formulas' tab on XL2007/2010?

Apologies if you have already tried this.

Regards
Adam

 

[shadydeals]

looks like my reply didn't make it through...

no apologies necessary... I would love a simple fix! Trace precedents will only take my to the referenced range. In this case, that is a large area, so not very helpful.