Forum Discussion

Minghao Liao's avatar
Minghao Liao
Copper Contributor
May 21, 2018
Solved

Trying to make my Stock Spreadsheet work

I am trying to make my stock workbook by having 2 sheets. One where I enter transactions and one where I can see a stock summary.    I will enter information in my sheet "Transactions(SG)" and I wo...
Formulas & Functions
Need Help
  • Matt Mickle's avatar
    Matt Mickle
    May 21, 2018

    Try updating your formulas to these:

     

    G2: {=IFERROR(INDEX('Transactions(SG)'!$J$2:$J$24,SUMPRODUCT(MAX(('Transactions(SG)'!$C$2:$C$24=A2)*ROW('Transactions(SG)'!$C$2:$C$24)))-ROW('Transactions(SG)'!$J$2:$J$24)+1),"No Transactions")}

     

    H2: {=IFERROR(INDEX('Transactions(SG)'!$O$2:$O$24,SUMPRODUCT(MAX(('Transactions(SG)'!$C$2:$C$24=A2)*ROW('Transactions(SG)'!$C$2:$C$24)))-ROW('Transactions(SG)'!$O$2:$O$24)+1),"No Transactions")}

     

    Please see attached file for additional reference.  You may need to change the "No Transactions" portion of the formula to 0 to avoid error messages.  This will happen if there is no transaction associated with the symbol.

Minghao Liao's avatar
Minghao Liao
Copper Contributor
May 22, 2018

Could you explain the 

1/(A2='Transactions(SG)'!$C$2:$C$24)*'Transactions(SG)'!$A$2:$A$24 part of the formula? )

 

Matt Mickle's avatar
Matt Mickle
Bronze Contributor
May 22, 2018

This is used to extract the max transaction date for X company (Stock Summary(SG)!A2).  It looks at all of the values that match x company along side with the date and then returns the max date of this subset from the transactions worksheet.  Which is then used in the Index / Match portion of the formula.

Resources