Hi,

I'm having trouble populating a validation list with my macro, my validation
list is on one sheet, whilst the data to populate the validation list is on
another sheet. The validation list needs to be populated with the values
inside the FilteredProducts Range. My code is as follows:

Set AllClients = Range("CustomerSolutions").Offset(1,
0).Resize(Range("CustomerSolutions").Rows.Count

Set FilteredProducts = AllClients.Columns(8).SpecialCells(xlCellTypeVisib le)
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:="=FilteredProducts.Address()"
'Applys a product validation list to the product cell for the particular
customer
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "Product Required"
.InputMessage = ""
.ErrorMessage = "Please select a product from the drop
down list." & vbCrLf & _
"All rechargeable expenditure as
well as non-rechargeable airfares and accommodation costs must be allocated
to a product."
.ShowInput = True
.ShowError = True
End With

The problem is that the FilteredProducts.Address() is returning an address
such as $H$74, but I need to somehow specify the name of the sheet that the
address is referring to, e.g. SheetName!$H$74 but I'm not too sure how to do
this. I think that might solve the problem but I'm not sure.

Help would be greatly appreciated.
Thanks.