Sep 06 2021 04:26 PM
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)
Sep 06 2021 07:10 PM - edited Sep 06 2021 07:13 PM
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
Sep 08 2021 12:44 AM
Hi
I have tried this & didn't work for,
Licence is Column C on sheet 1
Beth is A4 on Licence sheet see attached.
Sep 08 2021 12:49 AM
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
Sep 08 2021 01:49 AM - edited Sep 08 2021 01:50 AM
Hi
I have started another dummy, so only some of the sheets have a formula. Hope this helps. Thank you
Sep 08 2021 08:49 PM - edited Sep 08 2021 08:58 PM
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
Sep 09 2021 10:26 PM
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: