software

I am trying to use data validation to check whether when data is entered
if it is repeated. As the data is in a different worksheet I have
created a dynamic named range using the formula:
=OFFSET(BMs!$A$6,0,0,COUNT(BMs!$A:$A),1)
called BMs and then used a custom function in data validation to check
if the input is repeated using formula:
ISNA(VLOOKUP(B12,BMs,1,FALSE))
This gives me the same result whether an input is repeated or not.

any ideas?--
Jamuck
------------------------------------------------------------------------
Jamuck's Profile: www.excelforum.com/member.php...oamp;userid=30545
View this thread: www.excelforum.com/showthread...hreadid=505299Without looking at your formula at all, is there a reason you just don't use
List in the Data|Validation rules and point at =BMsJamuck wrote:
gt;
gt; I am trying to use data validation to check whether when data is entered
gt; if it is repeated. As the data is in a different worksheet I have
gt; created a dynamic named range using the formula:
gt; =OFFSET(BMs!$A$6,0,0,COUNT(BMs!$A:$A),1)
gt; called BMs and then used a custom function in data validation to check
gt; if the input is repeated using formula:
gt; ISNA(VLOOKUP(B12,BMs,1,FALSE))
gt; This gives me the same result whether an input is repeated or not.
gt;
gt; any ideas?
gt;
gt; --
gt; Jamuck
gt; ------------------------------------------------------------------------
gt; Jamuck's Profile: www.excelforum.com/member.php...oamp;userid=30545
gt; View this thread: www.excelforum.com/showthread...hreadid=505299

--

Dave Peterson


But won't that then require an input that is on the list and therefore
create a duplication which is what I want to avoid?--
Jamuck
------------------------------------------------------------------------
Jamuck's Profile: www.excelforum.com/member.php...oamp;userid=30545
View this thread: www.excelforum.com/showthread...hreadid=505299Yep. Sorry, I misunderstood the point of the question.

I used this formula:
=ISERROR(MATCH(b12,BMS,0))

(=match() is just about the same as =vlookup() when you're bringing back the
first column.)

This worked so that I couldn't enter any value that was in that list of BMs.

This may be easier to see:
=COUNTIF(BMS,b12)=0

====
Did you actually have an equal sign in your formula?
ISNA(VLOOKUP(B12,BMs,1,FALSE))
(the email post didn't show one, and your formula worked when I added it.)
=ISNA(VLOOKUP(B12,BMs,1,FALSE))

Jamuck wrote:
gt;
gt; But won't that then require an input that is on the list and therefore
gt; create a duplication which is what I want to avoid?
gt;
gt; --
gt; Jamuck
gt; ------------------------------------------------------------------------
gt; Jamuck's Profile: www.excelforum.com/member.php...oamp;userid=30545
gt; View this thread: www.excelforum.com/showthread...hreadid=505299

--

Dave Peterson


It did have an equals sign. I have figured out why it wasn't working
now - the figures in the list were linked to the inputted cells so that
every time I updated a value it found a repeat - doh! Thanks for your
help.--
Jamuck
------------------------------------------------------------------------
Jamuck's Profile: www.excelforum.com/member.php...oamp;userid=30545
View this thread: www.excelforum.com/showthread...hreadid=505299