Restrict Data Validation

[Gilliam]

Hello,
I am having an issue restricting a cell to only what is in the drop down.
I have a cell that is data validated as a list to be passed this:
=IF(B4="Lease",$O$3:$O$6,INDIRECT(VLOOKUP($P$21,$O$2:$S$17,5)))

the indirect passes a range for example "O2:O11" which would fill the drop down with numbers 1 through 10. so after the VLOOKUP, it should look like...
=IF(B4="Lease",$O$3:$O$6,INDIRECT(O2:O11))

Including an error alert does not work in restricting the data, nor does it even do anything... I'm wondering if the formula with indirect is the problem?

Any help would be appreciated, thanks.

 

[pjmorris]

what is in the cell identified by the VLOOKUP?

 

[Gilliam]

what is in the cell identified by the VLOOKUP?

P21 is this formula
ROUNDDOWN((TODAY()-((B2-1900)*365.25))/365,0)

The user enters a year of a vehicle, and excel gives how old the vehicle is.
it is just a number, 1 through 16.

 

[pjmorris]

Hi,

My experimenting shows that if B4=Lease the validation works correctly and 4 values are picked up as options in the drop down list. What do you have in column S next to each number 1 to 16? It needs to be a string giving the address of the data that should appear in the validation. I've set my sheet up to do this and it works fine.

In summary: You have overlapping data in that the LEASE validation list is in the lefthand side (the tested side) of you VLOOKUP, you need to make sure that the VLOOKUP returns an address (this can simply be typed in as text into column S.

Hope this makes sense, if not can you submit a sample of your sheet.Can you send a sample sheet?

Regards

 

[Gilliam]

[TABLE="class: grid, width: 250"]
<TBODY>[TR]
[TD]B
[/TD]
[TD]O
[/TD]
[TD]P
[/TD]
[TD]S
[/TD]
[/TR]
[TR]
[TD]2008
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD]O2:O16
[/TD]
[/TR]
[TR]
[TD]=2014-B2
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD]O2:O15
[/TD]
[/TR]
[TR]
[TD]data validation here
[/TD]
[TD]3
[/TD]
[TD][/TD]
[TD]O2:O14
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4
[/TD]
[TD][/TD]
[TD]O2:O13
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5
[/TD]
[TD][/TD]
[TD]O2:O12
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]6
[/TD]
[TD][/TD]
[TD]O2:O11
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]7
[/TD]
[TD][/TD]
[TD]O2:O10
[/TD]
[/TR]
</TBODY>[/TABLE]

I just want to restrict the user form entereing ... say 600

 

[pjmorris]

I hate to say it, but as you've described it and using the info abovethe validation works perfectly well for me. If I put 2008 in B2, I get 6 in B3 and 6 in P21, the validation list is then values 1,2,3,4,5,6,7,8,9,& 10. If I put in 2012 in B2 I get 2 in B3 and P21 and a validation list 1,2,3,4,5,6,7,8,9,10,11,12, 13 & 14.

Why not just restrict cell B2 to being an integer in the range you need?

Sorry this isn't more helpful.

Regards

Peter

 

[Gilliam]

I hate to say it, but as you've described it and using the info abovethe validation works perfectly well for me. If I put 2008 in B2, I get 6 in B3 and 6 in P21, the validation list is then values 1,2,3,4,5,6,7,8,9,& 10. If I put in 2012 in B2 I get 2 in B3 and P21 and a validation list 1,2,3,4,5,6,7,8,9,10,11,12, 13 & 14.

Regards

Peter

Oh the drop down works like a charm, however I can't restrict the user from inputting a number outside of that list (just typing it on their own).

 

[pjmorris]

Can you check that there is a tick in the check box for 'Show error alert after invalid data is entered' on the 'Error Alert' tab of the Data Validation dialogue?

If not, tick it and it should work, if it doesn't I think I'm stumped too!

 

[Gilliam]

It's ticked and it still allows me to enter numbers > 16 Thanks anyway! Must be something to do with having a formula as a source... I'll keep digging.
(Excel 2007).

 

[pjmorris]

We're using the same versions and my sheet restricts values so its nothing to do with the formula. I've sent you a private message with my e-mail, if you send me the spreadsheet I'll have a look.

Regards

Peter