concatenated value validation against list

[WallyWallWhackr]

Hey guys. I made a nice spreadsheet that pieces together three
separately entered digits and a multiplier digit in a fourth cell to
arrive at a value using the concatenate function.

I want to check the final, arrived at value against a list and pop up a
message if it is not.

The workbook is here:

http://office.microsoft.com/en-us/templates/recent.aspx#pg:4|ai:TC101932835|


I know it is not in much use any more (axial resistors) but the
workbook is the exercise.

Anyway, the list is the E96 value list:

100 121 147 178 215 261 316 383 464 562 681 825
102 124 150 182 221 267 324 392 475 576 698 845
105 127 154 187 226 274 332 402 487 590 715 866
107 130 158 191 232 280 340 412 499 604 732 887
110 133 162 196 237 287 348 422 511 619 750 909
113 137 165 200 243 294 357 432 523 634 768 931
115 140 169 205 249 301 365 442 536 649 787 953
118 143 174 210 255 309 374 453 549 665 806 976


The popup could appear near the band selector box.

 

[WallyWallWhackr]

OK. I have the list columnar and named it E96TermList

The cell where I have the concatenated term, I have named FinalTerm

Is there a function that I could use to pop up a note that says the
value is still wrong?

I know I can do it in a macro with a button or cyclic test, buy is
there a function that uses a list (named range) not to validate a
manually entered value, but that of a formula derived value?

Help! Please!

 

[WallyWallWhackr]

Is there a single "If this number is in this list, then do this, else do
this function"?

I could do it with nearly 100 conditional formatting entries, but I want
to avoid that.

Also in that vein, is there a way to manually make a conditional
formatting file and import it into the sheet all at once? Thanks.

 

[Max]

Is there a single "If this number is in this list, then do this, else do

this function"?

Recall 2 ways, eg:
=IF(COUNTIF(Range,2),<do this>,<do that>)
=IF(ISNUMBER(MATCH(2,Range,0)),<do this>,<do that>)

You could replace 2 with the cell reference which houses the test
value

 

[WallyWallWhackr]

Recall 2 ways, eg:
=IF(COUNTIF(Range,2),<do this>,<do that>)
=IF(ISNUMBER(MATCH(2,Range,0)),<do this>,<do that>)

You could replace 2 with the cell reference which houses the test
value

What takes place is that the user selects three significant digit
values, one at a time, then the fourth digit gets utilized as a
multiplier.

The first three, however, are what needs the test.

I currently concatenate those three together to a single value in a
cell position. That final cell is what I (want to) test.

The list is a range that runs from a lower value whole number to a
higher value whole number but the increment is not one, and it is also
not constant. So it is a specific list. I want to pop up a note when
the number is NOT valid, and ignore the valid numbers.It is 96 never
changing values.

I work with vlookup and the like quite well, but that is the other way
around. This is an "if is exist" kind of thing.

 

[Max]

I currently concatenate those three together to a single value in a

cell position. That final cell is what I (want to) test.

Concats returned by formulas result in text numbers.
To coerce these into real numbers,
you could use, for eg:
-- A2
A2+0
A2*1

where A2 contains the concat formula

 

[WallyWallWhackr]

Concats returned by formulas result in text numbers.
To coerce these into real numbers,
you could use, for eg:
-- A2
A2+0
A2*1

where A2 contains the concat formula

I have no problem here. Read the thread.

I want to test the value against a list.

I already know how to arrive at the value.

 

[Max]

The 2 ways given earlier should have worked ok.
If it didn't, then your data is inconsistent.
Ensure both lookup value and range values are consistent,
either all real numbers, or all text