Excel Question (Data Validation)

[Guest10]

i would like to perform the following in Excel:
Data > Validation > Data Validation > Allow List > Data
in Data field i would like to create a list of values to pick values of other cells. for example, A1, B2, C3, etc. however, this does not work: =A1,=B2,=C3, ... i know, however, a range works such as =A1:A10. but i would like to pick random values from other cells not a range: =A1,=A3,=A5, ... (this never works!). P.S.: Of course an easy/typical application of a list is a simple string of values: apples,oranges,grapes,bananas, ... but I would like to pick values of other random cells.

[tank]

so basically you want to choose a random cell from a list =INDEX(A1:A14,RANDBETWEEN(ROW(A1),ROW(A14)))
you could use columns instead. but you could use that to create random values from another range of cells in each of the cells in your list.
That isnt doing it for you but maybe it sparks an idea

[Guest10]

so basically you want to choose a random cell from a list =INDEX(A1:A14,RANDBETWEEN(ROW(A1),ROW(A14)))
you could use columns instead. but you could use that to create random values from another range of cells in each of the cells in your list.
That isnt doing it for you but maybe it sparks an idea

thanks, so basically i am not allowed to pick random values from other cells directly (=A3,=B5,=C7, ...) but i could use your workaround for the same end result ~

[tank]

well you could use some vba macros but i was offering simple workaround

[Guest10]

thanks, someone suggested to create a range (X1:X10), which is allowed, and then associate the cells in this range to target random cells (A3,B5,C7,...) in the worksheet ~

[mick3]

This is VBA data Validation
--
With Selection.Validation
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlEqual, Formula1:="A, B"
End With
--

i want this code in AHK But i can't find 'With' statement in AHK
What should I do?

i write AHK code like this
F1::
xl:=ComObjActive("Excel.Application")
xl.Visible := True
xl.With Selection.Validation
xl.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlEqual, Formula1:="A, B"
xl.End With
return

But this code occur compile error
What should I do?

[nitrofenix]

i want this code in AHK But i can't find 'With' statement in AHK
What should I do?

You don't really need it -- what you do need is to enumerate xlValidateList, xlValidAlertStop and xlEqual (or have variables/constants for them):

Code: Select all

F1::
xl:=ComObjActive("Excel.Application")
xl.Visible := True
; xlValidateList = 3
; xlValidAlertStop = 1
; xlEqual = 3
xl.Selection.Validation.Add[Type:=3,AlertStyle:=1,Operator:=3, Formula1:="A, B"]
return

[mick3]

Wow... Thank you very much!!!