Data Validation - Restriction Not Working

[AmyVick87]

Hello all

I have a spreadsheet where two columns are using data validation. The first column works fine in that you can only select from the list but the second column still lets you type in whatever you want and the restriction doesn't work. When I try to add an Error Alert, I get "The Source currently evaluates to an error. Do you wish to continue?" If I select yes then nothing changes, if I select no then nothing changes. What is this error please? The source is as follows, if that helps:

=OFFSET(MainGenreStart,MATCH(O1,MainGenreColumn,0)-1,1,COUNTIF(MainGenreColumn,O1),1)

Thanks in advance

[#2]

@AmyVick87 

Let me suggest a different way to approach it.

You're obviously (because you know how to use OFFSET and named ranges) more than a beginner with Excel. So here's a different way to approach it.

• Set up your secondary Data Validation tables as distinct tables, and give them names.
• Add a "helper column" between your two data validation columns. In that, use a conditional =IF formula to select the appropriate table name for the sub-category.
• Refer to that sub-category name in the Data Validation formula, using =INDIRECT(Whatever_the_AdjacentCell_is)

[#3]

@mathetes Thank you for your reply. I didn't actually create the data validation, it was my brother and it took him a long time to figure out and he says your suggestion is way out of his skill set unfortunately (or he's being lazy IDK :) ). Is there a way of just getting the 2nd column to do what it's meant to do? 

[#4]

@AmyVick87 There is no error, though the message comes up when you enter the DV rule but have not yet filled in the main genre. Ignore the message and just start using the schedule as you intended.

But I agree with @mathetes that you can do it a lot easier with a few named ranges and INDIRECT.

Just make sure that the ranges for the sub-genres are called exactly like the name of the main category.

Have made some changes to your file (attached). The green coloured cells have DV with INDIRECT.

[#5 · Accepted solution]

@AmyVick87 

Hi Amy

I have split the list into 5 lists to make it easier to work with - not particularly elegant but so long as you don't create to many genres it seems to work ok. 

Hope this is what you wanted it.

Obviously you and/or your brother are in the entertainment business - my daughter started a job there as well - quite difficult in these trying times.

all the best.

Peter

[#6]

@peteryac60 This is absolutely perfect, thank you so much for doing this. There shouldn't be a need to add any more genres but occasionally we may need to add secondary genres but not too many. 

These are trying times indeed, we are still seeing some sales for physical products but we would be massively struggling if we didn't have the digital side of things. I wish your daughter all the luck and thank you again.

[#7]

@Riny_van_Eekelen thank you for your help but I will use what peteryac60 sent me. Thanks again

[#8]

@AmyVick87 You're welcome. Personally, I feel that the INDIRECT option is easier and more flexible. Your choice.

[#9]

@AmyVick87 

Hi Amy,

Not a problem - and the other solutions suggested are also good.

Can you please mark this solutions as complete.

thank you.

Peter

[#10]

@Riny_van_Eekelen 

Nice solution. Still INDIRECT but more direct than mine; no need for the helper column.  I'll remember that.