Page 2 of 3 FirstFirst 1 2 3 LastLast
Results 11 to 20 of 24

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

  1. #11
    Seeker Pookah's Avatar
    Join Date
    Jul 2016
    Posts
    12
    Articles
    0
    Excel Version
    2016


    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

  2. #12
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    885
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by Pookah View Post
    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.
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  3. #13
    Seeker Pookah's Avatar
    Join Date
    Jul 2016
    Posts
    12
    Articles
    0
    Excel Version
    2016
    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.

  4. #14
    Administrator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,656
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Do you have any other requirements, steps we should know of before we continue with this thread?
    Thank you Ken for this secure forum.

  5. #15
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    885
    Articles
    0
    Excel Version
    Excel 2013

    Choose condition and copy results to new row into another range

    Quote Originally Posted by Pookah View Post
    My idea is then to use the Loans page to drive data for a mail merge letter to report loan information for the year.
    Quote Originally Posted by navic
    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.
    Attached Files Attached Files
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  6. #16
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    767
    Articles
    0
    Excel Version
    Excel 2010 64bit
    Isn't Albert Einstein credited with saying something about doing the same thing over and expecting different results ?

  7. #17
    Administrator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,656
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Well, Albert never really adhered to quantum mechanics...
    Thank you Ken for this secure forum.

  8. #18
    Seeker Pookah's Avatar
    Join Date
    Jul 2016
    Posts
    12
    Articles
    0
    Excel Version
    2016

  9. #19
    Seeker Pookah's Avatar
    Join Date
    Jul 2016
    Posts
    12
    Articles
    0
    Excel Version
    2016
    Quote Originally Posted by Pecoflyer View Post
    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.

  10. #20
    Seeker Pookah's Avatar
    Join Date
    Jul 2016
    Posts
    12
    Articles
    0
    Excel Version
    2016
    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?

Page 2 of 3 FirstFirst 1 2 3 LastLast

Posting Permissions

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