| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
including two ranges of cells in an IF formaula
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
including two ranges of cells in an IF formaula
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
including two ranges of cells in an IF formaula
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
including two ranges of cells in an IF formaula
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
including two ranges of cells in an IF formaula
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
including two ranges of cells in an IF formaula
Thank you both. This worked when i removed the speech marks from the numbers
in your formula. Mark "Bob Phillips" wrote: 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
including two ranges of cells in an IF formaula
What do you want to do with the ranges, count a value, compare each , or
what? -- HTH Bob Phillips (replace xxxx in email address with googlemail if mailing direct) "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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Average range including blank cells: #DIV/0! | Excel Worksheet Functions | |||
| Deleting contents of cells in non contiguous ranges | Excel Worksheet Functions | |||
| count them number of cells between values -2 and 2 including 0 | Excel Discussion (Misc queries) | |||
| how to combine including blank cells | New Users to Excel | |||
| empty cells in ranges | Excel Discussion (Misc queries) | |||
Hybrid Mode
