Data Validation Issue

Hi. I've got 60 columns of data in a table for a school cover timetable (for choosing which staff are available to cover absence for any particular lesson on any particular day). Each column has a header that represents green or yellow week, along with the day of the week and a lesson number e.g GM1 for Green week, Monday lesson 1 etc. The number of non-empty cells in each column of the table varies but the data (staff available to cover that particular lesson) will not exceed lets say 25 rows beneath the header. The data in each cell in the table is pulled via its own vlookup from another identical 'table' above it which was sourced via the filter formula from the school timetable located in a separate tab Staff TT. I require a data validation in a cell set away from these tables that will bring the correct column of data into the dropdown based on a specific header being present elsewhere which will give the week, day and lesson e.g. GM1 that I require the data to be pulled from beneath that value in the table. Is it possible to achieve this, and if so, how please? I created the vlookup table as apparently the filter will not help me to access what i require for the data validation, and since I created the vlookup table i now read that vlookup can be used to 'prepare' the data, but not in the source within the validation. so I've used a couple of ideas in empty cells elsewhere, rather than in the data validation source, to see if my formula can obtain the correct information. It works in a cell in two ways that I've found, but neither will work in data validation. The first is an xlookup, but this function cant be used in data validation so it was pointless even trying it, and the second is index and match using a colon between the parts of the formulae that connect the top row of data to the bottom row. This is the formula and the required result is shown in my screenshot, in Staff TT, cell BM27:

=INDEX('Available for cover'!A32:BH32,MATCH('Staff TT’!BM10,'Available for cover'!A32:BH32,0)):INDEX('Available for cover'!A56:BH56,MATCH('Staff TT'!BM10,'Available for cover'!A32:BH32,0))

I get an error with this formula when entered into the data validation source box despite reading that INDEX and MATCH functions can be used in data validation, and it working in a separate cell as described above. I think its something to do with the : and now I'm quite lost. I fear I'll need to unpick or remove one or more of my prep tables to move forward as I'm struggling to figure this out. Could someone please help if possible? Thank you

Originally Posted by alansidman

Hi and welcome to the forum

Pictures are of little value. Honestly, no one wants to re-type your data to try and solve your issue. Additionally, we would only be guessing at how your data was structured, ie. formulas, formatting, etc. Additionally, due to how some browsers behave, many of our members cannot see uploaded pictures/images. Please do not take this route.

Please attach a sample file that represents what you have. The structure of your attachment should be the same structure as your actual data. Any proprietary information should be changed.

Include in the attachment any code you're currently using (whether it works or not) and an "After" of what you wish the output to be.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

Thanks, will try to accomplish as soon as possible and will reply with the info requested.

Hi, I've added a sample document with all the formula that I was using previously with just a smaller sample size for the data. Hopefully this'll help. Thank you

Thanks so much, this has really helped! Clearly what I'd read prior to posting I either misunderstood or was incorrect, but this xlookup has done the trick. I applied the exact formula you gave when changing the dropdowns in cells BM3 and BMM5 and the correct dropdown unfortunately didn't appear (it was just a blank result), but playing about with your formula yielded exactly the results I needed when dragging the formula across from BM10 to BS10, and also into subsequent rows 12, 14, 16, 19 and 21 and similarly dragging them across to column BS. This is the formula that worked:

=XLOOKUP($BM$3&" "&$BM$5&" "&$BL9,'Available for cover'!$A$4:$BH$4,'Available for cover'!$A$32:$BH$52)

I'm not exactly sure why the one you gave me didn't work for everything and this one did, but either way your support has been brilliant and effective, so I can only say a heartfelt thank you!

Best wishes,

MBRJ9382