Alternatives to offset function

[Ufoo]

In order to avoid using offset function in determining the excel range to use in data validation (=OFFSET(C9,MATCH($B$6,$B$10:$B$22,0),0,COUNTIF(B10:B22,$B$6),1). Thanks Chandoo.org for this formula), I made this formula (=INDEX($C$10:$C$22,MATCH(B6,B10:B22,0)):INDEX(C10:C22,MATCH(2,1/(B10:B22=B6)))) which calculates the range correctly but is not accepted in data validation.
1). Any ideas on the way to determine the range without using offset function?
2). Any ideas of my formula above? Thanks

 

[bosco_yip]

Try to use this....

=OFFSET(C$9,MATCH(B6,B$10:B$22,0),0,COUNTIF(B$10:B$22,B6),1)

Or this,

=INDEX(C$10:C$22,MATCH(B6,B$10:B$22,0)):INDEX(C$10:C$22,MATCH(B6,B$10:B$22,0)+COUNTIF(B$10:B$22,B6)-1)

Regards
Bosco

 

[Marc L]

1) Just name the range …
​

 

[NARAYANK991]

Hi ,

As Marc has suggested , define a named range , say temp , and in the Refers To box , enter the formula :

=INDEX(Sheet1!$C$10:$C$22,MATCH(Sheet1!$B$6,Sheet1!$B$10:$B$22,0)):INDEX(Sheet1!$C$10:$C$22,MATCH(2,1/(Sheet1!$B$10:$B$22=Sheet1!$B$6)))

Note the use of the $ signs ; these are absolutely necessary.

Now , in your data validation , use the formula =temp.

Narayan

 

[Ufoo]

1) Just name the range …
​

Thanks Ninja. I have named the range, and it has worked. Thanks a lot

 

[Ufoo]

Hi ,

As Marc has suggested , define a named range , say temp , and in the Refers To box , enter the formula :

=INDEX(Sheet1!$C$10:$C$22,MATCH(Sheet1!$B$6,Sheet1!$B$10:$B$22,0)):INDEX(Sheet1!$C$10:$C$22,MATCH(2,1/(Sheet1!$B$10:$B$22=Sheet1!$B$6)))

Note the use of the $ signs ; these are absolutely necessary.

Now , in your data validation , use the formula =temp.

Narayan

Thanks ninja. It works. I am amazed!!