How to Update Cell Text and Color from the Latest Drop-down Selection in Excel
Question details
The user needs a target cell to dynamically display the most recent text selection from a row of drop-down lists and automatically update its background color based on that specific text value.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking the most recent status or event in a spreadsheet row where multiple drop-down cells record successive updates over time.
- Observed behavior
- The target cell needs to reflect the last non-blank text value entered in the designated range and apply corresponding conditional formatting rules automatically.
Ensure that your row of drop-down lists contains text values rather than numeric values, as the standard REPT lookup formula is specifically designed to retrieve the last non-blank text string.
Use LOOKUP Function and Conditional Formatting
Extract the last text entry in a row using the LOOKUP function and apply color rules to the target cell using conditional formatting.
The LOOKUP function combined with REPT can be used to scan an entire row and find the last inputted text value. Once the text is automatically retrieved into your target cell, conditional formatting rules can automatically adjust the cell's background color based on its exact text content.
Select the target cell (e.g., E12) and enter the formula =LOOKUP(REPT("z",255),F12:Z12). This formula searches the range F12:Z12 from left to right and returns the last non-blank text entry.
Keep the target cell selected, navigate to the Home tab on the ribbon, click on Conditional Formatting, and select 'New Rule'.
Choose 'Format only cells that contain', set the condition dropdowns to 'Specific Text' and 'containing', then type the exact text from your drop-down list (e.g., '1st Message'). Click the Format button to choose your desired fill color.
Repeat the conditional formatting process to add new rules for each possible drop-down selection (such as '2nd Follow-up Sent'), assigning a unique color to each status.
Easily Manage Formulas and Formatting with WPS Spreadsheet
WPS Office fully supports advanced lookup formulas and complex conditional formatting. You can easily build dynamic trackers and colorful status boards without any complicated setups.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your existing status tracking workbook or create a new one.
- 2. Apply the LOOKUP Formula: Select your target status cell and type =LOOKUP(REPT("z",255), F12:Z12) to dynamically pull the latest list selection.
- 3. Add Conditional Formatting: Go to the Home tab, click Conditional Formatting > Highlight Cells Rules > Text that Contains, and define your color codes for each status.
Frequently Asked Questions
Why does the LOOKUP formula return an #N/A error?
This error occurs if there are no text values at all in the specified lookup range. Ensure that at least one cell in your drop-down range contains a text selection to resolve the error.
Can I use this formula to find the last number instead of text?
For numeric values, the text-based REPT formula will not work. Instead, you need to use =LOOKUP(9.99999999999999E+307, F12:Z12) to successfully find and return the last numeric entry in the row.
How do I copy these formatting rules to the rest of my column?
You can use the Format Painter tool. Select the cell with the completed formula and conditional formatting rules, click Format Painter on the Home tab, and drag it down over the other cells in your target column to apply the same logic.
