## Excel Question (Data Validation)

Discuss other programming languages besides AutoHotkey
Guest10
Posts: 578
Joined: 01 Oct 2013, 02:50

### Excel Question (Data Validation)

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
Posts: 2822
Joined: 28 Sep 2013, 22:15
GitHub: ttnnkkrr
Location: Irving TX
Contact:

### Re: Excel Question (Data Validation)

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
We are troubled on every side‚ yet not distressed; we are perplexed‚
but not in despair; Persecuted‚ but not forsaken; cast down‚ but not destroyed;
If you have forum suggestions please submit a pull request
Check Out WebWriter
Thanks Tank
Guest10
Posts: 578
Joined: 01 Oct 2013, 02:50

### Re: Excel Question (Data Validation)

tank wrote: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
Posts: 2822
Joined: 28 Sep 2013, 22:15
GitHub: ttnnkkrr
Location: Irving TX
Contact:

### Re: Excel Question (Data Validation)

well you could use some vba macros but i was offering simple workaround
We are troubled on every side‚ yet not distressed; we are perplexed‚
but not in despair; Persecuted‚ but not forsaken; cast down‚ but not destroyed;
If you have forum suggestions please submit a pull request
Check Out WebWriter
Thanks Tank
Guest10
Posts: 578
Joined: 01 Oct 2013, 02:50

### Re: Excel Question (Data Validation)

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
Posts: 3
Joined: 16 Sep 2019, 10:14

### Re: Excel Question (Data Validation)

This is VBA data Validation
--
With Selection.Validation
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
Operator:=xlEqual, Formula1:="A, B"
xl.End With
return

But this code occur compile error
What should I do?
nitrofenix
Posts: 18
Joined: 20 Sep 2019, 03:26

### Re: Excel Question (Data Validation)

mick3 wrote:
16 Sep 2019, 10:31
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
; xlEqual = 3
return``````
mick3
Posts: 3
Joined: 16 Sep 2019, 10:14

### Re: Excel Question (Data Validation)

Wow... Thank you very much!!!