if condition with data validation list

[jatman]

i would like a list for data validation, but the list could be from different
tabs.

with validation criteria, allow custom:


=INDIRECT("'"&VLOOKUP(H5,Suppliers!A:B,2,FALSE)&"'!$a:$A")

all data will be in column A, but different tabs. when i use the above
formulae, i get an error message indicating that i cannot use references to
other worksheets or workbooks for Data Validation criteria. But if i use the
formula "=items" it references the list from a different worksheet.

any suggestions to get past this?

 

[T. Valko]

any suggestions to get past this?

No. That's how it works.

You can't directly reference the source if it's on another sheet.

 

[Bob Phillips]

I think that you just need to define a name for Suppliers!A:B, let's say
_suppliers, and use

=INDIRECT("'"&VLOOKUP(H5,_suppliers,2,FALSE)&"'!$A:$A")


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[jatman]

doesn't work... as for the referencing a list from another sheet, Excel can
do that.

TAB2, highlight the column A, in the cell next to the formula bar, enter
"test" as the descriptor. enter various data in column A.
in TAB1, select a cell, select DATA Valadation, and the validation criteria
is allow list, and for the source enter "=test", and you can reference the
data in column A, on TAB2...

next option...

can it be written into ThisWorkbook

Sub Macro1()
'
' Macro1 Macro
'
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=LOOKHERE"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub


where LOOKHERE tells what to look for, something like this

lookhere = vlookup(d5,Suppliers!,A:B,2,false)

if i change the selection in D5, the cell that should make the list would
look up somewhere else.

cannot really give a better example...

suggestions?

jat

 

[Bob Phillips]

doesn't work... as for the referencing a list from another sheet, Excel
can
do that.

But only if that list is a defined name!

TAB2, highlight the column A, in the cell next to the formula bar, enter
"test" as the descriptor. enter various data in column A.
in TAB1, select a cell, select DATA Valadation, and the validation
criteria
is allow list, and for the source enter "=test", and you can reference the
data in column A, on TAB2...

next option...

can it be written into ThisWorkbook

Sub Macro1()
'
' Macro1 Macro
'
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=
_
xlBetween, Formula1:="=LOOKHERE"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub


where LOOKHERE tells what to look for, something like this

lookhere = vlookup(d5,Suppliers!,A:B,2,false)

if i change the selection in D5, the cell that should make the list would
look up somewhere else.

cannot really give a better example...

suggestions?

No idea what you are trying to do now, had little to start with, less now.

 

[jatman]

i will post my purchase order on the community templates soon, and will post
back here, it is easier to understand with a working copy of something,
rather than bits and pieces of things.

thanx,

jat