Formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2726393%22%20slang%3D%22en-US%22%3EFormula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2726393%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3EI'm%20creating%20a%20staff%20matrix%20training%20spreadsheet%20that%20shows%20when%20their%20licences%20%26amp%3B%20tickets%20expire%20%26amp%3B%20I%20want%20to%20be%20able%20to%20insert%20new%20staff%20members%20in%20alphabetical%20order%20on%20sheet%201%20%26amp%3B%20their%20name%20carries%20onto%20sheet%202%20through%20to%20sheet%2010.%20When%20I%20inserted%20a%20new%20staff%20member%20for%20eg.%20between%26nbsp%3B%20A8%20%26amp%3B%20A9%20The%20information%20for%20A9%20disappears%2C%20so%20all%20the%20sheets%202%20-%2010%20licences%20%26amp%3B%20info%20don't%20line%20up%20any%20more.%20Can%20you%20please%20help%20me.%20This%20is%20the%20formula%20I'm%20using%20on%20sheet%201.%20and%20I%20have%20attached%20an%20example.%20I'm%20not%20an%20expert%20on%20formulas%20so%20an%20step%20by%20step%20would%20be%20greatly%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DVLOOKUP(A8%2CLicence!%24A%242%3A%24D%24195%2C4%2CFALSE)%3C%2FP%3E%3CP%3E%3DVLOOKUP(A9%2CLicence!%24A%242%3A%24D%24195%2C4%2CFALSE)%3C%2FP%3E%3CP%3E%3DVLOOKUP(A10%2CLicence!%24A%242%3A%24D%24195%2C4%2CFALSE)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2726393%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2726829%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2726829%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1148279%22%20target%3D%22_blank%22%3E%40bethcpm%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPaste%20this%20to%20cell%20B8%3A%3C%2FP%3E%3CP%3Ea)%20%3DIFERROR(VLOOKUP(A8%2C%24A%241%3A%24P%24195%2CCOLUMN(B%241)%2C0)%2C%22%22)%3C%2FP%3E%3CP%3Eb)%20copy%20B8%3C%2FP%3E%3CP%3Ec)%20Select%20cells%20C8%20to%20cell%20P8%3C%2FP%3E%3CP%3Ed)%20press%20enter%3C%2FP%3E%3CP%3Ee)%20Select%20cells%20B8%20to%20cell%20P8%3C%2FP%3E%3CP%3Ef)%20copy%3C%2FP%3E%3CP%3Eg)%20Select%20cells%20B9%20to%20cells%20B195%3C%2FP%3E%3CP%3Eh)%20press%20enter%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Echeers%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2731645%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2731645%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1047584%22%20target%3D%22_blank%22%3E%40Yea_So%3C%2FA%3E%3C%2FP%3E%3CP%3EHi%3C%2FP%3E%3CP%3EI%20have%20tried%20this%20%26amp%3B%20didn't%20work%20for%2C%3C%2FP%3E%3CP%3ELicence%20is%20Column%20C%20on%20sheet%201%3C%2FP%3E%3CP%3EBeth%20is%20A4%20on%20Licence%20sheet%20see%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2731671%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2731671%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1148279%22%20target%3D%22_blank%22%3E%40bethcpm%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Egive%20me%20a%20dummy%20workbook%20so%20I%20can%20put%20the%20formulas%20in.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ereplace%20the%20names%20with%20Name01%2C%20Name02%2C%20Name03...etc%3C%2FP%3E%3CP%3EI%20need%20the%20mapping%20on%20both%20sheets%20so%20I%20can%20reference%20the%20correct%20columns%20relative%20to%20each%20sheet%20with%20the%20correct%20column%20names%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2731770%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2731770%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3CBR%20%2F%3EI%20have%20started%20another%20dummy%2C%20so%20only%20some%20of%20the%20sheets%20have%20a%20formula.%20Hope%20this%20helps.%20Thank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

@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

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

@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

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

@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

 

@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

Yea_So_0-1631251350672.png

What the entry table looks like

Yea_So_1-1631251425246.png

 

What the Staff sheet tab looks like

Yea_So_2-1631251478961.png

What the add employee sheet looks like

Yea_So_3-1631251515606.png

File attached below: