The project is going well
I need help on 'MOVING' formulae and named ranges between worksheets.
But first here's how it's gone, and details of a beautiful solution to a very tricky problem:
******************
I've used dropbox lists linked to the ranges of each different stage of payment.
It's not ideal but it's workable.
Displaying a loan payment schedule from different loan options
I've also worked up six loan types that can be swapped in and out, with a dropbox list.
This is so useful for all projects and any multiple data lists.... it's beautiful!
Here's the code:
Create your loan data in columns.
Select the column of each of your loans, that you are interested in displaying in a row, and name the range.
You can see that I want to display payments from 6 different loans.
Make a column list of text names relevant to the different loan payments eg.
L12P
L18P
L24P
C12P
C18P
C24P
Select that list and name the range say loan_payment_list
Place a dropbox list at the start of the row, and select the list of names.
Paste the above code into the first cell of the row, then drag the cell as far as you want, Then press Ctrl+shift.
Boom!
Different loan payments can be swapped in at the click of the dropbox.
I perfected it by adding zeros in each loan table down to 24 to match the longest table (each range must match the longest range).
It was further perfected by adding 3 spaces between each payment, so that the payments appear every 4 weeks.
Note: this requires adjustment of the range refs.
For a payment holiday, I added 7 rows between the principal sum and the first payment.
The interest might be charged marginally different, but this is an information model, not an audited account.
Calendar vs Weeks
Calendar payments don't match the 4 week cycle required for container shipments.
It can be solved, once a start date has been established, by adding an extra row between payments, to match 5 week months.
**********************************
How To MOVE formulae between worksheets?
For ease of development, all the above work was entered into the same worksheet.
I presumed that I could simply cut certain elements and paste them to another worksheet.
After searching.... all the answers avoid this issue... only 1 answer stated it couldn't be done, as the links would break.
Apparently, the ranges should have referred to the worksheet name.
I naively presumed that the statement 'scope = workbook' meant that it could be moved anywhere in the workbook.
Alas... no.
I need to move the formulae to allow adding and subtracting cells above.
I can move the data section to a distant part of the worksheet
However, it should ideally be immediately accessible, and a worksheet called 'loans' would be ideal.
Here is a range ref
=Model!$A$150:$A$155
Model is the worksheet name.
I guess if I moved these ranges, all my formulas would have to be changed, with Model! added to any cell reference.
Is there not an easy way of doing this, so that everything is updated?
Perhaps it would be easier to setup 'GoTo' links and keep the calcs on the same spreadsheet
Bookmarks