Forum Discussion

bethcpm's avatar
bethcpm
Copper Contributor
Sep 06, 2021

Formula

Hi

I'm creating a staff matrix training spreadsheet that shows when their licences & tickets expire & I want to be able to insert new staff members in alphabetical order on sheet 1 & their name carries onto sheet 2 through to sheet 10. When I inserted a new staff member for eg. between  A8 & A9 The information for A9 disappears, so all the sheets 2 - 10 licences & info don't line up any more. Can you please help me. This is the formula I'm using on sheet 1. and I have attached an example. I'm not an expert on formulas so an step by step would be greatly appreciated.

 

=VLOOKUP(A8,Licence!$A$2:$D$195,4,FALSE)

=VLOOKUP(A9,Licence!$A$2:$D$195,4,FALSE)

=VLOOKUP(A10,Licence!$A$2:$D$195,4,FALSE)

excel
Formulas and Functions

6 Replies

  • Yea_So's avatar
    Yea_So
    Bronze Contributor
    Sep 10, 2021

    bethcpm 

     

    I created a variant, its somewhat dynamic.

    I used drop down lists when adding employees to the matrix transaction sheet

    and a Power query updates the Staff sheet.

     

    Let me know if you have any questions.

    Instructions within the file

    What the entry table looks like

     

    What the Staff sheet tab looks like

    What the add employee sheet looks like

    File attached below:

     

     

     

  • Yea_So's avatar
    Yea_So
    Bronze Contributor
    Sep 07, 2021

    bethcpm 

     

    Copy a) then Paste to cell B8:

    a) =IFERROR(VLOOKUP(Licence!A8,$A$1:$P$195,COLUMN(B$1),0),"")

    b) copy B8

    c) Select cells C8 to cell P8

    d) press enter

    e) Select cells B8 to cell P8

    f) copy

    g) Select cells B9 to cells B195

    h) press enter

     

    cheers

    • bethcpm's avatar
      bethcpm
      Copper Contributor
      Sep 08, 2021

      Yea_So

      Hi

      I have tried this & didn't work for,

      Licence is Column C on sheet 1

      Beth is A4 on Licence sheet see attached.

      • Yea_So's avatar
        Yea_So
        Bronze Contributor
        Sep 08, 2021

        bethcpm 

         

        give me a dummy workbook so I can put the formulas in.

         

        replace the names with Name01, Name02, Name03...etc

        I need the mapping on both sheets so I can reference the correct columns relative to each sheet with the correct column names

Resources