Is it arrays? pivot? both? Arrgh!

Pookah

New member
Joined
Jul 12, 2016
Messages
12
Reaction score
0
Points
0
Excel Version(s)
2016
I know there's a simple solution to this, but my mind is blocked. I have a spreadsheet with 5 cells of data. Based on these 5 cells, I generate 4 calculated values.
I have another spreadsheet with a list of 50 situations. Each situation has cells that correspond to the 5 cells of data (above). I would like to apply each of the 5 sets of data to the 1st spreadsheet and populate the remainder of the rows with the 4 associated calculated values.

It's not a normal lookup function as the 1st spreadsheet changes dynamically with the data.

A better analogy would be if I had 50 loans with different maturities, interest rates, initial amounts. I have a sheet that calculates an amortization table for the loan. I then would like to update my sheet of 50 loans with, say the principal balance, principal paid and interest paid in 2015.

I've tried a number of approaches and know it must be elegantly simple, but for the life of me I don't know what it is.

Thank you,
 
Hi

if you know there is a simple solution, then you know the solution, no?

Anyway please post a sample sheet ( no pics pse) showing your data and an example of result. Thanks
 
If I knew what it was, I wouldn't ask. I merely guess that I'm missing a relatively simple technique.

I'm new to this site, please let me know how to upload a file to this thread.
 
Here is the attachment

Attached is the example. Also, please look at cells H8 and H9. I added year values because these functions were not working. The interesting thing is the Function Arguments dialog box show the correct result, but the cell does not. Also one is #VALUE and the other is #N/A, but the syntax is the same. Same result if I make both cells identical.
 

Attachments

  • example nqdc.xls
    29 KB · Views: 13
If your problem is in cells H8 and H9 then you notice that there ARRAY formulas (or CSE formulas).
MATCH function returns the number of row. Format cells as 'General'
Respective formulas you end up with Ctrl+Shift+Enter (not just enter)
 
Thank you that worked. Previously, I had applied CSE to the elements and not the whole expression. H8 and H9 were merely bonus questions, however, the initial question still apples.
 
however, the initial question still apples.
If I understood you well, look at the attached file.
I've moved the formulas to the 'Loan' Sheet
 

Attachments

  • pookah-navic.xls
    44.5 KB · Views: 15
Navic,

You missed the point. What you did was fill in each row with the input data from the first row only.


Row 3 should show the results for a $250,000 loan at 6% for10 years
Row 4 should show the results for a $10,000 loan at 7% for3 years
etc.


See how I2 and I5 are the same? They should be different.

The idea is that I want to apply the inputs sequentially to 'Amort, to generate the results on each line as opposed to setting up a separate 'Amort table for each loan.
 
Get data ffom multiple sheets

You missed the point. What you did was fill in each row with the input data from the first row only.
I'm sorry, I can not read your thoughts. You should set the expected results.
Here's a new example, you adapt to your needs.
 

Attachments

  • pookah-navic2.xls
    79 KB · Views: 12
See attachment file, there is a new idea for a solution. There are one sheet 'Amort'.
 

Attachments

  • pookah-navic3.xls
    47 KB · Views: 9
I'll work on it, but you did what I wanted to avoid. I know I can set up a separate amort spreadsheet for each loan and relate each line to that sheet. How can I do this if I only want a single amort table and have the input applied to each loan. Array constants? Can't seem to find the way to get A's data applied, B's data applied referring only to the singular amort table. I've tried using {=Loans!$f$2:$f$7} in B! of Amort sheet and similar array contstant in the other inputs, but how do they get applied sequentially?

In other words, run Amort with 30000,120,5%,12,4/1/10 for Row 2 of Loans, then 250000,120,6%,12,6/1/12 for Row 3,etc.

So if I have 200 loans I don't have to maintain 200 Amort tables.
 
In other words, run Amort with 30000,120,5%,12,4/1/10 for Row 2 of Loans, then 250000,120,6%,12,6/1/12 for Row 3,etc.
So if I have 200 loans I don't have to maintain 200 Amort tables.
My apologies, English is not my native language.

You have a one table/range that returns the results for a particular group of conditions when you choose one client.
How to Excel return results for the second client if no source table which returned a result for the respective second client?
You need multiple tables or multiple sheets. (my opinion)

Please you!
Just type a dozen expected results (do not describe the problem).
You necessarily add an attachment file!

I will try for you to create a new solution.
 
OK, but all loans should show up on Loans sheet. I don't want to see all years for a single loan. I want to see all loans for a single year on loan page. I'll take it one step further. My idea is then to use the Loans page to drive data for a mail merge letter to report loan information for the year.
 
Do you have any other requirements, steps we should know of before we continue with this thread?
 
Choose condition and copy results to new row into another range

My idea is then to use the Loans page to drive data for a mail merge letter to report loan information for the year.

navic said:
Please you!
Just type a dozen expected results (do not describe the problem).
You necessarily add an attachment file!
Did you read this. Obviously that does not help my writing and highlighting in red font.

I do not want to read your thoughts. I can not help you if you do not tell me all the relevant information and put the expected results. No need to explain much.

But it does not matter. Let's move on.

Alright,
- You do not want to write the expected results?
- You do not want multiple sheets?
- You do not want multiple table with the results?
- You do not want see all years for single client. Ok, now is clear.

There is a possibility that for you someone write a VBA macro for all 200 cases. I believe that he will that take sometime for programming, of course, if someone wants you to write a VBA macro. (Of course, this is just my opinion, may I mistake)
I am once again tried to help you solve the problem. Sorry, I'm not VBA programmer.

You notices in the attached files following.

First: In the column 'A' must not be duplicates of clients. Because of, there used VLOOKUP function.
After you choose your client, you just click on the button VBA. (VBA code is located in Module1 caled 'CopyAndRemember')
Excel will automatically copy results of the respective client result into range 'I:M'
So, for each client you need choice and add them into the new row.

How does it work this action.
1) You need choose the client in cell 'G1'. Excel will automatically calculates all data for the chosen client on the sheet 'Amort'.
2) Next, you click on VBA button. After clicking the button VBA Excel automatically copies the relevant results into the new row in range 'I:M'.
3) If you made a mistake, just delete the last row.
4) Repeat this action for each client.

There is two VBA buttons.
1st - copying result for all clients, to another range at once (VBA macro called 'navicCopyAtOnce' related to 'CopyAndRemember')
2nd - copying result client by client, into another range to new row (VBA macro called 'CopyAndRemember')

Also, please see atached file version *4a.xlsm
There some results are placed into sheet 'Amort'

Attention!
You use *.XLS Excel file format. In the attached file is a ARRAY function IFERROR. This function is available into Excel 2007 and newer. If you use Excel 2003 then you should use the ARRAY IF/ISERROR functions.
Code:
=IFERROR(formula;"")
or
=IF(ISERROR(formula);"";formula)
Sorry, I have no idea anymore. My last solution is "pookah-naivic4a.xlsm" attached file

If you solved problem on your another way, please share attach file here on forum, then other members of forum can see solved file.
I hope that this idea will help to you in search solution. If no, good luck from me.
 

Attachments

  • pookah-navic4a.xlsm
    33.8 KB · Views: 4
  • pookah-navic4.xlsm
    30.2 KB · Views: 5
  • pookah-navic4.xls
    63 KB · Views: 8
Well, Albert never really adhered to quantum mechanics...
 
Do you have any other requirements, steps we should know of before we continue with this thread?

No, I only added the last to let responder know why what he was giving me was not what I need and to give context.
 
Navic, thank you for your help, perhaps it's the language, but you don't seem to understand the question. Can someone confirm that the only way to accomplish this is VBA? It seems like it should be some sort of array or an array combined with a pivot of some sort.

Again, for others--can I
1. take an array of inputs (loan parameters)
2. apply them to an amortization table generator
3. show the resulting array of outputs for each loan on the same page for a given year?
 
Back
Top