How to attach specific dates to a specific category that is in the dropdown menu??? Help !

[NeedHelp1015]

Hello All, 

I am trying to create a worksheet of several of my accounts category review schedules and simply putting their information in one place that is easy to use and maintain. That being said, I am a newbie with excel and I need to know how to attach specific dates to a specific category that is in the dropdown menu. 

Example:

Sprouts - Category(Aromatherapy in the dropdown list) - Submission Deadline date is : 

I have attached my worksheet for a visual. 

Thank you in advance!!! 

[#2]

@NeedHelp1015 

That's a formal logic is not defined how Excel knows which date to which category to attach.

[#3 · Accepted solution]

@NeedHelp1015 

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 @Sergei Baklan 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.

[#4]

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! 

[#5]

@Riny_van_Eekelen  Also, each "Retailer" have different dates for each of the categories listed; would I create a "Categories" tab for each retailer so the dates populate properly? If so how do I link each category tab to the individual retailer? 

Example: Fresh Thyme Farmers Market "Aromatherapy" submission deadline is 4/2/20 but Sprouts Farmers Market "Aromatherapy" submission deadline is 6/10/20 . 

[#6]

@NeedHelp1015 

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.

[#7]

@NeedHelp1015 

Okay! Didn't see your latest post until after I posted my last. Let me have a look at it.

[#8]

Thank you so much, @Riny_van_Eekelen ! This is incredibly helpful. I am taking classes on excel but I need this worksheet to be functioning quicker than the classes will teach me. I appreciate all of your help and guidance! 

[#9]

@NeedHelp1015 Not sure now if every retailer has different data for every single category. Have aded a column for "Retailer" in the Category sheet. Would it be feasible to enter all the possible dates there? If not every retailer has every category you can simply leave them out.

It will be quite a job, I believe. From there, we can take the next step and set the dates for each category based on retailer.

[#10]

@Riny_van_EekelenOk, so on the categories tab I copied the information for each category and pasted it underneath so that all Retailers are listed with all the same categories. I just need the dates on the category tab to populate onto the main sheet properly. This way no matter what category I select on the main tab and on any retailer it will pull up those dates specifically pertaining to that retailer. I have attached what I have been doing. 

[#11]

@NeedHelp1015 Have tried to keep it fairly simple, by adding two grouped columns in the Accounts table. This will keep the formulae shorter and easier to maintain. Press the + sign above column Q to display them. These two columns determine the starting position of the Retailer and Category inside the Category table (=Table1). Note that the header is not counted here. For instance, Apple Wellness sits on row 2 but has position number 1 in the table.

Changed the formulae that lookup the dates to use INDEX and MATCH (in stead of VLOOKUP) and included an extra row at the top to indicate the column number of each of the dates in the Category table.

Corrected a few inconsistent Retailer Names. Note that these have to exactly the same in both tables. Otherwise the formulae will not be able to find a match.

Deleted a date block for "Marlenes Market" since you had two of them, but added a date block for "Vitamins & Such". The latter you need to fill in yourself.

Hope this works for you!