Limiting Validation Selections with VLOOKUP Data

[kingsolo]

So trying to build a spreadsheet for my daughter for a game she got for Christmas. Each card has a value 1-3. I can build the data validation and I have the VLOOKUP correct, but I'm trying to figure out how to make it to where she can only select the number of things that the card value is. So for example:

Ron Weasley has a Spellbook Value of 1, Harry Potter has a Spellbook Value of 2, and Snape has a Spellbook Value of 3.

If she selects Ron, I only want 1 of her Validation Lists cells to become useable. Harry, I only want 2 of her Validation Lists cells to become usable. Snape, she should be able to use all 3 Validation Lists.

Hope I laid this out correctly to where someone can help me out. Thanks.

 

[James006]

Hello,

There are several possibilities to create dependent drop-down lists ...

see Debra's excellent explanations : https://www.contextures.com/xlDataVal02.html

Hope this will help

 

[kingsolo]

Yeah Ive been looking at her site, but it doesnt include what Im trying to do. I want to use the data returned from a VLOOKUP formula (the Spellbook Values) to actually activate or disable data validation in 1-3 different cells. Im not even sure it can be done.

I should've done this to begin with I guess.

https://1drv.ms/x/s!Aoa335Q8qKqRji2J0jzigDUQaaP9

 

[James006]

Thanks for the link which does clarify your constraints ...

What is your actual objective ... since Range B13:B16 has no Data validation ...

Would you like rows 15 and 16 to be automatically hidden ?

or

Are you after some kind of special data validation for cells B13 - B16 ?

or

something else ...?

 

[kingsolo]

Well, Im currently building the sheet. But basically Im trying to limit her from being able to select anything in more rows than the character has in their Spellbook Value. So as you can see, Harry has a Spellbook Value of 2, meaning she should be able to select only 2 spells in B13:B16. So B13:B14 should be slectable for Harry, B15:16 should error out saying only 2 are selectable with this character or something similar. Eventually I'll add data to pull Potions and Artefacts similar to this section as well.

 

[James006]

Below event macro will adjust the number of visible rows ...

Private Sub Worksheet_Change(ByVal Target As Range)
  Select Case Range("A14").Value
      Case 3
        Rows("14:16").EntireRow.Hidden = False
        Rows("14:16").EntireRow.Hidden = True
      Case 2
        Rows("14:16").EntireRow.Hidden = False
        Rows("15:16").EntireRow.Hidden = True
      Case 1
        Rows("14:16").EntireRow.Hidden = False
        Rows("16:16").EntireRow.Hidden = True
      Case Else
        Rows("14:16").EntireRow.Hidden = False
  End Select
End Sub

Hope this will help

 

[kingsolo]

I dont think hiding rows will work. As I said Im building it so things will change as I see challenges and such. Heres a new link. As you can see she can have up to 5 characters, and each one has a different Spellbook Value between 1-3. So if we hid rows for Harry with a 2, Alastor would be missing a row to fill in for one of his spells.

So basically I need to disable the Validation in the cells B15:B16 for Harry, B14:B16 for Ron, B15:B16 for Nymphadora, B15:B16 for Sirius, and B16 for Alastor.

https://1drv.ms/x/s!Aoa335Q8qKqRji2J0jzigDUQaaP9

 

[kingsolo]

I would like something better, but I have come up with a backup by blacking out the cells using CF.

 

[Fluff]

Try in data validation for B13
=IF($C$11>=ROWS($C$11:C11),Spells!$A$2:$A$25)
and fill down

 

[kingsolo]

Sorry for the delay, was in an area with no internet service.

Once I figured out that I couldn't build my IF statements with data in C11, I was golden! Im an idiot sometimes for sure!

Thanks!