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 carr...
excel
Formulas and Functions
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

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

      bethcpm 

       

      The following reasons why your formula does not work:
      1) You are getting your Employee names into sheet2 through sheet10 using a VLOOKUP from column A in sheet1 Staff!$A$6:$A$204

      =IFERROR(VLOOKUP(Staff!$A$6:$A$204, Staff!$A$6:$C$204, 1, FALSE), "")   ????

      2) Then you get your statuses "E", or "C" into sheet1 by doing a VLOOKUP against Column A in sheet1 from sheet2 through sheet10 "Licence" column in sheet1 "Staff!sheet which results in a spilled range from sheet1

       

      =VLOOKUP(A6,Licence!$A$2:$D$200,4,FALSE)

      I am surprised that excel did not error out with a circular error message! The formula works in the first three cells but starts to cause frustration on the fourth cell.  You need to change that before going forward.

       

      cheers

       

Resources