Here is a recap after our email exchange:

Description and Problem
------------------------
- data enties are in column F, H, J rows 4 to 16
- in F, the user pick from a Data Validation List, List1
- in H, user picks from a Data Validation List, List2. This list depends on
the value in F on the same row.
- in J, user picks from a Data Validation List, List3. This list depends on
the value in J on the same row.
-- How to create these dependent validation lists

Sheets
-------
The above entries are in sheet Data Entry and add a second sheet, Lists, to
store and organize the lists

Lists
------

- List1 is in sheet ListsRevenue, Administrative,Warranty, and Sick or
Vacation Time

- List2 for Revenue is: On Site Commissioning, Site Reports, Pre-Site Work,
Post-Site Work, Training, Telephone & Email Support, VP&D Support, RMA
request, Quotes, Upgrades
- List2 for Administrative is : Expense Reports, Telephone & Email , Field
Service, Reports, Infotrac Reports, Travel Arrangements, Meetings /
Conference Calls
- List 2 for Warranty is : Customer Support, Parts
- List 2 for Sick or Vacation Time is : Sick Time, Vacation Time

-List 3 ... same idea

Organize the Lists sheet
------------------------

1
- Add a sheet called Lists
- Put list one in B4:B7
- For List2, copy/paste the Transpose of List1 into B13:E13 -- header
- bellow each header enter the corresponding List2:
- D13 is 'Warranty' form List1
- In D14:D15, enter Customer Support, Parts -- list2 for Warranty
- same idea for List 3
- copy each items from all lists of List 2 in one row B28:U28
- bellow each header, enter the correwsponding List3 items

2 Named ranges
- List1: First list is easy, I just set the named range List1 to $B$4:$B$7
- List2:
Second list List2 is a dependent list: it depends on the selection made from
List1.
1. Transpose all the values from List1 on the top row and enter lists
underneath filed name.
2. Set a first named range called List2r (with a small 'r' ifor 'row'
as the top row ie header) ie cells $B$13:$E$13. This is where we search the
top row to determine which list to use based on choice from List1
3. Set a second named range called List2d (with a small 'd' ifor
'data' as lists themselves, ie $B$14:$E$23 (we'll determine later the length
of each list)
4. Add the final Named range List2:
=OFFSET(List2d,0,MATCH( 'Data Entry'!$F4, List2r, 0) -
1,COUNTA(OFFSET(List2d, 0, MATCH('Data Entry'!$F4,List2r,0) -1, ROWS(List2d),
1)),1)
5. on sheet 'Data Entry, select H4:Hxxx and apply the data validation:
List =List2
Here we could have done everything in 1 single formula without using
intermediate List2r and List2d but it makes everything painfull.
Also, as you can see, each cell of H has validation on List2... no
need of separate list for each cell of H.
- List3: For List3, just follow exactly the same process as for List 2 with:
List3r : =$B$28:$U$28
List3d : =$B$29:$U$33
List3 : replace List2d by List3d, List2r by List3r, 'Data Entry'!$F4 by
'Data Entry'!$H4 since it depends of value in $H
=OFFSET(List3d,0,MATCH( 'Data Entry'!$H4, List3r, 0) -
1,COUNTA(OFFSET(List3d, 0, MATCH('Data Entry'!$H4,List3r,0) -1, ROWS(List3d),
1)),1)

If you wanted to add more dependent lists follow the process for list 2 the
same way you did it for List3

Issue
------
Say F4, H4, J4 have been entered
Now the user modifies F4.
-- H4 and J4 stay the same as they were before the change ie they are part
of old List2 and List3 but not of the new List2 and List3
To prevent this issue, add code in the sheet _Change event to clear H4 and
J4 after F4 has been modified.
Same idea: clear J4 when H4 is modified.
Put the macro below into the Data Entry sheet code module. Just 2 lines of
code need to be changed to adapt to a different set of dependencies (section
'CHANGE HERE'):
'------------------------------------------------------

Private Sub Worksheet_Change(ByVal Target As Range)
Dim DepCols 'array of dependent columns
Dim RgDepEntries As Range 'range of columns
Dim RgDepTarget As Range 'range intersect DepEntries and Target
Dim rg As Range
Dim str As String
Dim ub As Long, pos As Long, i As Long

'CHANGE HERE ---------------------------
DepCols = Array("F", "H", "J") 'ordered by dependence
Set RgDepEntries = Range("F4:F16,H4:H16,J4:J16") 'correspomding range
with row numbers
'---------------------------------------

Set RgDepTarget = Application.Intersect(Target, RgDepEntries)
If RgDepTarget Is Nothing Then Exit Sub 'Exit if no entry in that range

'from now on, assumes RgDepTarget as rectangular area

'get letter of 1st column being changed
Set rg = RgDepTarget.Cells(1)
str = Left(rg.Address(False, False), IIf(rg.Column 26, 2, 1))

'look for next columns in dependency and clear them
pos = WorksheetFunction.Match(str, DepCols, 0)
ub = UBound(DepCols)
If pos - 1 < ub Then 'if not last column in dependecy then clear
Application.EnableEvents = False

For i = pos To ub
Set rg = Application.Intersect(Range(DepCols(i) & ":" & DepCols(i)),
RgDepEntries, RgDepTarget.EntireRow)
rg.ClearContents
Next

Application.EnableEvents = True
End If

'-find first cell column letter
'first cell column letter, used as reference for top dependency, the rest
(right side) is cleared

End Sub
'--------------------------------------------------------

--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Imaginator" wrote:

Any Luck?

"Imaginator" wrote:


Yes - I want the Total Time Spent cell to reference back to the data
validation drop down list that contains - Admin-Revenue- Warranty-Sick or
Vacation etc. and have that cell reference the Time Allocation table.

"sebastienm" wrote:

I got it, yes.
I think I get it know; you are tring to implement Dependent Lists for Data
Validation, isn't it?
I 'll work on it once i get home tonight.
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Imaginator" wrote:

Did you get my email?

"sebastienm" wrote:

yes you can email it to
remove 'dom' right after the @ in the email address.
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Imaginator" wrote:


The issue is that I have one cell validated list that contains "Revenue -
Administration - Warranty - Sick or Vacation Pay" the cell next to it
validates what function the field engineer did i.e. "system commissioning for
Revenue or emails for Administration" the next cell should call out to a time
sheet that they fill in and reflects back to let's say " Revenue- System
Config.

If this is confusing myabe I can send you the .xls sheet to look at?
"sebastienm" wrote:

Could you please be more specific.

I assume you are trying to have data validation as follow:
- The data-entry cells value must be found in the range Data
So far, no problem, nothing different than normal. You would use a regular
List type of data validation.
- The issue is that Data is made of 3 non-continuous ranges: Data1, Data2,
and Data3
In this case, you could use a function that checks is the value is in one
(at least) of these 3 ranges. To use a function, you would the Custom type of
data validation instead of the List type:

Assuming the data entry cell is A1:
=NOT(AND(ISERROR(MATCH(B2,data1,0)),ISERROR(MATCH( B2,data2,0)),ISERROR(MATCH(B2,data3,0))))

would that work for you?
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Imaginator" wrote:

I really need an answer if you can provide one

"sebastienm" wrote:

Hi,
What do you mean by 'validate a list'?
--
Regards,
Sébastien
<http://www.ondemandanalysis.com