# Thread: Is it arrays? pivot? both? Arrgh!

1. ## Is it arrays? pivot? both? Arrgh!

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,

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

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

4. ## 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.

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

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

7. Originally Posted by Pookah
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

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

9. ## Get data ffom multiple sheets

Originally Posted by Pookah
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.

10. See attachment file, there is a new idea for a solution. There are one sheet 'Amort'.

Page 1 of 3 1 2 3 Last

#### Posting Permissions

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