How to Build an Excel Budget with Dependent Drop-Downs and Loan Formulas in WPS Office
Question details
The user wants to structure a budget workbook that uses dependent loan-type and lender drop-down lists, lender-specific rates, loan calculations, and potential and actual payment amounts.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Creating or updating a budget workbook that needs interactive loan selection and payment calculations.
- Observed behavior
- The budget workbook needs to show the correct lender list and rate after a loan type is selected, then calculate loan, potential payment, and actual payment amounts.
Open WPS Office Spreadsheets and prepare a lender data table with LoanType, Lender, and Rate columns. If you plan to use FILTER and XLOOKUP, make sure your WPS Office version supports dynamic array functions.
Create the Lender Table and Loan Type Drop-Down
Use this first to store lender rates in a structured Excel Table and create the initial loan-type list.
A dedicated lender table makes dependent drop-downs and lookups more reliable than scattered rate cells.
Open WPS Office Spreadsheets, create a sheet named LenderData, and enter the headers LoanType, Lender, and Rate in A1:C1.
Select the data range, click Insert > Table, check the My table has headers option, and name the table LenderTable in the Table Tools area.
On the budget sheet, select the loan type cell, click Data > Validation, choose Allow: List, and enter the available loan types or select a range that contains them.
Make the Lender Drop-Down Dependent on the Selected Loan Type
Use this after the loan type cell exists; it narrows the lender list to the chosen loan type.
Retrieve the Lender Rate and Calculate Loan and Payment Amounts
Use this to pull the lender-specific rate and calculate loan amounts, potential payments, and actual payments.
Build a WPS Spreadsheet Budget with Dependent Drop-Downs and Loan Formulas
Use WPS Office Spreadsheets to create a structured lender table, dependent loan-type and lender lists, and XLOOKUP-based loan and payment calculations in a familiar Excel-compatible workbook.
- 1. Open a budget workbook: Open WPS Office Spreadsheets and start a new workbook or open an existing Excel budget file.
- 2. Create the lender table: Create a lender table with LoanType, Lender, and Rate columns and convert it to a named Excel Table.
- 3. Add dependent drop-downs: Add Data Validation lists for loan type and lender, using FILTER for the dependent lender list.
- 4. Add loan formulas: Use XLOOKUP to retrieve the lender rate, then add loan and payment formulas in the budget cells.
- 5. Save in Excel format: Save the workbook as .xlsx to keep it compatible with Microsoft Excel.
Frequently Asked Questions
Can I create a dependent lender drop-down in WPS Office?
Yes. Select the lender cell, click Data > Validation, choose List, and use a formula such as =FILTER(LenderTable[Lender],LenderTable[LoanType]=$B$2). If FILTER is not supported, build a helper list and refer to that range.
How do I structure the lender data for dependent lists?
Use a proper Excel Table with columns named LoanType, Lender, and Rate. Put loan types in one column, lender names in the next, and the rate for each lender in the rate column.
How do I get the rate for the selected loan type and lender?
Use XLOOKUP with FILTER, for example =XLOOKUP($B$3,FILTER(LenderTable[Lender],LenderTable[LoanType]=$B$2),FILTER(LenderTable[Rate],LenderTable[LoanType]=$B$2),0).
How should I keep transaction records and totals?
Store transaction records in an Excel Table with only active data rows. Place totals and dashboard calculations outside that table so the data range stays clean.
Why do potential and actual payment formulas need workbook column details?
Because those amounts depend on which columns track fees, expenses, assistance, and commissions, as well as the rules for when payments are recognized.
