Remove item from list due to variable

[nutrastat]

Hi, hope somebody can help me...

I have a sheet for electric fly killer (EFK) maintenance. There are two types of EFK - Zapper (i.e. electric killer grid kills flies) and Glue Type (i.e. a sticky glue board catches & retains the flies to kill them).

To show when maintenance has been undertaken, on the drop down list are just two entries - Changed & Emptied (named range called EFK_Maint_List). On the Glue Type (G) you can 'change' the glue board, whilst with the Zapper (Z) you can only 'empty' the fly catch tray. On this sheet, to maintain legal compliance, it is important to select the correct type of maintenance undertaken (i.e. Z = Emptied and G = changed).

Each row has all the EFK details, and in column D it does have a 'G' or 'Z' to denote fly killer type, but despite this fact personnel still select the wrong item 'changed' or 'emptied'.

Question: what formula can be used to only show 'changed' if column D = G, and only shows 'emptied' if column D = Z?

 

[jbeaucaire]

1) In an empty cell somewhere put the word Changed (example AA1)
2) In an empty cell put the word Emptied (example AA2)
3) Highlight AA1 and click in the NAME BOX (just to the left of the formula bar where AA1 is probably showing). In that NAME BOX, type the word GlueType
4) Highlight AA2 and type the word Zapper into the NAME BOX
5) Now let's add a Data Validation drop down to the cells where you were wanting a "formula". Instead, let's highlight those cells.

For instance, if you highlighted G2:G50...

6) Click on Data > Validation > Settings > Allow: List > Source: =IF($D2="G",GlueType,IF($D2="Z",Zapper,""))

7) Click OK to apply that formula.

Now, based on the value in D2, there will be a "drop down" list in G2 that only has one option showing... if D2 has a "G" in it, the only option will be "Changed". If it's "Z", then the drop down will show "Emptied". If neither, there will be no options in the drop down.

 

[nutrastat]

Hi,

MANY THANKS for this solution; it has been plumbed into the spreadsheet and works GREAT!

It is also a solution I will use on another couple of lists.