Limiting Validation Selections with VLOOKUP Data

kingsolo

New Member
Joined
Feb 17, 2008
Messages
25
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.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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
 
Upvote 0
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 ...?
 
Upvote 0
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.
 
Upvote 0
Below event macro will adjust the number of visible rows ...

Code:
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
 
Upvote 0
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
 
Upvote 0
I would like something better, but I have come up with a backup by blacking out the cells using CF.
 
Upvote 0
Try in data validation for B13
=IF($C$11>=ROWS($C$11:C11),Spells!$A$2:$A$25)
and fill down
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,223,248
Messages
6,171,021
Members
452,374
Latest member
keccles

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top