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

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

        SergeiBaklan 

        I meant in ref to the operators in the formula you supplied:
        "...="Yes")*(ROW..." and "..(ROW()-ROW..."

        Wouldn't have thought they would be used in a non-numeric sense. I'm learning though.

         

        I see what you mean re the 'SMALL' function. That's a bit easier to understand than the examples I'd read online. Thanks.

         

        Malc

Resources