Forum Discussion

iapetustitan's avatar
iapetustitan
Copper Contributor
Nov 13, 2020
Solved

Can anyone sense check my formula? Copying cells between sheets

Hi.  I've got this formula to copy cell contents into another sheet on the same workbook: =IFERROR(INDEX('Sheet1'!$D:$D, TEXT(IF('Sheet1'!$C:$C="Yes",ROW('Sheet1'!$C;$C)),ROW())),"")   Now I'm on...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Nov 15, 2020

    iapetustitan Thanks for the clarification. Not sure I fully understood the logic, but I'd suggest

    in second column

    =IFERROR(
     INDEX(
       Area_Running_Totals[Order Code],
       AGGREGATE(15,6,
             1/(Area_Running_Totals[[Add To Order?]:[Add To Order?]]="Yes")*
             (ROW(Area_Running_Totals[Add To Order?])-ROW(Area_Running_Totals[[#Headers],[Add To Order?]])),
             (ROW()-ROW(OrderForm[[#Headers],[Supplier Product Code]]))
       )
),"")

    and similar to the right.

iapetustitan's avatar
iapetustitan
Copper Contributor
Nov 15, 2020
Hey folks, just bumping to keep this alive, hoping there is an answer out there. Thanks Malc
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Nov 15, 2020

    iapetustitan Thanks for the clarification. Not sure I fully understood the logic, but I'd suggest

    in second column

    =IFERROR(
     INDEX(
       Area_Running_Totals[Order Code],
       AGGREGATE(15,6,
             1/(Area_Running_Totals[[Add To Order?]:[Add To Order?]]="Yes")*
             (ROW(Area_Running_Totals[Add To Order?])-ROW(Area_Running_Totals[[#Headers],[Add To Order?]])),
             (ROW()-ROW(OrderForm[[#Headers],[Supplier Product Code]]))
       )
),"")

    and similar to the right.

    • iapetustitan's avatar
      iapetustitan
      Copper Contributor
      Nov 16, 2020

      SergeiBaklan 

      Excellent, thanks so much. Works like a charm.

      I'm scrutinising the formula to get a better understanding of it, still not sure how the 'SMALL' function works with text but I also didn't know you could use normal operators (-, *, etc) with non-numeric data either (though I guess it's not technically the actual cell content that is super important in this fashion?).

       

      Thanks again though, very much appreciated.

      Malc

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Nov 16, 2020

        iapetustitan 

        SMALL actually means that we take 1st, 2nd, 3rd, etc. value for which [Add to Order] flag is "Yes". For example

        row flag value

        1               201      

        2       Yes   302

        3               103

        4       Yes     55

        5               105

         

        first smallest position with flag will be 2; second smallest position is 4, etc. Returned values are 302 and 55 accordingly.

         

        Didn't catch the question about arithmetic operations with texts - nope, as a rule that doesn't work, by why do you need them here?

        5

Resources