Budget Spreadsheet with multiple functions required.

[tobym025]

Hi There, 

I am looking at formulas as per the below on my budget spreadsheet (attached) any help would be greatly appreciated. I have managed to work out a couple of them however have still noted them all below so you know how I need the sheet to work.

Type - Drop Down Box

Options

Home Loan

Business

Personal Loan

Asset Finance

Lender - Drop down box

This is dependent on what is selected above. Each option is on a table on Data table

Unpaid Paid

This is dependent on the above 2 drop downs. I need to be able to select the Type, Lender and then this will calculate the Loan Amount X the percentage rate that is next to the lender selected in the tables (Type and Lender). This is then to be calculated at 95% (This is noted in cell on budget page in cell G8)

FA Fee Received *** I have already got this cell working***

- Drop down box with yes or no as options and changes colour to green when yes and orange when no is selected

Commission Received *** i have already got this cell working***

• as above

Potential ex gst

This cell needs to calculate the FA Fee column, the Upfront Paid and/or the Upfront Amount. In regards to the upfront paid and upfront amount, if there is a figure in the upfront paid, I would like it to take it from this column and miss the Upfront Amount column. If there is nothing in the upfront paid column then it is to take the figure from upfront amount column. Minus any expenses in expenses column. Once the FA received and or Commission received column is changed to yes then this is taken from the potential column and moved to actual column.

Actual column

This cell is calculated from Upfront column and Finance Assistance Fee columns when Yes is selected in FA Received and Commission Received.

[#2]

@tobym025 

You've had 60 views of this posting--presumably a smaller number have taken the time to view that loom.com video. I'm one of the latter, and I am going to respond that (a) the size of the spreadsheet on screen is smaller than I'd want to really be able to follow, and (b) a written description would be easier to follow anyway, in no small measure because one could go back and read and re-read where something wasn't clear. Possible with the audio, but not as easy.

So let me suggest you post the spreadsheet itself, either on OneDrive or GoogleDrive with a link pasted here that grants edit access to it. And accompany your posting with a written description of what you're wanting to do but not knowing how. 

[#3]

Thank you so much for your feedback. I will do just that.

[#4]

@tobym025 

I think we need to take this in stages, now that I've seen what you've got.

Some opening questions regarding the overall design:

• please explain the relationship between the band at the top of your sheet (shown here) and the rows that follow
  • 
• Why do you have 150 numbered rows set up (plus a mysterious blank row at the bottom) before your row of totals.
• Why have a row of totals at the bottom? In general, if you intend this sheet to be a legitimate Excel Table, you'd only have rows that actually have data, and any summary totals (or averages or whatever) would be elsewhere, on a dashboard sheet or at the top or off to the side. That is, don't interfere with the structure of the Table itself.

So first off, you have the Loan Type drop down (AKA data validation) running satisfactorily.

Then we get to...

Lender - Drop down box

This is dependent on what is selected above. Each option is on a table on Data table

I've attached a spreadsheet--DataValCascading.xls-- with examples of secondary and tertiary drop downs, where first choices determine the options made visible on the next (and the next) drop downs. You obviously have a fair level of skill, so my hope is that you'll be able to discern what's happening there and see if it's readily applicable to your situation.

A challenge is going to be the sheer volume of Lenders you seem to have in each of the four categories of Type. Which makes me wonder whether those lists are realistic; if they are, that's fine, and the method I illustrate will work, it will just take you a short time to put all of those relationships (probably by cut and paste OR copy and paste) into a single dynamic table. Once done, in the manner of my example, you'll also be readily able to add new lenders as the marketplace changes.

Unpaid Paid

This is dependent on the above 2 drop downs. I need to be able to select the Type, Lender and then this will calculate the Loan Amount X the percentage rate that is next to the lender selected in the tables (Type and Lender). This is then to be calculated at 95% (This is noted in cell on budget page in cell G8)

I don't quite understand your description here, and how it applies to the spreadsheet (I see no column with the heading Unpaid/Paid, for example). However, retrieving the percentage rate is (should be) a simple matter of VLOOKUP (or the equivalent) based on the table (newly consolidated as per above) of lenders and rates. 

Anyway, why don't you take a look at DataValCascading.xls and see if you can adapt the method there to create the secondary drop down with names of Lenders based on which Type is selected.  

I've also attached a different spreadsheet, different situation, called Tax Tables 2021.xlsx. This uses some dynamic selection methods to change the table that is used to calculate taxes based on tax filing status. This is less likely to be applicable to your situation, but it might give you some ideas.

In either case, as noted up at the start, this is not a finished exchange, and I'm expecting you'll come back with questions or further clarifications as needed.