Can a formula check for a certain value in a range?

L

Lee IT

I need a formula function to check if a named range contains a certain entry.
A formula similar to: IF(DATA!Dave='computer',d16,""), where DATA is the
worksheet and Dave is the named range. But, rather than '=' I need a
function that checks if the range 'contains' a single or a number of
'computer' entrys in the range. The quantity of entrys is unimportant.

The Range:
The named range is a number of rows, of which the first column is merged to
contain the name of the person the rest of the data refers to. The data can
contain numbers, text or blank cells - relevant to the column headers.

I'm sure this must be possible!!!?
 
G

galimi

You can write a custom function similar to the following:

function contains (rngnm,cmpr)

for each cl in range(rngrm)

if cl.value = cmpr then

contains=true
exit function

end if
next
end function

http://HelpExcel.com
 
J

Jack Sons

Use this formula

=IF(COUNTIF(your_range,"computer"),D16,"")

Jack Sons
The Netherlands
 
L

Lee IT

Thanks - I have used this method before with a similar custom formula
function required problem. The code was given to me via a forum also.

I have copied this to Visual Basic Editor and saved it as a module. I
don't know VB macro code very well so I'm not sure how to adjust it if
I so need to. Would the formula then look something like this?

=IF(DATA!AOAcontains"Computer",D20,"")
 
Top