Dynamically Reference Named Ranges?

[Erik the Awful]

Is there a non-macro way to dynamically reference named ranges?

I use a database at work for tracking inspections on equipment. I have no power over the database and can only pull a two-thousand-row long report with the pertinent info. The database report has an individual line for each inspection for each piece of equipment, for each location on the equipment (the equipment can have multiple parts with the same inspection). I'm also simplifying a bit, but it does not change the requirement of the formula.

Macros are locked out by our security settings. No macros, it must be done by formula.

I built a spreadsheet that takes the data and puts it into a displayable matrix. The inspections are listed in column A and the equipment is listed in row 1. Column B has the reference that requires the inspection. Column C has the location on the equipment for the inspection. The matrix shows the due dates of the inspections.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Due Dates
[/TD]
[TD]Reference
[/TD]
[TD]Loc
[/TD]
[TD]Machine A
[/TD]
[TD]Machine B
[/TD]
[TD]Machine C
[/TD]
[/TR]
[TR]
[TD]Thingy Replacement
[/TD]
[TD]1.1
[/TD]
[TD].
[/TD]
[TD]10/1/2018
[/TD]
[TD]11/1/2018
[/TD]
[TD]02/1/2019
[/TD]
[/TR]
[TR]
[TD]Whatzit Inspection
[/TD]
[TD]1.3
[/TD]
[TD].
[/TD]
[TD]10/3/2018
[/TD]
[TD]10/7/2018
[/TD]
[TD]9/30/2018
[/TD]
[/TR]
[TR]
[TD]Gizmo Adjustment
[/TD]
[TD]3.2
[/TD]
[TD]01
[/TD]
[TD]4/12/2019
[/TD]
[TD]6/6/2019
[/TD]
[TD]3/4/2019
[/TD]
[/TR]
</tbody>[/TABLE]

For a few years now I've tweaked and reformed the spreadsheet. I added a similar page that shows the responsible shop for performing the inspection. I added a similar page that tracks the serial number of a component that may be installed during the inspection. I added a page that can draw a monthly schedule of major inspections so I can prevent concurrent maintenance.

Now the spreadsheet takes 30 seconds to recalculate and I'd like to simplify. I changed the hard cell references in my index-match formula to dynamic named ranges that can resize when data is added and deleted, and I no longer have to worry about my cell references shrinking when other people delete the rows of the database dump.

I would like to further simplify to a single page matrix where I can click on a single drop-down menu in cell A1 and select the data displayed in the matrix.

Considering the named ranges:
DBEquip = DBase!$C:$C
DBInsp = DBase!$G:$G
DBRefer = DBase!$B:$B
DBLocat = DBase!$D:$D
DBDueDt = DBase!$E:$E
DBShop = DBase!$A:$A
DBSerial = DBase!$F:$F

[TABLE="width: 500"]
<tbody>[TR]
[TD]Shop
[/TD]
[TD]Reference
[/TD]
[TD]Equipment
[/TD]
[TD]Location
[/TD]
[TD]Due Date
[/TD]
[TD]Serial Number
[/TD]
[TD]Inspection Name
[/TD]
[/TR]
[TR]
[TD]Technicians
[/TD]
[TD]3.2
[/TD]
[TD]Machine A
[/TD]
[TD]01
[/TD]
[TD]4/12/2019
[/TD]
[TD].
[/TD]
[TD]Gizmo Adjustment
[/TD]
[/TR]
[TR]
[TD]Mechanics
[/TD]
[TD]1.1
[/TD]
[TD]Machine A
[/TD]
[TD].
[/TD]
[TD]10/1/2018
[/TD]
[TD]AB432
[/TD]
[TD]Thingy Replacement
[/TD]
[/TR]
[TR]
[TD]Mechanics
[/TD]
[TD]1.3
[/TD]
[TD]Machine A
[/TD]
[TD].
[/TD]
[TD]10/3/2018
[/TD]
[TD].
[/TD]
[TD]Whatzit Inspection
[/TD]
[/TR]
</tbody>[/TABLE]

...and the current formula from cell D2, the upper left cell in the matrix:
=INDEX(DBDueDt,MATCH(D$1&$A2&$B2&$C2,DBEquip&DBInsp&DBRefer&DBLocat,0))

How can I replace "DBDueDt" with a dynamic reference to select between viewing due dates, shops, and serial numbers? Or have I hit the limitations of Excel?

 

[Roderick_E]

look at INDIRECT, replacing all your static references as needed, perhaps with another dropdown list where you pick which "DB" to access

 

[Erik the Awful]

look at INDIRECT, replacing all your static references as needed, perhaps with another dropdown list where you pick which "DB" to access

I tried putting a cell with a dropdown for which named data to draw, but in the example above, if you put the desired data in A1 and change the formula to:

=INDEX(INDIRECT(A1),MATCH(D$1&$A2&$B2&$C2,DBEquip&DBInsp&DBRefer&DBLocat,0))

...it will give you an error. I forget which error, and I am not currently at work to try it again. As I understand it, INDIRECT() does not work with named ranges.

 

[Roderick_E]

What does this part return?

 

[Roderick_E]

Also... This array will return the range per selection of A1

=IFERROR(INDEX({"DBase!$C:$C","DBase!$G:$G","DBase!$B:$B","DBase!$D:$D","DBase!$E:$E","DBase!$A:$A","DBase!$F:$F"},MATCH(A1,{"DBEquip","DBInsp","DBRefer","DBLocat","DBDueDt","DBShop","DBSerial"},0)),"Selection not found")