VB Macro to jump to cell referenced in INDEX function

shadydeals

New Member
Joined
Jun 17, 2008
Messages
22
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!
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

adam087

Well-known Member
Joined
Jun 7, 2010
Messages
1,356
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

New Member
Joined
Jun 17, 2008
Messages
22
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,312
Messages
5,600,887
Members
414,414
Latest member
neil_c

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top