I'm using Excel 2019 (as I don't have office 365 subscription) and I've attached excel sheet at https://ufile.io/5xlkh1v3 which contains Data and Graph sheets. Data sheet contains stock tickers, quantity, date acquired, etc and Graph sheet contains array formula in column A which finds unique stocks in particular hard coded account (say eTrade from Data sheet). Similarly, column B contains formula to calculate cost for those tickers from Data sheet. Everything is working as expected in the sheet except that when I insert row in the middle of data sheet, the populated values from column A in sheet Graph disappears as the hardcoded cell reference changes.
Can someone help in making this complex array formula in column A from sheet Graph dynamically change so that its populated values will retain?
Thanks!
CodePudding user response:
It would be better to post the formulas you used and ask why they cause trouble.
The following formula in Graph!A2
causes the error:
=IFERROR(INDEX(Data!A:A,SMALL(IF((Data!$I$2:$I$20="TD Ameritrade")*(Data!$J$2:$J$20="Stock")*(Data!$D$2:$D$20="")*MATCH(Data!$A$2:$A$20&Data!$I$2:$I$20&Data!$J$2:$J$20,Data!$A$2:$A$20&Data!$I$2:$I$20&Data!$J$2:$J$20,0)=ROW($1:$19),ROW($2:$20)),ROW(A1))),"")
If you insert a row somewhere in Data!$2:$20
your formula will dynamically widen that range, but this part of the formula: ROW($1:$19),ROW($2:$20)
will not expand, since it's not referring to Sheet Data!
if you include that it'll expand as well:
`=IFERROR(INDEX(Data!A:A,SMALL(IF((Data!$I$2:$I$20="TD Ameritrade")(Data!$J$2:$J$20="Stock")(Data!$D$2:$D$20="")*MATCH(Data!$A$2:$A$20&Data!$I$2:$I$20&Data!$J$2:$J$20,Data!$A$2:$A$20&Data!$I$2:$I$20&Data!$J$2:$J$20,0)=ROW(Data!$1:$19),ROW(Data!$2:$20)),ROW(A1))),"")