How to Automatically Populate a Parts List from Another Excel Sheet
Question details
The user needs to automatically pull new facility IDs from a source sheet into a Partslist sheet using a dynamic formula.
- Product
- Microsoft Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Updating a master parts list dynamically when new data is added to a source sheet without manual data entry.
- Observed behavior
- The goal is to maintain a dynamic list that automatically reflects unique, non-blank facility IDs from the source data as it is refreshed.
Ensure both your source sheet and destination sheet are in the same workbook, and identify the exact column ranges containing your source data to prevent slow calculation speeds.
Use the UNIQUE and FILTER Functions
The easiest and most modern way to pull a dynamic list of unique items, available in Microsoft 365 and newer spreadsheet software.
This method uses dynamic arrays that automatically spill into neighboring cells. It is the most efficient way to extract unique, non-blank values without heavy calculation times.
Open your destination sheet (e.g., Partslist) and select the first cell where you want the populated data to appear.
Type the formula: =UNIQUE(FILTER(Facility!A2:A10000,Facility!A2:A10000<>"")) and press Enter.
The formula will automatically spill down the column, displaying all unique, non-blank facility IDs from the source sheet without needing to drag the fill handle.
Use an INDEX and MATCH Array Formula
A traditional array formula approach for older versions of Excel that do not support dynamic array functions like UNIQUE.
Use WPS Spreadsheet to Handle Dynamic Formulas
WPS Spreadsheet fully supports advanced array functions, allowing you to easily manage dynamic data lists across multiple sheets without expensive subscriptions.
- 1. Download and Install: Get WPS Office for free from the official website and install it on your device.
- 2. Open Your Workbook: Launch WPS Spreadsheet and open your existing Excel workbook containing the facility data.
- 3. Apply the Formula: Select your destination cell and type =UNIQUE(FILTER(Facility!A2:A10000,Facility!A2:A10000<>"")) to instantly generate the dynamic list.
Frequently Asked Questions
Why is my INDEX and MATCH formula returning a #VALUE! error?
In older versions of Excel, this is an array formula. You must press Ctrl+Shift+Enter instead of just Enter to apply it correctly. Curly brackets {} should appear around the formula when done right.
Can I automatically sort the populated parts list alphabetically?
Yes. If you are using the UNIQUE function, you can simply wrap it in the SORT function like this: =SORT(UNIQUE(FILTER(Facility!A2:A10000,Facility!A2:A10000<>""))).
Will using these formulas slow down my spreadsheet?
Using entire column references (like A:A) in traditional array formulas such as INDEX/MATCH can significantly slow down performance. It is always recommended to use specific ranges (like A2:A10000) instead.
Does WPS Office support dynamic array formulas like UNIQUE?
Yes, modern versions of WPS Spreadsheet fully support dynamic array formulas, making it easy to create automatically updating lists without manual workarounds or complex legacy formulas.
