Index, Indirect, Match, Large

ChidgeyP

New Member
Joined
Aug 27, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Afternoon All,

Wondering if I could get a little help.

I'm trying to create a "Top 10" list of Outstanding Invoices, by Value. I have 12 Tabs (one per month) which have the data for the Monthly Invoices on.

I've tried the following :

=INDEX(INDIRECT("'"&Q1&"'!B:B"),MATCH(LARGE(INDIRECT("'"&Q1&"'!P:P"),1),(INDIRECT("'"&Q1&"'!P:P",0))))

Which is pulling a #REF! Error.

Basically, B:B is my list of Invoice Numbers, P:P is the Value of the Invoice. Q1 has my Month's name in it. Changing the Formula from an Indirect to just refer to the named tab (January/February/etc.) it works just fine, but as soon as I change it to an Indirect, the error occurs. I was using the Indirect so I can use a Data Validation to flick between the Month's easily.

Am I missing something or have I just overly complicated this?

Any help would be greatly appreciated.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,242
Office Version
  1. 365
Platform
  1. Windows
Can you post some sample data of one of your month sheets & the output sheet.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,137
Messages
5,835,611
Members
430,370
Latest member
contentment

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