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]

but I can't get my head around how.

But in post #1 you say

I know there's a simple solution to this,

And what do you mean by "elegant"?

Right now scenarios seem to be the only approach I've found other than a macro (which I'm not allowed to use here)

At excelguru you waited 19 posts to say that VBA was not an option !

 

[shg]

Ah, but here's the rub: One worksheet generates the outputs.

I don't understand what you don't understand about this, or why you don't think it solves your problem.

Your model needs four inputs; there they are in B10:C10. What you do with them to arrive at the results can be arbitrarily complex.

 

[Dalai Pookah]

But in post #1 you say

And what do you mean by "elegant"?




At excelguru you waited 19 posts to say that VBA was not an option !

What I mean by elegant is a concise direct solution. In my example I could generate 100 amortization tables and then summarize them. That's not elegant. What shg suggested is, if I can figure out how to apply it. My alternative of using scenarios with a single amortization table is getting there, but being a multi-step process is still cumbersome. There are a number of aspects of excel that I have not used much (or at all). Arrays and scenarios are among them. Intuitively, I could imagine there's a simple straightforward way to accomplish this, but I just am not connecting the dots of functions and techniques. Maybe there's not a way to do this and my expectations are too high. That's why I turned to the forums for insight.

As to the 19 post thing--1. I never thought I had to say no VBA in my original post. I thought that if I was looking for VBA help, I would have asked for it.
2. It wasn't until post 15 that navic gave a VBA solution and my next response to him asked if there was a solution without VBA (4 intervening posts in the thread not responsive to VBA), so I don't appreciate the implication that I had people working on VBA solutions and waited a long time to mention this.

Again, I am new to this board, so forgive my lack of understanding, if, in fact, mentioning VBA was a requirement, I never saw anything to this extent on the FAQ.

 

[Dalai Pookah]

Your model needs four inputs; there they are in B10:C10. What you do with them to arrive at the results can be arbitrarily complex

I guess it's the linkage aspect I'm not following. I have a spreadsheet that calculates an amortization table given 4 inputs. I have a data table of a list of 100 sets of inputs. I can see that I can take one set of inputs and apply it to my amortization table. My disconnect is trying to apply all inputs to the amortization table. My results are not discrete formula, but are based on different aspects of the amortization schedule. I tried using array constants in the amortization table for the input fields. I couldn't find a way to use the amortization table multiple times except by applying a scenario to each data set and getting a summary table.

To summarize my ignorance, I can see how to apply particular formulae to a data table. I do not see how to make another spreadsheet dynamic with respect to this data table.

 

[shg]

One some other tab in the same workbook is your model, and four of those cells are inputs. Right so far?

In the first input cell, enter =myDataTableSheet!B10

In the next, enter =myDataTableSheet!C10

In the next, ...

 

[Dalai Pookah]

Thank you. It took me a while to figure out the ins and outs of Data Tables in 2003, but now that I have, it works like a charm.
ray:

 

[shg]

You're welcome.

 

[shg]

There's a workbook that illustrates five ways to set up data tables (1D (row input), 1D (column input), 2D (row and column input), N-way by rows, and N-way by columns) at https://app.box.com/s/s9khp18ql7j9m1hzaegnkb2wyuq35j67.