Shrinking List through Data Validation

Hi!
I've a Cover sheet (sheet1) for presentation & a Data sheet (sheet2) where
all the data are. In my Cover sheet, I've had a column where 3 connecting
cells (Sheet1!B2:B4) having data validation dropdown list from a named list
(MyList - Sheet2!$A2:$A65536) from my Data sheet. The funny thing I notice
is while the topmost cell (B2) might contain the full list, each cell
beneath are missing of the topmost entry of the previous list. This means
the list

in B2 is Sheet2!$A2:$A65536,
in B3 is Sheet2!$A3:$A65536,
in B4 is Sheet2!$A4:$A65536.

I thouught that by using a named list in my data validation, such
scenarios should not happen. Anyone got any idea why and how to fix this?
Thanks.
________
cLiffordiL

The named list should have absolute row references:
Sheet2!$A$2:$A$65536

Unless you have entries in every row, a dynamic list may be a better
solution. There are instructions here:

http://www.contextures.com/xlNames01.html

cLiffordiL wrote:
> Hi!
> I've a Cover sheet (sheet1) for presentation & a Data sheet (sheet2) where
> all the data are. In my Cover sheet, I've had a column where 3 connecting
> cells (Sheet1!B2:B4) having data validation dropdown list from a named list
> (MyList - Sheet2!$A2:$A65536) from my Data sheet. The funny thing I notice
> is while the topmost cell (B2) might contain the full list, each cell
> beneath are missing of the topmost entry of the previous list. This means
> the list
>
> in B2 is Sheet2!$A2:$A65536,
> in B3 is Sheet2!$A3:$A65536,
> in B4 is Sheet2!$A4:$A65536.
>
> I thouught that by using a named list in my data validation, such
> scenarios should not happen. Anyone got any idea why and how to fix this?
> Thanks.
> ________
> cLiffordiL
>
>


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

'VBA code - copy paste entire thing.Do read comments in line 4,5,6. Modify ranges as required.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

' range C5:C12 will get validation dropdown after macro is run
' G5:G12- list of items required in drop down
'H5:H12 will have formula to determine selection - =IF(COUNTIF($C$5:$C$12,G5),"",G5) --enter this formula in G5 and drag till G12

Dim t As Integer, j As Integer, intLastRow As Integer, introw As Integer
Dim keycells As Range
Dim txt As String
Set keycells = Range("C5:C12")

If Not Application.Intersect(keycells, Range(Target.Address)) Is Nothing Then

intLastRow = 12
For introw = 5 To intLastRow
If Not IsEmpty(Cells(introw, 8)) Then
txt = txt & Cells(introw, 8) & ","
End If
Next introw

txt = Left(txt, Len(txt) - 1)
With Range("C5:C12").Validation
.Delete
.Add _
Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:=txt
End With
End If

End Sub