Help with Data validation and list with formula

Hi,

It is my understanding that Validation lists cannot be populated using formulas, but rather only static data. There is a possible solution available using VBA if you would be interested.

Let me know and I will be happy to help you out

hi,

i tried and possible to use index and match for validation data list.
please use this formula on E2..

=INDEX(Avaliability!$B$3:$AH$12,,MATCH($B2,Avaliability!$B$1:$AH$1,0))

regards,

Hi,

I had updated the file with necessary changes as u requested and it is working fine for me


Punnam

Yes you can use a formula, sorry that was my mistake. However, your formula will only return a single value. What you want is the OFFSET function. Highlight column E2:72 and enter this formula in the Data Validation List source entry field:

Please Login or Register  to view this content.

Hi compspider,

Let me take a stab at explaining this formula for you:
1. Availability (named range): Punnam has defined this named range as Availability!$A$1:$AH$1
2. MATCH(B74,Availability,0): this will return the column number on the Availability worksheet that represents the date in cell B74
3. CHAR(): this function takes an integer and converts it to a character according to your computer's character set. In most instances, CHAR(65) = A, CHAR(66) = B and so on. Hence, by adding 64 to the MATCH formula, the CHAR function will return the column letter
4. The 4 and the 10 that you ask about are the top and bottom rows of the range of names on the Availability sheet that you want in the drop-down list, and the colon indicates that everything to the left is the start of the range, and everything to the right is the end of the range
5. INDIRECT: this function converts text to an Excel range.

In my opinion, this is actually quite a messy way to return a range, when a simpler and faster OFFSET/MATCH formula can also be used to return the same result. (Note that an INDEX/MATCH won't work here, as this will return a single value. Instead of INDEX, I use OFFSET to return an entire range).

I hope this helps

If this solves your problem, please don't forget to mark this thread as solved, and please click on the * next to my post to say thanks. If you do decide later that you need some more help with this same problem, please start a new thread, as often people will unsubscribe from your thread once it is marked as solved, so you are more likely to get the help you need by starting a new thread.

Thanks

@Ajryan

Thanks for the explanation .

1) My intention is to help in the best way i can do, i am not having any idea of "Offset" & its use, if u think any part of my solution is messy, u can be replaced with " Offset " guide me. i am open to learn.

Punnam

Hey no problems Punnam. I wasn't being critical. Just that I learnt a lot from this website before becoming a contributor myself, so now I do my best to pass that knowledge back to the people who are now in the position that I used to be in.

Have a great day