Create delimeted list instead of constant array

[jaryszek]

Hi Guys,

i have formula which i am trying to input into validation list drop down:

Please Login or Register  to view this content.

what i am getting is :

"You may not use reference operators (such as unions, intersections, and ranges) or array constants for conditional formatting criteria"

as i wrote i can not use constant arary and i should have delimeted list.
How can i do this with my formula ?

Can anybody help?

Jacek

[XLent]

if the data is sorted, as implied by your use of MATCH (0 & 1), try changing to:

Please Login or Register  to view this content.

there is no volatility here, despite use of OFFSET, as the DV formula is only evaluated when the DV list is 'triggered' / 'opened'.

[bebo021999]

I wonder why you are using two MATCH of same value within a same range, with two types at once: 0 for exact and 1 for not exact match?
Also the INDEX.

Besides, you are using =INDEX():INDEX(). Try follow this : INDEX(INDEX():INDEX(),) to establish a new range.

[jaryszek]

thank you Guys!!

XlLent your formula is working.
Why mine formula didnt work?

INDEX(INDEX():INDEX(),)

thanks! Can you please provide any example ? I do not know how to implement?

Jacek

[AliGW]

i have formula which i am trying to input into validation list drop down:

...

what i am getting is :

"You may not use reference operators (such as unions, intersections, and ranges) or array constants for conditional formatting criteria"

Are you trying to create conditional formatting or data validation? They are not the same thing. The error message suggests the former, but you keep talking about the latter. Which is it?

[XLent]

I don't think the above really matters, you cannot use an INDEX:INDEX construct with either.

[jaryszek]

I am trying to do validation list.

INDEX:INDEX will create constant array?

I wonder why you are using two MATCH of same value within a same range, with two types at once: 0 for exact and 1 for not exact match?

i have just learned this method and was using.
Jacek

[AliGW]

I don't think the above really matters, you cannot use an INDEX:INDEX construct with either.

Granted, but why is the OP is trying to create data validation via a CF rule? In other words, why would he get that error message from the DV dialog???

[XLent]

INDEX:INDEX will create constant array?

a 'constant array' is, by definition, constant - e.g. =MATCH(A1;{"a";"b";"c"};0)

what you're trying to do is, rather, build a dynamic list relative to a criteria value
e.g. pick all values from Column D where associated value in C matches some criteria defined / selected already

outside of Data Validation (DV) and Conditional Formatting (CF) the INDEX:INDEX route would be preferable to OFFSET, as latter is Volatile.

fortunately, whilst you cannot use INDEX:INDEX with DV + CF, because DV is non-volatile and CF is super-volatile by default using OFFSET, as an alternative, is not really an issue.

[jaryszek]

I am getting error directly from Vaidation List when i am tyring to input formula above.

XLent,
thank you for such good explanation.

with count if i have very big issue which i described in this topic:

https://www.excelforum.com/excel-for...ml#post5372224

see post #8.

Can you please take a look how to solve the issue using countifs function?

Best,
Jacek

[bebo021999]

I gave it a test again, and below is what I got:
=INDEX(PivotHelpful!D2:D15;MATCH($A2;PivotHelpful!$C$2:$C$15;0);1):INDEX(PivotHelpful!$D$2:$D$15;MATCH($A2;PivotHelpful!$C$2:$C$15;1);1)
work, but in condition:

1) Put it in a User define name (Ctrl-F3 to activate the Name Manager), give it a name, i.e, "Range"
2) Apply name "Range" into DataValidation
3) 1st Source list MUST BE in increasing order (to not-exact-match $A2)

Anyway, OFFSET is usually used for this kind of job, as per #2.