Forum Discussion

tofitofi's avatar
tofitofi
Copper Contributor
Apr 19, 2024

Auto fill cells with colors

I am looking for something specific, so I am going to try what I'm struggling with.

 

I currently want to create an auto-generating timetable for a class schedule. I have three tables, two of then in a single spreadsheet, and the 3rd is on a different one. Table 3 from spreadsheet 2 contains two columns: course titles and their representative course codes. Table 2 is where I want to input the course codes (e.g., A01, A02, etc.) which will highlight certain cells in Table 1 to represent the time. In the end, I imagine it to look something like the image below. Only, I don't know what formulas to use to connect the three tables, since conditional formatting doesn't work when I try it. 

 

 

Excel
Formulas and Functions
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Apr 22, 2024

    tofitofi 

    To achieve the desired result of automatically filling cells with colors based on course codes input in Table 2, and the corresponding course codes and colors defined in Table 3, you can use a combination of formulas and conditional formatting. Here's a step-by-step guide:

    1. Define Course Codes and Colors: In Table 3 of Spreadsheet 2, list the course codes in one column and their representative colors in the adjacent column.
    2. Input Course Codes: In Table 2, input the course codes (e.g., A01, A02, etc.) where you want to schedule classes.
    3. Set Up Conditional Formatting: In Table 1, select the range of cells where you want the colors to change based on the course codes.
    4. Create Conditional Formatting Rules: Create conditional formatting rules based on the course codes input in Table 2 and the corresponding colors defined in Table 3.
    5. Use VLOOKUP to Get Color Codes: Use the VLOOKUP function in the conditional formatting rules to fetch the corresponding color codes based on the course codes.

    Here's an example of how you can set up the formulas and conditional formatting:

    1. Define Course Codes and Colors: In Table 3 (let's say in columns A and B), list the course codes in column A and their representative colors in column B.
    2. Input Course Codes: In Table 2, input the course codes (e.g., A01, A02, etc.) in the cells where you want to schedule classes.
    3. Set Up Conditional Formatting: Select the range of cells in Table 1 where you want the colors to change based on the course codes.
    4. Create Conditional Formatting Rules: Create conditional formatting rules for each course code in Table 2.

    For example, if your course codes are in cells B2:B10 in Table 2 and you want to apply formatting to cells in Table 1 based on these codes, you can create conditional formatting rules like this:

      • Rule 1: Apply formatting if the value in Table 1 matches B2 in Table 2, and set the fill color to the corresponding color from Table 3.
      • Rule 2: Apply formatting if the value in Table 1 matches B3 in Table 2, and set the fill color to the corresponding color from Table 3.
      • Repeat this process for each course code in Table 2.

    5. Use VLOOKUP to Get Color Codes: In the conditional formatting rules, use the VLOOKUP function to fetch the corresponding color codes from Table 3 based on the course codes in Table 2.

    By following these steps, you should be able to automatically fill cells with colors based on the course codes input in Table 2 and the corresponding colors defined in Table 3.

     

    To use the VLOOKUP function in conditional formatting rules to fetch corresponding color codes from Table 3 based on the course codes in Table 2, you can follow these steps:

    1. First, ensure that Table 3 contains the course codes in one column (let's say column A) and their corresponding colors in another column (let's say column B).
    2. In Table 1, where you want the cells to be colored based on the course codes, select the range of cells where you want the conditional formatting to apply.
    3. Go to the "Home" tab in Excel, click on "Conditional Formatting" in the toolbar, and select "New Rule."
    4. In the "New Formatting Rule" dialog box, select "Use a formula to determine which cells to format."
    5. In the "Format values where this formula is true" field, enter a formula using the VLOOKUP function to fetch the corresponding color code based on the value in the current cell. Here's the general syntax of the formula:

    =VLOOKUP(cell_with_course_code, Table3_range, 2, FALSE)

    • cell_with_course_code: This should be the reference to the cell containing the course code in Table 1.
    • Table3_range: This should be the range of cells in Table 3 containing the course codes and corresponding colors.
    • 2: This specifies that you want to retrieve the value from the second column of Table 3, which contains the colors.
    • FALSE: This ensures an exact match is performed in the VLOOKUP function.

    For example, if the course codes in Table 2 start from cell B2 and you want to apply conditional formatting to cells in Table 1, and if Table 3 is in the range A1:B10, the formula for the conditional formatting rule might look like this:

    =VLOOKUP(A1, Table3!$A$1:$B$10, 2, FALSE)

    1. After entering the formula, click on the "Format" button to specify the formatting options, such as fill color.
    2. Once you've set up the formatting options, click "OK" to apply the conditional formatting rule.

    Repeat these steps for each course code you want to apply conditional formatting to. Adjust the ranges and references as necessary based on the actual layout of your tables.

    This setup will apply conditional formatting to the cells in Table 1 based on the course codes and corresponding colors defined in Table 3. The text, steps and formulas were created with the help of AI.

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

    Was the answer useful? Mark as best response and Like it!

    This will help all forum participants.

Share

Resources