WPS Spreadsheet: Copy Values from Another Sheet with Override Logic
Question details
The user needs to populate Spreadsheet2 column C from Spreadsheet1 while using an override value whenever one exists.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Spreadsheet2 column C should retrieve the corresponding value from Spreadsheet1 column I, unless Spreadsheet1 column J contains an override.
- Observed behavior
- The desired result is for each row to display the column I value when column J is blank and the column J value when it is not blank.
Confirm that the source sheet is named Spreadsheet1 and the destination sheet is named Spreadsheet2. Check that the rows in both sheets are aligned so row 2 in one sheet corresponds to row 2 in the other.
Use an IF formula to prioritize the override column
Use this solution when column I is the default source and column J should replace it whenever an override value is present.
The formula checks whether the corresponding cell in Spreadsheet1 column J is blank. If it is blank, the formula returns the value from column I; otherwise, it returns the override from column J.
Open Spreadsheet2 and select cell C2, where the first result should appear.
Type =IF('Spreadsheet1'!J2="",'Spreadsheet1'!I2,'Spreadsheet1'!J2) into the formula bar or directly into cell C2, then press Enter.
Select C2 and drag the fill handle down column C, or copy C2 and paste it into the remaining rows. WPS Spreadsheet will adjust the row references automatically.
Check a row where Spreadsheet1 column J is blank and a row where it contains a value. The first should show column I, while the second should show the column J override.
Apply Cross-Sheet Default and Override Values in WPS Spreadsheet
WPS Spreadsheet lets you build the IF formula, reference cells on another worksheet, and fill the result down an entire column to apply default and override logic efficiently.
- 1. Open the workbook: Launch WPS Spreadsheet and open the workbook containing Spreadsheet1 and Spreadsheet2.
- 2. Enter the cross-sheet formula: In Spreadsheet2 cell C2, enter =IF('Spreadsheet1'!J2="",'Spreadsheet1'!I2,'Spreadsheet1'!J2) and press Enter.
- 3. Copy the formula through column C: Use the fill handle or copy and paste C2 into the required cells below it.
- 4. Review override results: Compare rows with blank and nonblank values in Spreadsheet1 column J to confirm the correct source value is displayed.
Frequently Asked Questions
What formula copies column I unless column J has an override?
In Spreadsheet2 cell C2, enter =IF('Spreadsheet1'!J2="",'Spreadsheet1'!I2,'Spreadsheet1'!J2). It returns Spreadsheet1 column I when J2 is blank and returns J2 when it contains a value.
How do I apply the formula to all rows in Spreadsheet2?
After entering the formula in C2, select the cell and drag its fill handle down column C. You can also copy C2 and paste it into the remaining destination cells.
Will the formula use the override when column J contains zero?
Yes. A numeric zero is not an empty cell, so the formula returns the zero from column J as the override. The default from column I is used only when J is blank.
What should I do if my worksheet names contain spaces?
Put the full worksheet name in single quotation marks, such as ='Sales Data'!I2. Update both worksheet references in the IF formula to match the exact sheet names.
