How to Auto-Populate Excel Rows Based on a Yes or No Selection
Question details
The user needs to automatically populate a row in an updated table with data from an old table when 'No' is selected from a dropdown. If 'Yes' is selected, the row should remain blank and available for manual data entry.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Managing an updated dataset where rows selectively pull historical data or require new manual input based on a conditional Yes/No dropdown.
- Observed behavior
- The user requires a formula or VBA macro that leverages a shared index between two tables to conditionally fetch data or leave cells editable depending on the specific Yes/No value.
Ensure that both your old data sheet and the new updated sheet contain a shared unique identifier (such as an ID number or index code) in a specific column so Excel can accurately match and retrieve the correct records.
Use a Combination of IF and VLOOKUP Formulas
This method is best for simple setups where you want to fetch data instantly without enabling macros. It uses an IF function to check the Yes/No condition and a VLOOKUP to pull the data.
Formulas are the easiest way to conditionally retrieve data. Keep in mind that if a cell contains a formula to pull old data, typing new data into that cell (when the condition is 'Yes') will overwrite and delete the formula.
Select the cell where you want the Yes/No option. Go to the 'Data' tab, click 'Data Validation', choose 'List' under the Allow criteria, and type 'Yes,No' in the Source box.
In the cell where you want the populated data, enter a formula like: =IF(B2="No", VLOOKUP(A2, OldSheet!A:D, 2, FALSE), ""). In this example, B2 is the Yes/No dropdown, A2 is the shared index, and OldSheet!A:D is your old data table.
Hover over the bottom-right corner of the cell with the formula until the fill handle appears. Click and drag it down to apply the formula to the rest of the rows in your updated table.
Implement a VBA Macro for Persistent Editable Rows
If you need the flexibility to manually type new data when 'Yes' is selected without permanently destroying a formula, using a Worksheet Change VBA macro is the most robust solution.
Easily Manage Conditional Data with WPS Spreadsheet
WPS Spreadsheet fully supports advanced logical formulas like IF, VLOOKUP, and XLOOKUP, as well as VBA macros. You can effortlessly set up automated data retrieval and dropdown lists without needing expensive software.
- 1. Open Your Data Tables: Launch WPS Spreadsheet and open your workbook containing both the old data sheet and the updated table.
- 2. Create a Dropdown List: Highlight your condition column, navigate to the 'Data' tab, and click 'Validation'. Select 'List' and input 'Yes,No' to create your interactive dropdowns.
- 3. Apply Conditional Formulas: Use the formula bar to combine IF and VLOOKUP functions to automatically pull historical data when 'No' is selected, leaving cells blank for new entries when 'Yes' is chosen.
Frequently Asked Questions
Can I use XLOOKUP instead of VLOOKUP for auto-populating rows?
Yes, XLOOKUP is often preferred over VLOOKUP because it allows you to search in any direction and handles errors more gracefully. You can write your formula as =IF(B2="No", XLOOKUP(A2, OldSheet!A:A, OldSheet!C:C), "").
Why does typing in the 'Yes' row erase my formula?
In spreadsheet applications, a cell can contain either a formula or a hardcoded value, but not both. When you manually type data into a cell that was displaying blank via an IF formula, the new text overwrites the formula. If this is a problem for your workflow, you should use a VBA macro instead.
How do I make the formula return multiple columns at once?
If you are using a modern spreadsheet version that supports dynamic arrays, you can use XLOOKUP or VLOOKUP with an array constant for the column index (e.g., {2,3,4}) to spill the results across multiple adjacent columns automatically.
