View original (French)

Copy a row based on a cell value

peterpepin Posted messages 1 Status Member -  
via55 Posted messages 14393 Registration date   Status Member Last intervention   -
Hello,

Can you tell me how to copy a row based on a cell to another sheet?

In this example, the choice of the cell would depend on column B (thickness).
I would like that if 6 is selected (B11), the entire row 11 is written.

Thank you in advance.

1 answer

  1.
    via55 Posted messages 14393 Registration date   Status Member Last intervention   2 759
     
    Good evening peterpepin

    Use VLOOKUP for each element of a column to return

    For example: =VLOOKUP(6,$B$6:$Q$16,2,0)
    will look for 6 in column B and return the value from the 2nd column of the table B6:Q12 located on the same row, which is the value in column C (the 0 at the end of the formula specifies an exact match)
    In the next cell, you want the formula to return the value in column D on the same row as 6 in B, so the formula will be:
    =VLOOKUP(6,$B$6:$Q$16,3,0)
    etc

    To make it easier, you can initially name the range B1:Q13 (select the range - right click Name the range) for example montableau and then use this name in the formula:
    =VLOOKUP(6,montableau,2,0)

    Finally, if you want to change the value to search for, you can enter this value, for example in A1, and in the formula, indicate the reference to this cell
    =VLOOKUP($A$1,montableau,2,0)
    so every time you modify the value in A1, the result in the formula cell will adjust

    Best regards
    Via
    "Imagination is more important than knowledge." A. Einstein
    0