'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Copy Names When Adjacent Cells Contain Yes in Excel
Question details
The user needs an Excel formula to automatically aggregate and display names from multiple worksheets into a summary sheet if the adjacent status cell contains 'Yes'.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Consolidating on-call rosters or status lists from several different worksheets into one master summary sheet based on a specific condition.
- Observed behavior
- The user wants the summary worksheet to update dynamically in real-time as individual office sheets are modified.
Ensure you are using a modern version of Excel that supports dynamic array functions like FILTER and VSTACK (such as Microsoft 365 or Excel 2021) for this solution to work seamlessly.
Use FILTER and VSTACK Dynamic Array Formula
Combine multiple sheet ranges using VSTACK and filter the results using the FILTER function based on the 'Yes' criteria.
This method utilizes advanced dynamic array functions to stack the data from multiple tabs into a single virtual array. It then filters that array to only output rows where the adjacent cell value is exactly 'Yes'. Since it is dynamic, any updates made on the individual office sheets will instantly reflect on the master summary sheet.
Click on the top-left cell in your master summary worksheet where you want the compiled list of names to begin displaying.
Type the formula: =FILTER(VSTACK(Sheet2:Sheet5!A2:B10), CHOOSECOLS(VSTACK(Sheet2:Sheet5!A2:B10), 2)="Yes"). If your sheets are named differently, replace 'Sheet2:Sheet5' with your actual sheet names, and adjust 'A2:B10' to match your data range.
Press Enter. The formula will automatically 'spill' the filtered names and their corresponding 'Yes' values into the adjacent cells.
Easily Manage Data Across Multiple Sheets with WPS Spreadsheet
WPS Spreadsheet natively supports advanced data consolidation features and dynamic array formulas, allowing you to seamlessly pull data based on specific criteria like 'Yes' across multiple tabs.
- 1. Open Your File: Launch WPS Spreadsheet and open the workbook containing your office rosters.
- 2. Select the Summary Area: Click on the starting cell in your summary sheet where the filtered names should appear.
- 3. Apply the Formula: Input your preferred dynamic formula, utilizing FILTER to look up the 'Yes' criteria.
- 4. Review Dynamic Results: Press Enter to instantly display all matching names. Data will update automatically as changes are made.
Frequently Asked Questions
Why does my FILTER formula return a #CALC! error?
This error typically occurs if no cells in your specified range meet the 'Yes' criteria. You can fix this by adding an 'if_empty' argument to the end of the FILTER function, like =FILTER(range, criteria, "No one on call").
Can I use this formula on older versions of Excel like Excel 2016 or 2019?
No, functions like VSTACK, CHOOSECOLS, and FILTER are dynamic array functions introduced in newer versions (Microsoft 365, Excel 2021). For older versions, you would need to use complex INDEX/MATCH array combinations, Power Query, or VBA macros.
How do I exclude the 'Yes' column from the final results so only names appear?
If you only want to return the names (assuming they are in the first column), wrap your primary VSTACK data range inside the CHOOSECOLS function to only output column 1. The formula would start like this: =FILTER(CHOOSECOLS(VSTACK(Sheet2:Sheet5!A2:B10), 1), ...).
Is the formula case-sensitive when looking for 'Yes'?
Standard logical tests in Excel formulas (like ="Yes") are not case-sensitive. It will successfully identify 'Yes', 'yes', or 'YES' in the adjacent cells.
