Home > Net >  Dynamically fetch values which uses complex formula from another sheet
Dynamically fetch values which uses complex formula from another sheet

Time:10-09

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))),"")

  • Related