How to Fill a Range of Excel Cells Based on Name and Quantity
Question details
The user wants to automatically repeat a specific text string or name down a column for a set number of rows, dictated by a numeric quantity value located in another cell.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating dynamic lists or repeating data entry where the number of sequential repetitions is controlled by a reference cell.
- Observed behavior
- Needs a logical formula or method to output the name from a designated cell sequentially down the rows until the row count matches the numeric value in the quantity cell.
Ensure that the cell containing your target name and the cell containing the quantity are properly referenced, and that your target output range has enough empty rows below it to accommodate the maximum expected quantity without overwriting existing data.
Use the IF and ROW Functions to Repeat Names
Use a dynamic logical formula to check the current row number against the target quantity and output the corresponding name.
The most straightforward way to fill rows dynamically based on a specific quantity is by combining the IF function with the ROW function. The ROW function determines your current position, while the IF function evaluates whether that position exceeds your specified limit.
Click on the cell where you want the first name to appear (for example, cell B1).
Type the formula =IF(ROW()<=A$2,A$1,"") into the formula bar. In this formula, A$1 contains the name you want to repeat, and A$2 contains the target quantity. The dollar sign ($) locks the row references so they do not shift when copied.
Press Enter to apply the formula. If the current row number is less than or equal to the quantity in A2, it will display the target name.
Click the small green fill handle at the bottom-right corner of cell B1 and drag it down through the required range. The formula will populate the cells with the name until the specified quantity is reached, leaving subsequent cells blank.
Easily Manage Dynamic Data with WPS Spreadsheet
WPS Spreadsheet provides powerful formula support, including fully functioning IF and ROW functions, allowing you to seamlessly repeat data based on specified quantities. It is a highly capable tool designed to streamline your daily calculations.
- 1. Launch WPS Spreadsheet: Open WPS Office on your computer and select Spreadsheet to create a new workbook or open an existing file.
- 2. Enter your reference data: Input your target name into cell A1 and your desired quantity into cell A2.
- 3. Input the formula: Click on cell B1, type =IF(ROW()<=A$2,A$1,"") in the formula bar, and press Enter.
- 4. Drag to fill: Hover over the bottom-right corner of cell B1 until the cursor turns into a cross, then click and drag down to fill the column dynamically.
Frequently Asked Questions
What if my data doesn't start in row 1?
If you start your output in row 2 (e.g., cell B2), you need to adjust the ROW() function by subtracting the offset so the count starts at 1. For example, use =IF(ROW()-1<=A$2,A$1,""). If you start in row 5, subtract 4: =IF(ROW()-4<=A$2,A$1,"").
Why is the formula returning a blank cell?
The formula intentionally returns a blank cell ("") when the current row number exceeds the numeric quantity specified in the reference cell (A2). Check if your quantity reference is correct, and ensure the row is locked with a dollar sign (A$2).
Can I repeat multiple different names based on different quantities?
Yes, but it requires a more advanced setup. You can place different names in a column and their respective quantities next to them, then use Power Query, VBA, or dynamic array formulas like REDUCE and VSTACK (in newer spreadsheet versions) to expand the entire list automatically.
Is this formula compatible with WPS Spreadsheet?
Yes, WPS Spreadsheet fully supports all standard logical and lookup functions, including IF and ROW. You can use the exact same formula in WPS Spreadsheet with perfect compatibility.
