Dynamic Index Array

smitpau

Board Regular
Joined
Apr 2, 2020
Messages
143
Office Version
  1. 365
Platform
  1. Windows
Hi,

I'm not sure if this is possible so would appreciate any responses.

I'd like to be able to use a named range in an index but would like the named range to be dynamic.

For example ideally the formula would be like =index(cell reference to dynamic array, etc).

The problem is I don't believe you can cell reference a named range.

I believe I'll have to list all the named ranges and then make the area_num dynamic which is not the most practical solution.

Thanks for reading,

Paul
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,833
Office Version
  1. 365
Platform
  1. Windows
Do you mean you want to put the name of a named range in A1 & then use =index(A1,...)
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,823
Do you mean this?
Book1
ABCDEFGHI
1Range1Range2Range3Range4RangeOffsetResult
2DogCarCaliforniaOrangeRange33Oregon
3CatRVWashingtonBlue
4MouseTruckOregonGreen
5IguanaBusNevadaRed
Sheet13
Cell Formulas
RangeFormula
I2I2=INDEX(INDIRECT(G2),H2)
 

smitpau

Board Regular
Joined
Apr 2, 2020
Messages
143
Office Version
  1. 365
Platform
  1. Windows
Thanks both for the quick responses.

It's like Fluff described.

So say there are 10 named ranges, an IF formula could return the specific named range for the array I'm after but it wouldn't recognise it as text.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,833
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Eric has shown you how to do it. :)
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,823

ADVERTISEMENT

Glad we could help! Thanks for the feedback. :)
 

smitpau

Board Regular
Joined
Apr 2, 2020
Messages
143
Office Version
  1. 365
Platform
  1. Windows
This may bit a bit fussy and there may not be an answer to this but here goes anyway.

Is there a way to do this without using a volatile function such as INDIRECT (conscious this logic repeated could slow a file down).

Thanks
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,823
I am not aware of a way to do this without a volatile function, at least in the general sense. Depending on how your sheet is laid out, and where your defined ranges are, you could do something like:

Book1
ABCDEFGHI
1Range1Range2Range3Range4RangeOffsetResult
2DogCarCaliforniaOrangeRange33Oregon
3CatRVWashingtonBlue
4MouseTruckOregonGreen
5IguanaBusNevadaRed
Sheet3
Cell Formulas
RangeFormula
I2I2=INDEX($B$2:$E$5,H2,MATCH(G2,$B$1:$E$1,0))


This assumes the ranges are all adjacent. Depending on how your ranges are arranged, there might be a way.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,094
Messages
5,640,078
Members
417,126
Latest member
Jeffman52

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