T
tdemartino
Last week I posted for help w/VLookUp, Data Validation or anything that
would populate a spreadsheet based on a criteria. I don't think I
explained correctly or gave enough information, so I'm desperately
trying this again - I need so much help.
I used http://www.contextures.com/excelfiles.html#DataVal -
ProductsList and did some editing to suit our needs.
I have a spreadsheet with 2 tabs.
Tab 2 is called EmployeeList, with a list our employees with their
name, start date, dept, roll (manager, clerk, etc.) and a list of
classes (Class I, Class II, Class II) needed to attend (all in
columns). Depending on the roll, an "x" would go under the class you
needed to attended (Class A, in the row of John Smith would be an "x".
Beginning Column A, Row 4
ID Dept Position StartDate Class I Class II Class II
John Smith AR Asst Mgr 12/1/1999 X X X
Ruby Tuesday AP Mgr 9/1/2005 X X X
Mindy Waters CS Employee 3/1/2005 X
Bugsy Dee GRAP Mgr 8/1/2003 X X
Column A, Row 1 has ID, Row 2 has a formula = (Form!C3) - the
validation
I have 2 Named Lists. 1 is called Database and holds A4:G10; 2nd is
called EmployeeNames and holds A4:G10.
Tab 1 is called Form - C3 is the validated field, pointing to
EmployeeNames list. Row 6 is the headings from EmployeeList Tab Row 4.
When I use the drop down list at C3 to pull an employee name -
let's say John Smith - it populates A6 to G6
ID Dept Position StartDate Class I Class II Class II
John Smith AR Assistant Manager 12/1/1999 X X X
Here's the vb code in the form:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 3 And Target.Column = 3 Then
'calculate criteria cell in case calculation mode is manual
Worksheets("EmployeeList").Range("A2").Calculate
Worksheets("EmployeeList").Range("Database") _
.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("EmployeeList").Range("A1:A2"), _
CopyToRange:=Range("a6:g6"), Unique:=False
This is what I'd like to do if possible.
Based on the choice from the validated list on the Form, C3, have the
information NOT populate A6:G6 but rather like a form letter and
include the column heading when it applies. and if there isn't an X
in one of the class columns move the information up:
Employee: Alicia Zariello
Dept AR
Position Assistant Manager 12/1/1999
Class I X Class IV X
Class II X Class V X
Class II X
If Class IV is blank, have Class V move up
Employee: Mindy Waters
Dept CS
Position Clerk 3/1/2005
Class I Yes Class V Yes
Class II Yes
Class II Yes
I hope I explained myself correctly and gave enough information. Can
this be done?
would populate a spreadsheet based on a criteria. I don't think I
explained correctly or gave enough information, so I'm desperately
trying this again - I need so much help.
I used http://www.contextures.com/excelfiles.html#DataVal -
ProductsList and did some editing to suit our needs.
I have a spreadsheet with 2 tabs.
Tab 2 is called EmployeeList, with a list our employees with their
name, start date, dept, roll (manager, clerk, etc.) and a list of
classes (Class I, Class II, Class II) needed to attend (all in
columns). Depending on the roll, an "x" would go under the class you
needed to attended (Class A, in the row of John Smith would be an "x".
Beginning Column A, Row 4
ID Dept Position StartDate Class I Class II Class II
John Smith AR Asst Mgr 12/1/1999 X X X
Ruby Tuesday AP Mgr 9/1/2005 X X X
Mindy Waters CS Employee 3/1/2005 X
Bugsy Dee GRAP Mgr 8/1/2003 X X
Column A, Row 1 has ID, Row 2 has a formula = (Form!C3) - the
validation
I have 2 Named Lists. 1 is called Database and holds A4:G10; 2nd is
called EmployeeNames and holds A4:G10.
Tab 1 is called Form - C3 is the validated field, pointing to
EmployeeNames list. Row 6 is the headings from EmployeeList Tab Row 4.
When I use the drop down list at C3 to pull an employee name -
let's say John Smith - it populates A6 to G6
ID Dept Position StartDate Class I Class II Class II
John Smith AR Assistant Manager 12/1/1999 X X X
Here's the vb code in the form:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 3 And Target.Column = 3 Then
'calculate criteria cell in case calculation mode is manual
Worksheets("EmployeeList").Range("A2").Calculate
Worksheets("EmployeeList").Range("Database") _
.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("EmployeeList").Range("A1:A2"), _
CopyToRange:=Range("a6:g6"), Unique:=False
This is what I'd like to do if possible.
Based on the choice from the validated list on the Form, C3, have the
information NOT populate A6:G6 but rather like a form letter and
include the column heading when it applies. and if there isn't an X
in one of the class columns move the information up:
Employee: Alicia Zariello
Dept AR
Position Assistant Manager 12/1/1999
Class I X Class IV X
Class II X Class V X
Class II X
If Class IV is blank, have Class V move up
Employee: Mindy Waters
Dept CS
Position Clerk 3/1/2005
Class I Yes Class V Yes
Class II Yes
Class II Yes
I hope I explained myself correctly and gave enough information. Can
this be done?