?
Solved

Excel: Replace all the Named Ranges in a Spreadsheet

Posted on 2012-09-05
17
Medium Priority
?
438 Views
Last Modified: 2012-09-11
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
0
Comment
Question by:Tim Jackoboice
  • 9
  • 6
  • 2
17 Comments
 
LVL 22

Expert Comment

by:Flyster
ID: 38370986
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
0
 

Author Comment

by:Tim Jackoboice
ID: 38371009
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 it's ammortization table.

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!
0
 
LVL 22

Expert Comment

by:Flyster
ID: 38371116
Here's one way to do it. Copy your formulas from the first sheet to another sheet. Then reference the second chart to the new sheet. Attached is a sample, but your formulas were not working for me.
EE.xls
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:Tim Jackoboice
ID: 38371166
I don't want them on two seperate sheets/tabs -- I need them side-by-side  on the same worksheet.
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.
0
 
LVL 10

Expert Comment

by:mark_harris231
ID: 38372223
@Cactus - what specifically are you having problems with?  Converting the formulas to use cell references from named ranges is relatively straight-forward, if a bit tedious.  The only real issue you may struggle with would be the use of absolute, relative and mixed references (e.g., 'Loan Amount' will need to be an absolute reference in each "version").

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.
0
 
LVL 10

Expert Comment

by:mark_harris231
ID: 38372281
As an example, the conversion of the formula in Cell H16 would be:

=IF(IF($D$18*$D$19*$D$20*$D$22>0,1,0),-PMT($D$19/$D$21,$D$20*$D$21,$D$18),"")

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*$O$22>0,1,0),-PMT($O$19/$O$21,$O$20*$O$21,$O$18),"")
0
 

Author Comment

by:Tim Jackoboice
ID: 38373870
Should've done this from the beginning. Attached is an example of exactly what I need -- two ammoritization tables, side by side, on the same spreadsheet, to calculate and compare two different loans simulataneously.

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
0
 
LVL 10

Expert Comment

by:mark_harris231
ID: 38373952
@Cactus - replace formula in Cell R16 with the following:

=IF(IF($N$18*$N$19*$N$20*$N$22>0,1,0),-PMT($N$19/$N$21,$N$20*$N$21,$N$18),"")

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*$D$22>0,1,0),-PMT($D$19/$D$21,$D$20*$D$21,$D$18),"")

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.

Name Manager Screenshot
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.
0
 
LVL 10

Expert Comment

by:mark_harris231
ID: 38373985
Note: It doesn't hurt to leave them in, but you can omit the "Sheet1!" reference when replacing the named ranges since you only have one sheet.

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

Author Comment

by:Tim Jackoboice
ID: 38374325
Thanks. But ugh...
I thought there would be a less time-consuming answer, than to have to change each individual Named Range manually...
0
 
LVL 10

Expert Comment

by:mark_harris231
ID: 38374361
Sorry, but no way that I know of.  However,  I think you can probably copy down the formulas in the table area from the 2nd row down.  The first row has a slightly different "initial" formula.
0
 
LVL 10

Expert Comment

by:mark_harris231
ID: 38377328
Cactus - potentially good news

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.
0
 

Author Comment

by:Tim Jackoboice
ID: 38377361
Hey Mark. I am using Excel 2007 ... do you know exactly how I would go about doing this?

Thanks.
0
 
LVL 10

Expert Comment

by:mark_harris231
ID: 38377787
Sure...

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).

IMPORTANT make sure you select these cells before doing the replace.  Otherwise, you'll replace the references in both tables.

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.

Replace Settings
 Cross-reference
0
 
LVL 10

Accepted Solution

by:
mark_harris231 earned 2000 total points
ID: 38377927
Attached is your revised file.  It took less time to do the replace than it did to document the previous post.  ;)

There are a couple of problems:

1. The formulas reference the named ranges "Scheduled_Extra_Payments" and "Extra_Pay".  Replacing these with the ranges they represent causes formula errors because there are no values.  Since there is no label for these cells ($D$23 and $N$23 in table 1 and 2 respectively), I'm not sure if these reference can just be deleted from the formula (?).  This is not fixed in the attached file.
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
0
 

Author Closing Comment

by:Tim Jackoboice
ID: 38388822
@mark_harris:

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.
0
 
LVL 10

Expert Comment

by:mark_harris231
ID: 38388845
No worries at all, Tim.  Hope all is better(?) with the family.

Take care.

Mark
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

864 members asked questions and received personalized solutions in the past 7 days.

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

Join & Ask a Question