Using INDEX and refering to a Drop down

[umaks]

Hi, I basically want to create a drop down where you can select an age group and it simply lists the top 20 medical conditions in one column, number of admissions last year and this year, in the next two columns.

Basically I have five tables by age group - 20s, 30s, 40s, 50s, 60s

For each table, the column headings are Medical Condition (where the rows contain the list of the types of conditions), column 2016/17 YTD (rows containing the number of admissions for each condition), and column 2017/18 YTD (again, listing the number of admissions for each condition).

I have created Defined Name for each of the five tables - (I.e defined names are the age groups).

I then created a drop down list of the Defined Names - (which are the five age groups).

I now want to create a separate table which includes the list of top 20 medical conditions (and corresponding activity) for whichever age group you select from the toggle.

I thought it would be as simple as doing =index($b$4,3,1), where $B$4 refers to drop down which contains the Defined names (I.e the age group) I have just created. The 3 would refer to the third row, and 1 referring to the first column.
(I would have therefore created a whole table so then it would refer to the exact position, but the data would change according to the drop down toggle).
Why does it not pick up $B$4 as the array or Defined name.

How else can I crack this nut? Your help would be much appreciated

 

[ajamess]

=index(Indirect($b$4),3,1)

 

[umaks]

Unfortunately that doesn't seem to work either

 

[ajamess]

Make sure your Defined names match up exactly with your dropdown list.

I just made an example sheet, with 3 named ranges, "Test", "Test2" and "Test3". I made a data validation dropdown in B1 from a list of "Test", "Test2" and "Test3" and when I use

=index(indirect(B4),2,2)

and switch between the data validation options it works correctly. I tried with different row and column numbers, and all works fine.

 

[Eric W]

ajamess has put his finger on the main problem. But if you want the list of conditions to be listed in order, you might need some additional formulas. For example:

	B	C	D	E	F	G	H	I	J
1							20s
2							Medical Condition	2016/17 YTD	2017/18 YTD
3	Age Range		Top 20 Conditions	Count			Acne	1	8
4	Twenties		Cholera	25			Stubbed toe	14	23
5			Malaria	25			Flu	20	15
6	Year Range		Stubbed toe	23			Asthma	6	17
7	2017/18 YTD		Hayfever	19			Hayfever	1	19
8			Asthma	17			Bad hair day	13	17
9			Bad hair day	17			Broken leg	12	7
10			Flu	15			Cholera	11	25
11			Acne	8			Malaria	9	25
12			Broken leg	7			Dizziness	23	1
13			Dizziness	1

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula E4 =IFERROR(LARGE(INDEX(INDIRECT($B$4),0,MATCH($B$7,INDEX(INDIRECT($B$4),1,0),0)),ROWS($E$4:$E4)),"") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula D4 {=IF(E4="","",INDEX(INDIRECT($B$4),SMALL(IF(INDEX(INDIRECT($B$4),0,MATCH($B$7,INDEX(INDIRECT($B$4),1,0),0))=E4,ROW(INDEX(INDIRECT($B$4),0,1))-ROW(INDEX(INDIRECT($B$4),1,1))+1),COUNTIF($E$4:$E4,E4)),1))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

The range H2:J100 has a named range of "Twenties". The other age ranges are defined just the same. In B4 is your drop down list for the age range, and in B7 I put in a drop down for the year. With those in place, you can use the formulas in E4 and D4 to get the list of conditions, sorted by frequency. It will dynamically change as you change the dropdowns in B4 and B7. Hope this helps.