Best way to get Excel to do this?

chuckles1066 said:

I'd personally use Access for this but I've been asked to develop it in Excel.

There is to be a lookup sheet with employee names, which site they work at and various skills, training qualifications etc.

So, for example, the sheet might show Chuckles, his site is The Circus Of Horrors, his department is Children's Entertainment first aid trained would be y or n, trapeze artist grade would be 1 to 5, driving licence held would be y or n etc etc so that a complete picture in one table can be built up about every employee.

This will drive a userform or something, I imagine.

What I've been asked to do is create some sort of screen where, for example, someone can enter their requirements and it lists all qualified employees......so, above, if you entered the department as Children's Entertainment and driving licence held = y, it would bring back Chuckles (and anyone else who matched the criteria).

It's the classic select * from employees where........

Is this even do-able in Excel?

TIA.

Click to expand...

One option is to run AutoFilter, another is to invoke a system of formulas...

Let A1:F400 on Sheet1 house such data with fields: Employee, Site, Dep, First Aid, Trapeze Artist, Driving Licence.

Sheet2

A1: Dep

A2: Children's Entertainment

B1: Driving Licence

B2: Y

A4, just enter:

A5: Employee

A6, control+shift+enter, not just enter, and copy down:

chuckles1066 said:

What I've been asked to do is create some sort of screen where, for example, someone can enter their requirements and it lists all qualified employees......so, above, if you entered the department as Children's Entertainment and driving licence held = y, it would bring back Chuckles (and anyone else who matched the criteria).

Click to expand...

Would you only ever have one criteria (or none) per column like your example above? If so, here is one way that might suit.

Set up the sheet like this:




Now
1. Right click the sheet name tab and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window.

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> LastCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, c <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> sCrit <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><br>    <SPAN style="color:#00007F">Const</SPAN> CritRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = 2<br>    <SPAN style="color:#00007F">Const</SPAN> TableRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = 4<br><br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, Rows(CritRow)) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        LastCol = Cells(TableRow, Columns.Count).End(xlToLeft).Column<br>        <SPAN style="color:#00007F">With</SPAN> Cells(TableRow, 1).CurrentRegion<br>            <SPAN style="color:#00007F">For</SPAN> c = 1 <SPAN style="color:#00007F">To</SPAN> LastCol<br>                sCrit = Cells(CritRow, c).Value<br>                <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> sCrit<br>                    <SPAN style="color:#00007F">Case</SPAN> vbNullString<br>                        .AutoFilter Field:=c<br>                    <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Else</SPAN><br>                        .AutoFilter Field:=c, Criteria1:=sCrit<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br>            <SPAN style="color:#00007F">Next</SPAN> c<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>



Enter criteria in the yellow cells in the appropriate column. The result could be this for example.




If you might want more than 1 criteria per column, how many might you want (maximum) and what version of Excel are you using?

Thanks both.

There will definitely be a need for more than one criteria I imagine, I think 5 would be the maximum.

My company are still using Excel XP.

chuckles1066 said:

Thanks both.

There will definitely be a need for more than one criteria I imagine, I think 5 would be the maximum.

My company are still using Excel XP.

Click to expand...

Thanks for providing us feedback.

An efficient formula-system would be possible if a range criterion for a grade could be excluded. Otherwise, I expect VBA to be more suitable. Peter: Any comments?

With many columns (we don't know how many) and up to 5 criteria per column, any formula solution would be out of my reach at least.

I do have a vba suggestion, though I'm wondering if there might be a more efficient way than what I have come up with. Anyway here it is. I have done some testing in Excel 2003 so I think Excel XP should be okay but I don't have that version to test.

Set up the sheet like this:

Formula in A7 copied across.

Formula that I have in E2 needs ..
- to be in row 2 and 2 columns to the right of the last table column.
- to be adjusted to have a section like +C$7+COUNTIF(C$2:C$6,C11) for each table column.





Implement this code as I described previously (remove the previous code if it exists).

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> LastCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">Const</SPAN> CritRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = 2<br>    <SPAN style="color:#00007F">Const</SPAN> MaxCrit <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = 5<br>    <SPAN style="color:#00007F">Const</SPAN> TableRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = 10<br>    <br>    LastCol = Cells(1, Columns.Count).End(xlToLeft).Column<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, Range("A2").Resize(MaxCrit, LastCol)) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        Range("A" & TableRow).CurrentRegion.AdvancedFilter _<br>            Action:=xlFilterInPlace, CriteriaRange:= _<br>            Cells(1, LastCol + 2).Resize(2), Unique:=<SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>


Then enter your Criteria in the yellow cells. Here is one example result.

BTW.. If you want the sheet to look a bit tidier you could hide the formula rows/column (row 7 and column E in my example) once the formulas have been populated in them.


Aladin (or anybody else of course )

Can you see a more efficient way to write my E2 formula? Is a SUMPRODUCT or array formula possible here?
It would be especially hand if there were say 10 or more columns.

The formula needs to return TRUE if for every column ..

either: There is nothing entered in rows 2:6 for that column

or: The value in row 11 is found in rows 2:6 in that column

Any references to the top section (rows 1-7) need to be absolute (for rows at least) and the reference to row 11 needs to be relative since the formula has to effectively apply to each of the bottom section rows from row 11 down.

Peter_SSs said:

BTW.. If you want the sheet to look a bit tidier you could hide the formula rows/column (row 7 and column E in my example) once the formulas have been populated in them.


Aladin (or anybody else of course )

Can you see a more efficient way to write my E2 formula? Is a SUMPRODUCT or array formula possible here?
It would be especially hand if there were say 10 or more columns.

The formula needs to return TRUE if for every column ..

either: There is nothing entered in rows 2:6 for that column

or: The value in row 11 is found in rows 2:6 in that column

Any references to the top section (rows 1-7) need to be absolute (for rows at least) and the reference to row 11 needs to be relative since the formula has to effectively apply to each of the bottom section rows from row 11 down.

Click to expand...

A simpler set up maybe...

A8, copied across:

=COUNTIF(A$2:A$6,A$11)

E2 then becomes:

=SUM(A7:C7,A8:C8)=COUNTA($1:$1)

In the OP, it's said that there is a field Trapeze Artist with grades which go from 1 to 5. Does your code admit:

Trapeze Artist
2
4

as criteria?

I've avoided providing a pretty efficient formula solution which would not admit such, that is, the so called From-To criteria.

Aladin

Thanks for the suggestion. Unfortunately I haven't been able to get that to work with Advanced Filter. I think that the problem is that row 8 is actually static and when the Advanced Filter in the background effectively applies the E2 formula to each row, it is applying those static values from row 8 each time rather than calculating what those values would be for the row in question.

However, your suggestion did make me think differently and helped me progress as shown below in F2. This new formula would still need adjustment if the number of columns is increased/decreased, but it is now a more concise formula and therefore easier task to do the editing.

Aladin Akyurek said:

In the OP, it's said that there is a field Trapeze Artist with grades which go from 1 to 5. Does your code admit:

Trapeze Artist
2
4

as criteria?

Click to expand...

Yes, seems to handle that fine.

Raw data:




Filter criteria entered:

This would still fail, wouldn't it, if there was more than 1 field that could take similar values. For example two numeric fields that could both contain the same number?

For example here the filter shows rows where there is a 2 in either column D or column E.





Back to the drawing board! .. Or back to post #6 formula.