and replace them with cell references and formulas that calculate within each chart independently

What cell references and formulas do you want to use for the second chart?

Flyster

Solved

Posted on 2012-09-05

I want to put two of the same (attached) ammorization charts, side-by-side, in the same spreadsheet. The reason I'm doing this is to calculate and compare two completely different loans at the same time. Problem is, all the named ranges reference to the first chart when I copy a second chart next to it.

I'm thinking I can replicate this chart and put it side-by-side with the first, if I can just remove all the named ranges, and replace them with cell references and formulas that calculate within each chart independently. I just can't figure out how to do this.

Hope this makes sense. Thanks in advance!

EE.xls

I'm thinking I can replicate this chart and put it side-by-side with the first, if I can just remove all the named ranges, and replace them with cell references and formulas that calculate within each chart independently. I just can't figure out how to do this.

Hope this makes sense. Thanks in advance!

EE.xls

17 Comments

and replace them with cell references and formulas that calculate within each chart independently

What cell references and formulas do you want to use for the second chart?

Flyster

I just want two charts, exactly the same, side-by-side, calculating different loans.

The one on the "left" will reference all the loan information above its ammortization table, and the one on the "right" will reference all the loan information above

Not sure how else to explain it. If it will help, imagine the chart on the left calculating a $10,000 loan at 15.9% interest for 3 years of 12 payments per year, and the one on the right calculating a $15,000 loan at 4.9% interest for 3 years of 4 payments per year. I just want to see the two different loans side-by-side for comparison sake.

Thanks!

EE.xls

The formulas aren't working for you because they're still referencing the same named ranges. I need the named ranges replaced with actual formulas from each chart.

I'd suggest having a look here for some guidance on absolute/relative/mixed references if you aren't already comfortable with these concepts.

Also, it appears that there as some 'broken' references in your named ranges (e.g., Schedule_Monthly_Payment = #REF!$H$17). It may be that they aren't used, but worth mentioning as they should be removed if unused.

As a suggestion to help get you started, I'd suggest making an attempt to replace the named ranges in one version, and get that working. Once that's done, it should be fairly simply to copy that version, paste it alongside, then modify any absolute/mixed references as needed.

If you have specific questions as to how to translate a particular named range, feel free to ask.

=IF(IF($D$18*$D$19*$D$20*$

If you copied the entire loan area chart (Columns A-J) to columns L-U (Column K left blank), then the formula above would need to be changed to reference the new "Factors". In this case, all the "D" column references would be changed to "O" references:

=IF(IF($O$18*$O$19*$O$20*$

I understand absolute references. What I don't understand is how to change Named Ranges, because apparently the chart on the right than I copied from the left is still referencing those Named Ranges in the original chart on the left.

How do I simply copy the chart on the left so that both charts work independently, using their own unique loan figures?

Thanks. See the attached, in that the chart on the left works, and the one on the right doesnt.

EE.xls

=IF(IF($N$18*$N$19*$N$20*$

Note that the new result is using the new loan amount. All I did was replace the named ranges with the values they have "mapped" to them, which as previously indicated, gives you this formula:

=IF(IF($D$18*$D$19*$D$20*$

From there, I merely changed the absolute references from column D to column N (i.e., $D$18 becomes $N$18).

What version of Excel are you using? I'm attaching a screenshot of the Name Manager, which shows you what each named range replacement value should be (don't include the "=" sign when making replacements thought). It doesn't show the "conversion" for Values_Entered, but if you grasp the rest of them, you should be able to figure it out.

Just to clarify, one of the guiding principles behind EE is to help subscribers improve their technical skills. In the interest of this, I'm trying to provide some direction to get you started without doing the full conversion for you. I'm more than happy to help with any specific element you get stuck on, but I think you will find it more rewarding and fruitful long-term if you try to work your way through it based on what I've provided so far. Unless there is some limitation you haven't described, it really isn't hard, but it is a bit time-consuming.

i.e., "Sheet1!$C$25:$C$60" is OK to replace with "$C$25:$C$60" in this case.

I thought there would be a less time-consuming answer, than to have to change each individual Named Range manually...

In Exel 2007, you can simply do a find/replace of the named range with the formula. I don't know if that holds true for earlier versions, but maybe something to try.

1. First, unless you absolutely want to, you don't need to change the formulas in Columns A-J. They can continue to use the named ranges.

2. Next, select Cells K16-T48 (encompasses all the cells that contain formulas).

3. Then select Find & Select > Replace from the file menu.

4. In the "Find what:" field, put in one of the named range names (e.g., Beg_Bal)

5. In the "Replace with:" field, put in the range/value/formula it represents (e.g., $M$25:$M$60) (Note that you have to adjust the formulas to reflect the new ranges in the 2nd "table". I've listed a cross-reference below.)

6. Make sure that the other fields are set as shown in the attached screenshot, then choose Replace All.

There are a couple of problems:

1. The formulas reference the named ranges "Scheduled_Extra_Payments"

2. The Find/Replace will mess up some of your cell labels (e.g., Cell P20 becomes "Total $R$25:$R$60erest" because the "Int" gets replaced, but easy manual fix)

EE.xls

I'm sorry for the delay accepting this solution, and in thanking you for working everything out for me. We've had a bit of a family emergency going on.

You solved my problem, saved me a ton of time when I've needed it the most, and you've provided some great instruction that I might be able to work it out myself if ever needed again in the future.

Thanks Mark, I truly appreciate it. Tim.

Title | # Comments | Views | Activity |
---|---|---|---|

VBA double quote escaping question | 8 | 27 | |

Adding to a VBA? | 6 | 31 | |

Simple Calculation for Value of Availablity | 5 | 50 | |

Excel Averageifs | 2 | 15 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**18** Experts available now in Live!