match either of 2 criteria

[k1s]

Hi I've got a dependent drop down based on a named range using this formula:

=INDEX(Table1[Column2],MATCH(ThisYear,Table1[Year],0),1):INDEX(Table1[Column2],MATCH(ThisYear,Table1[Year],1),1)

The available items for the user to select from comes from column2 based on matching against column1 the named cell "ThisYear". The value of ThisYear can be whole numbers 1-6 or string "FS"

It all works nicely, however, I'd like to modify it so that the dependent list shows items matching ThisYear and 'last year' - like it the sheet & picture attached

Anybody know how to do that?

I was thinking perhaps I could do it with some kind of nesting using the OR function, but would appreciate some help.

Many thanks

 

[PCosta87]

Hi I've got a dependent drop down based on a named range using this formula:

=INDEX(Table1[Column2],MATCH(ThisYear,Table1[Year],0),1):INDEX(Table1[Column2],MATCH(ThisYear,Table1[Year],1),1)

The available items for the user to select from comes from column2 based on matching against column1 the named cell "ThisYear". The value of ThisYear can be whole numbers 1-6 or string "FS"

It all works nicely, however, I'd like to modify it so that the dependent list shows items matching ThisYear and 'last year' - like it the sheet & picture attached

Anybody know how to do that?

I was thinking perhaps I could do it with some kind of nesting using the OR function, but would appreciate some help.

Many thanks

Hi,

Here you go (refer to attachment)
In the first INDEX of the named range, I changed the MATCH function to look for the smallest year in the table instead of the actual year indicated in "E3"!
EDIT: This will return a list of items matching all previous years up to the current year.

 

[PCosta87]

For "This Year" and "Last Year" only, use "ThisYear-1" in the MATCH function of the first INDEX of the named range.

 

[k1s]

Hi, Thanks. That seems to work for numerical values in the Year column. The problem is I have text values too (a year called "FS").

Any ideas?

 

[k1s]

Also if I Have only numerical values, starting from 1 and "ThisYear"=1, it doesn't seem to work.

 

[PCosta87]

Hi,

I would use a helper column then (refer to attachment)

Hope this helps

 

[k1s]

Thank you. Still not functioning as expected if the value of "ThisYear" is the same as the top row in the table. If if put "FS" in cell F3, I need to see items 1&2 in the list. Or ThisYear = 1 and the value in the first rows in Year column is 1 (not FS), the drop-down doesn't show any values. I guess I could just trap with with nested IF's...

 

[PCosta87]

Here you go... should be working now

 

[k1s]

Here you go... should be working now

Thank you,

For anyone tracking the thread the helper column uses this formula:
=IFERROR([@Year]-ThisYear,IF([@Year]=C5,B5,B5-1))

so, logically,
=IFERROR([@Year]-ThisYear, IF([@Year] is the same as the [YEAR] below this row, then use the Helper column value below this one, otherwise that value -1

...and the named list "dd_1" is defined as:
=INDEX(Table1[Column2],IF(ThisYear=INDEX(Table1[Year],1),MATCH(ThisYear,Table1[Year],0),MATCH(-1,Table1[Helper],0))):INDEX(Table1[Column2],MATCH(ThisYear,Table1[Year],1),1)

 

[bosco_yip]

Another option for dependent list

1] Define Name

Name : dd_1

Refer to, formula :

=OFFSET(C3,MATCH(IF(E3="FS",E3,INDEX(Table1[Year],MATCH(E3,Table1[Year],0)-1)),Table1[Year],0),,COUNTIF(Table1[Year],E3)+IF(E3="FS",0,COUNTIF(Table1[Year],INDEX(Table1[Year],MATCH(E3,Table1[Year],0)-1))))

2] See attachment

Regards
Bosco