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

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!!!?

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

"Lee IT" wrote:

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!!!?

Use this formula

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

Jack Sons
The Netherlands


"Lee IT" schreef in bericht
...
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!!!?

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,"")

galimi Wrote:
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

"Lee IT" wrote:
-
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!!!?-


--
Lee IT