Forum Discussion

Mark_Bry's avatar
Mark_Bry
Copper Contributor
Apr 24, 2020
Solved

If column 1 in the top table = 'yes' I want to copy column 2 & 3 into the table below columns 1 & 2.

I have basic knowledge in Excel. I want to know how to do the following:

If cells in column 1 in a table = 'yes' I want to copy that row in column 2 & 3 into a separate table. I have been trying different formulas but its above me. Please can you help by giving the correct formula. Or at least point me in the right direction.

excel
Formulas and Functions
  • wumolad's avatar
    wumolad
    Apr 24, 2020

    HiMark_Bry 

     

    Perhaps this works better. I have inserted another sheet with the report table and a choice cell.

     

    The formula is using the index function and small function to extract the information based on the choice made.

     

    Hope this works better. Do let me know if you need further clarification.

     

    Cheers

14 Replies

  • wumolad's avatar
    wumolad
    Iron Contributor
    Apr 24, 2020

    Mark_Bry You can use this formula

     

    =IFERROR(INDEX($C$2:$C$14, SMALL(IF($E$2=$A$2:$A$15, ROW($A$2:$A$15)-ROW($A$2)+1), ROW(1:1))),"" )

     

    I believe it works. It works as an array so you need to press Ctrl+Shift+Enter.

     

    Cheers

    • Mark_Bry's avatar
      Mark_Bry
      Copper Contributor
      Apr 24, 2020

      wumolad 

      Thanks for the helping on this one, although it is still not working. I get a 0 entered in column 1 of the second table. I'm not great on excel yet so your formula went straight over me head (got it sort of).  I may not of explained very well. I want a hidden data sheet with lots of rows showing info. When a row is selected by picking yes from the drop down in column 1 then columns 2 & 3 in that row is added to a table on another sheet which is not hidden. We can then pick and chose which bits of info goes in the table which can be viewed to send out. By selecting yes automatically adds the info. The tables I attached was so that I could test in principal then alter the cells/sheet in the formula to suit. I really do appreciate any help on this.

      • wumolad's avatar
        wumolad
        Iron Contributor
        Apr 24, 2020

        HiMark_Bry 

         

        Perhaps this works better. I have inserted another sheet with the report table and a choice cell.

         

        The formula is using the index function and small function to extract the information based on the choice made.

         

        Hope this works better. Do let me know if you need further clarification.

         

        Cheers

Resources