Excel Question (Data Validation)

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

Excel Question (Data Validation)

24 Feb 2014, 22:58

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.
User avatar
tank
Posts: 2784
Joined: 28 Sep 2013, 22:15
Facebook: charlie.simmons.7334
Google: ttnnkkrr
GitHub: ttnnkkrr
Location: Irving TX
Contact:

Re: Excel Question (Data Validation)

27 Feb 2014, 12:26

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;
https://www.facebook.com/ahkscript.org
If you have forum suggestions please submit a pull request
Check Out WebWriter
Thanks Tank :thumbup:
Guest10
Posts: 578
Joined: 01 Oct 2013, 02:50

Re: Excel Question (Data Validation)

27 Feb 2014, 13:19

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 ~ :)
User avatar
tank
Posts: 2784
Joined: 28 Sep 2013, 22:15
Facebook: charlie.simmons.7334
Google: ttnnkkrr
GitHub: ttnnkkrr
Location: Irving TX
Contact:

Re: Excel Question (Data Validation)

27 Feb 2014, 14:16

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;
https://www.facebook.com/ahkscript.org
If you have forum suggestions please submit a pull request
Check Out WebWriter
Thanks Tank :thumbup:
Guest10
Posts: 578
Joined: 01 Oct 2013, 02:50

Re: Excel Question (Data Validation)

27 Feb 2014, 14:42

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

Re: Excel Question (Data Validation)

16 Sep 2019, 10:31

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
Posts: 18
Joined: 20 Sep 2019, 03:26

Re: Excel Question (Data Validation)

20 Sep 2019, 22:53

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
; xlValidAlertStop = 1
; xlEqual = 3
xl.Selection.Validation.Add[Type:=3,AlertStyle:=1,Operator:=3, Formula1:="A, B"]
return
mick3
Posts: 3
Joined: 16 Sep 2019, 10:14

Re: Excel Question (Data Validation)

28 Sep 2019, 04:59

Wow... Thank you very much!!!

Return to “Other Programming Languages”

Who is online

Users browsing this forum: No registered users and 4 guests