New Contributor

# 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)

6 Replies

# Re: Formula

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

# Re: Formula

@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.

# Re: Formula

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

# Re: Formula

Hi
I have started another dummy, so only some of the sheets have a formula. Hope this helps. Thank you

# Re: Formula

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

# Re: Formula

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: