Is it arrays? pivot? both? Arrgh!

[Dalai Pookah]

I know there's a simple solution to this, but my mind is blocked. I have a spreadsheet with 5 cells of data in a row. 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,

 

[arthurbr]

Cross posted without links.
Please add links to same posts on other forums.
Please read Excelguru Help Site - A message to forum cross posters to understand why
Thx

 

[Dalai Pookah]

Thank you. I also posted on Is it arrays? pivot? both? Arrgh!. I was getting responses that didn't address my issue. Then, I found this site, which I think is more on point and more robust.

 

[Dalai Pookah]

A workaround would be to use scenarios to change the loan variables using formulas like =INDEX(Loans!$d$2:$d$7,INDIRECT($D$1)), where d2:d7 were the various principal values and $D$1 held Loans!d2 and could be changed to represent each loan. The same formula would be used for each of the other loan variables. If I set up a scenario for each loan (setting $D$1 equal to each loan's line) and generated a Scenario Pivot Table with the values I wanted for each loan.

In this case, I'd have to set up a scenario for each loan and then run them all in a pivot table. Is there a direct way to generate the pivot table without multiple scenarios? Or to use an array in the scenario run all instances?

This is new ground for me.

 

[shg]

You could use a 1D data table where the (row or column) input value selects a row of input variables from a table.

 

[Dalai Pookah]

Can I do this if each loan has 4 or 5 variables? Doesn't seem to work on the version I'm using. (2003)

 

[shg]

The input variable is a selector for all of the other variables. You can have as many as you want.

Here's a trivial example with four inputs. Note that the answers in the data table are the same as those in col F.

	A​	B​	C​	D​	E​	F​	G​	H​
1​	Input Set​	a​	b​	c​	d​	​	​	​
2​	1​	2​	5​	1​	4​	46​		F2: =SUMSQ(B2:E2)
3​	2​	2​	4​	3​	5​	54​
4​	3​	4​	4​	1​	5​	58​
5​	4​	5​	2​	4​	2​	49​
6​	5​	5​	1​	1​	4​	43​
7​	6​	5​	1​	2​	1​	31​
8​
9​	Select​
10​	1​	2​	5​	1​	4​			B10:E10: {=INDEX($B$2:$E$7, A10, 0)}
11​					Model​
12​					46​
13​								E12: =SUMSQ(B10:E10)
14​
15​	Select​	46​						B15: =E12
16​	1​	46​						B16:B21: {=TABLE(,A10)}
17​	2​	54​
18​	3​	58​
19​	4​	49​
20​	5​	43​
21​	6​	31​

 

[Dalai Pookah]

I get this. The issue in this analysis is that each input set is applied as input to another spreadsheet, which analyzes the inputs and generates 4-5 outputs. These are the outputs I want to apply in my result table. None of these can be expressed as a single equation. This is why I took the scenario approach. If there is a way to apply the date table approach, then I'm all for it.

 

[shg]

The model can be as complex as you like; it can use 100 worksheets to calculate the results. E12 would typically refer to one of the outputs on another worksheet.

 

[Dalai Pookah]

Ah, but here's the rub: One worksheet generates the outputs. What I'm trying to avoid is the 100 spreadsheets. This is not my exact situation, but it's analogous.
If I have 50 loans, the terms are on SS1. SS2 generates the amortization schedule for a loan. Let's say I have 3 result fields on SS2, say principal balance, int paid and principal paid in a given year. I know I could set up 50 amortization tables and generate the results and have them input on SS1 so I could use SS1 to run a mail merge report to each borrower.

I'm looking for a more elegant solution where I can accomplish it using only 1 SS2. Right now scenarios seem to be the only approach I've found other than a macro (which I'm not allowed to use here). If I set up a scenario for each loan, then use the pivot summary, I can generate the results. Anything more elegant? If I could somehow harness data tables to do this, I would, but I can't get my head around how.