Forum Discussion

carl1962's avatar
carl1962
Copper Contributor
Nov 24, 2018

IF.. I am trying to get information from one sheet to mirror in a second sheet IE IMPUT TO QUOTE

{=IFERROR(INDEX('COST PRICE ESTIMATE SHEET'!A$16:A$872,SMALL(IF('COST PRICE ESTIMATE SHEET'!$J$16:$J$872<>0,ROW('COST PRICE ESTIMATE SHEET'!A$16:A$872)-ROW('COST PRICE ESTIMATE SHEET'!A$16)+1),ROWS('COST PRICE ESTIMATE SHEET'!A$16:'COST PRICE ESTIMATE SHEET'!A16))),"")}

 

Above is what I have used before and its worked but since doing some alterations i cannot get it to go? I have entered alt control and ENTER but no joy, any ideas?

excel
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Nov 24, 2018

    That's array formula, did you enter entire formula =IFERROR(...,"") with Ctrl+Shift+Enter ?

    • carl1962's avatar
      carl1962
      Copper Contributor
      Nov 24, 2018

      Hi Yes how the file works, only if I add a quantity out of the list of 872 lines will it appear in the quote, originally a Pal did this formula and I have been copying / paste for the last 12 months and its worked fine but now its just not picking up?

      • SergeiBaklan's avatar
        SergeiBaklan
        MVP
        Nov 24, 2018

        Sorry, but I didn't catch what exactly doesn't work. Formula itself is correct, it copies values from column A starting from row 16 if only for such rows values in J are not zeros. May you attach the file or at least screenshot which illustrates what doesn't work?

Resources