Excel Question (Data Validation)
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.
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.
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
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;
Telegram is the best way to reach me
https://t.me/ttnnkkrr
If you have forum suggestions please submit a
Check Out WebWriter
but not in despair; Persecuted‚ but not forsaken; cast down‚ but not destroyed;
Telegram is the best way to reach me
https://t.me/ttnnkkrr
If you have forum suggestions please submit a
Check Out WebWriter
Re: Excel Question (Data Validation)
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 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
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;
Telegram is the best way to reach me
https://t.me/ttnnkkrr
If you have forum suggestions please submit a
Check Out WebWriter
but not in despair; Persecuted‚ but not forsaken; cast down‚ but not destroyed;
Telegram is the best way to reach me
https://t.me/ttnnkkrr
If you have forum suggestions please submit a
Check Out WebWriter
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 ~
Re: Excel Question (Data Validation)
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?
--
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?
-
- Posts: 18
- Joined: 20 Sep 2019, 03:26
Re: Excel Question (Data Validation)
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
Re: Excel Question (Data Validation)
Wow... Thank you very much!!!