Surely it should be

=IF(OR('Substance Use'!G9="40",'Mental Health'!G9:="40"),"40","0")

in G9 on MySheet and copy down


--
HTH

Bob Phillips

(replace xxxx in email address with googlemail if mailing direct)

<Andy wrote in message ...
Try this:

=IF(OR('Substance Use'!G9:G71="40",'Mental Health'!G9:G71="40"),"40","0")

Andy.

"Mark" wrote in message
...
The cells will contain numbers. I want to identify those cells that
contain
"40" and then put a value of 40 in the corresponding cell ie if in
'Substance
Use'!G9 there was a value of 40, i would want to put a value of 40 in
'mysheet'!G9. if there was no value of 40, just add 0. If i was looking
at
just one range of figures, the formula would be:
=IF('Substance Use'!G9:G71="40","40","0")

but i also need to look at the other range of cells 'Mental
Health'!G9:G71
for the same criteria.

"Andy" wrote:

Hi
It depends on what you are wanting. You could use OR or AND functions
to
do
this but we'd need a bit more info!

Andy.

"Mark" wrote in message
...
I wish to query two ranges of cells. the cells are on two separate
worksheets
in the same workbook, but do not know how to specify more than one
range
in
an IF function. i need the formula to look at both ranges for a
particular
value
the ranges a
'Substance Use'!G9:G71 and 'Mental Health'!G9:G71

please can you advise how to construct the fiormula.
thanks