# Thread: Use 1 set of formulae for 5 cells? REVISED

1. ## Use 1 set of formulae for 5 cells? REVISED

<P ID="edit" class=small>Edited by hobkirk on 23-Feb-02 14:11.</P>Revised "Explanation" of problem

i am looking for a formula for a cell like:
CustCopy c1 = LeaseRate b2 >>>if (LeaseRate a1 = CustCopy b1)<<<

Worksheet LeaseRate calculates numerous lease payments for the "principal" in cell a1 -
a1 = input principal value
b2 = lease payment for a1 for a 60 month lease
b3 = lease payment for a1 for a 48 month lease
Note: the calculation varies according to points added, size of lease, and several other factors, so it really isn't practical to reproduce it multiple times (unless I must)

Worksheet CustCopy has 3 cells with 3 different "principals".
b1 = option 1 principal (e.g., \$5,000)
b2 = option 2 principal (e.g., \$8,000)
b3 = option 3 principal (e.g., \$11,000)

GOAL: I want the 60 mo. lease payments for the 3 amounts in column "b" in column "c"...
c1 = value b2 in LeaseRate if b1 in CustCopy (e.g., \$5,000) were plugged into a1 in LeaseRate
c2 = value b2 in LeaseRate if b2 in CustCopy (e.g., \$8,000) were plugged into a1 in LeaseRate
c1 = value b2 in LeaseRate if b3 in CustCopy (e.g., \$11K) were plugged into a1 in LeaseRate

I also want the 48 mo. lease payments for the 3 amounts in column "b" in column "d"...
d1 = value b3 in LeaseRate if b1 in CustCopy (e.g., \$5,000) were plugged into a1 in LeaseRate
d2 = value b3 in LeaseRate if b2 in CustCopy (e.g., \$8,000) were plugged into a1 in LeaseRate
d1 = value b3 in LeaseRate if b3 in CustCopy (e.g., \$11K) were plugged into a1 in LeaseRate

I hope this is expressed more coherently... so coherently that someone can understand me!

2. ## Re: Use 1 set of formulae for 5 cells?(Excel 2000)

How about a Data table Sam?

Cheers

3. ## Re: Use 1 set of formulae for 5 cells?(Excel 2000)

Note -- this reply is not really applicable now that the above post has been edited

This won't be an exact answer to your question, because I had trouble following your layout, but hopefully this will give you something to get started.

I think that you want to use the Data | Validation to select the principle from the list; however, for data validation the list must be on the same sheet as the cell that uses it. See attached worksheet for an example. If absolutely necessary, you can get around this by making a copy of your list and hiding those cells. In the attachment, I also created a dynamic length list: notice that you can add more principles, terms, and rates. I did this with dynamic named ranges. See Insert | Name | Define to see the definitions. I also protected the worksheet so that the user can only change the parameters. HTH --Sam

4. ## Re: Use 1 set of formulae for 5 cells? REVISED

Do you mean something like the attached? (uses the PMT function assuming 8% interest on the various principals over the two terms).

5. ## Re: Use 1 set of formulae for 5 cells? REVISED

I hate to disagree Sam (it's against my principles), but principal was correct as meaning an original amount!

6. ## Re: Use 1 set of formulae for 5 cells? REVISED

<img src=/S/blush.gif border=0 alt=blush width=15 height=15>Edited by Sam to change principle to principal

As Catharine says, the best way would be to create a two variable (principal & term) data table, but that would probably require a major design change. To investigate this, look at help for Data Table, Ways to forcast values....

For less changes, I have attached a workbook the just uses linked cells and absolute references to do the sort of calculations that you want to do. Obviously I am not in the leasing business, but hopefully this example will help you with your calculations. Notice that I placed all of the information that does not depend on the principal at the bottom and used absolute references (the dollar-signs in front of the row & column e.g. \$B\$8) to refer to this data. On the other hand, I placed all of the calculations that used the principal in the same row as the principal. Now if you select C1:F1, you can copy & paste these formulas into the other two rows without any changes.

You should be able to rearrange you sheet fairly quickly using drag-and-drop: after you select a cell or range, you can "grab" its border and move it anywhere else. You also may want to use the Tools | Auditing | Show Auditing toolbar, because it has a tool to draw arrows to dependent cells. Finally, you can quickly change a relative reference in a formula to absolute by clicking in the middle of the reference and pressing <F4>. HTH --Sam

7. ## Re: Use 1 set of formulae for 5 cells? REVISED

Hi, everyone. It's frustrating to find I still have not expressed myself cogently. But I really appreciate your efforts, and I will take another shot at clarification.

I simplified my original post to try and make it easier. Maybe more detail would help.

8. ## Re: Use 1 set of formulae for 5 cells? REVISED

As complicated as your spreadsheet is, I would just record a macro that copies each of the principals (as Rory observed Harvard spelling is better than Wittenberg), one-by-one, to D11 and then copy just the values back into the proper leasing columns. Is this an acceptable solution? --Sam

9. ## Re: Use 1 set of formulae for 5 cells? REVISED

Yes, I suspect a macro would work. Great idea. I try to avoid macros in Office files (primarily so email recipients I share with won't get "nervous") and I don't have much experience with them. But I think I just start the "record macro", do all my moving, then "end the macro," and save it to the spreadsheet [adding a cool button, of course).

I really thought there would be some sort of command like:

= CELL-RESULT if CELL-LEASE-CALC-INPUT were made to equal CELL-RAW-INPUT

(this would obviously make it a breeze - and it seems like this would be useful to you "industrial Excel users")

Thanks again for your efforts. I really appreciate it.

10. ## Re: Use 1 set of formulae for 5 cells? IT WORKED

The macro works great. Took about 5 minutes!
I added some BIG arrows outside the print area so I don't forget to "click the macro button" before I print. Problem solved.
Of course I still think MS should include something that works like the "formula" I wanted (hubris - bg).

Thanks again.

11. ## Re: Use 1 set of formulae for 5 cells? REVISED

Check out the attached. The columns I added to the right could be hidden or on another sheet if you like. It may not be exactly what you're looking for, but should point you in the right direction.

12. ## Re: Use 1 set of formulae for 5 cells? REVISED

A radically different answer than I was "looking for" but brilliant. Even if I had looked up "VLOOKUP" I would not have realized it did what you just illustrated. And your formulae certainly are clean.

Thanks. Have a beer (or Diet Coke) on me [but for pragmatic reasons get the money out of your wallet].
.
.
.
.....doesn't this guy understand the artistic beauty of using 27 convoluted steps to achieve a solution instead of just getting it done in one step......this is a really hard problem.......why doesn't he realize that....he probably does rubik cubes with his eyes closed......this wopr lounge is a real trip.....i hope he realizes that i really am impressed and appreciative....but of course he does, he's pretty sharp....

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•