Forum Discussion
How to attach specific dates to a specific category that is in the dropdown menu??? Help !
I notice that you have all the items for your dropdown list written in the Source-field of the Conditional Formatting dialogue box. It's better to create a list of all items in a separate sheet and then create a reference to that list in the Source-field. And then you can add the logic that SergeiBaklan was aiming at. You need to determine the date to be attached to each Category yourself. Once that has been accomplished, it's easy to fill in the date, based upon the selected Category.
I've attached a revised version of your sheet to demonstrate the principle. It uses Structured Tables and the VLOOKUP function to find the dates. In case your Excel version recognises the new XLOOKUP function, you could try the following formula in column K of the first sheet. It will produce the same result, but with more flexibility.
=XLOOKUP([@Category],Table1[Category],Table1[Submission Date],"",0)If this is not the kind of logic you had in mind, please clarify your intentions.
Thank you Riny_van_Eekelen ! This is exactly what I needed. I have added a few more of the columns that need to have the date populated on the first sheet but I am not sure how to get those dates to roll over properly. Please see revised excel sheet.
Thank you again!
The VLOOKUP function call for several arguments.
1) what to look up (cell)
2) where to look up (range)
3) which column in the range to return
4) look for closest match (1 or TRUE) or exact match (0 or FALSE)
In cell I2, you want to return the date in the 2nd column from the Category listing, in J2 it would be the 3rd and so on. I changed it for you in the attached sheet, but please read the help screens on VLOOKUP to fully understand the principle. You can automate this part, but it would require incorporating MATCH in the formula.
I2: =IFERROR(VLOOKUP([@Category],Table1,2,0),"")
J2: =IFERROR(VLOOKUP([@Category],Table1,3,0),"")
K2: =IFERROR(VLOOKUP([@Category],Table1,4,0),"")
etc.