Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif across multiple sheets - sheet NAME issue?
Hi there,
I am using the following formula (see below), but wondering if the sheetnames I enter have to be in the EXACT order that there are in the spreadsheet? =SUMPRODUCT(COUNTIF(INDIRECT("'"&BA2:BA151&"'!e50" ),"x")) where BA2:BA151 is a range housing the relevant sheetnames in separate cells ... AND e50 is the cell I am referencing in every sheet. I get a #REF! error. Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif across multiple sheets - sheet NAME issue?
I get the Ref error only if a sheet name is mispelled or if one of the cells
that is supposed to have a sheet name is empty "Tim" wrote: Hi there, I am using the following formula (see below), but wondering if the sheetnames I enter have to be in the EXACT order that there are in the spreadsheet? =SUMPRODUCT(COUNTIF(INDIRECT("'"&BA2:BA151&"'!e50" ),"x")) where BA2:BA151 is a range housing the relevant sheetnames in separate cells ... AND e50 is the cell I am referencing in every sheet. I get a #REF! error. Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif across multiple sheets - sheet NAME issue?
I have extra sheets at the beginning and end of the 'named' sheets ... would
that have anything to do with it do you think? "Duke Carey" wrote: I get the Ref error only if a sheet name is mispelled or if one of the cells that is supposed to have a sheet name is empty "Tim" wrote: Hi there, I am using the following formula (see below), but wondering if the sheetnames I enter have to be in the EXACT order that there are in the spreadsheet? =SUMPRODUCT(COUNTIF(INDIRECT("'"&BA2:BA151&"'!e50" ),"x")) where BA2:BA151 is a range housing the relevant sheetnames in separate cells ... AND e50 is the cell I am referencing in every sheet. I get a #REF! error. Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif across multiple sheets - sheet NAME issue?
I have extra sheets at the beginning and
end of the 'named' sheets What does "extra" sheets mean? If the sheets don't exist yet but you have their names listed in the range then you'll get the #REF! error. -- Biff Microsoft Excel MVP "Tim" wrote in message ... I have extra sheets at the beginning and end of the 'named' sheets ... would that have anything to do with it do you think? "Duke Carey" wrote: I get the Ref error only if a sheet name is mispelled or if one of the cells that is supposed to have a sheet name is empty "Tim" wrote: Hi there, I am using the following formula (see below), but wondering if the sheetnames I enter have to be in the EXACT order that there are in the spreadsheet? =SUMPRODUCT(COUNTIF(INDIRECT("'"&BA2:BA151&"'!e50" ),"x")) where BA2:BA151 is a range housing the relevant sheetnames in separate cells ... AND e50 is the cell I am referencing in every sheet. I get a #REF! error. Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif across multiple sheets - sheet NAME issue?
Hi there ... these are just additional sheets that are not apart of the
'named' sheets. I have a couple at the beginning of the spreadsheet ... then 150 named sheets ... then a couple extra sheets at the end of the spreadsheet. Didn't know if they would cause the issue..... "T. Valko" wrote: I have extra sheets at the beginning and end of the 'named' sheets What does "extra" sheets mean? If the sheets don't exist yet but you have their names listed in the range then you'll get the #REF! error. -- Biff Microsoft Excel MVP "Tim" wrote in message ... I have extra sheets at the beginning and end of the 'named' sheets ... would that have anything to do with it do you think? "Duke Carey" wrote: I get the Ref error only if a sheet name is mispelled or if one of the cells that is supposed to have a sheet name is empty "Tim" wrote: Hi there, I am using the following formula (see below), but wondering if the sheetnames I enter have to be in the EXACT order that there are in the spreadsheet? =SUMPRODUCT(COUNTIF(INDIRECT("'"&BA2:BA151&"'!e50" ),"x")) where BA2:BA151 is a range housing the relevant sheetnames in separate cells ... AND e50 is the cell I am referencing in every sheet. I get a #REF! error. Thanks . |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif across multiple sheets - sheet NAME issue?
'Extra' sheets wouldn't have any influence on the issue. I suspect you have
one or more names in your list of sheets that has an extra space at the end, or is mispelled in some other fashion, or maybe even references a sheet that doesn't exist. You could put a formula in an empty column next to the list of sheet names to check on the validity of the name =INDIRECT("'"&[cell with first name]&"'!A1") Copy that down to the end of your list. If one or more of them gives you the Ref error, you have a pointer to the problem. "Tim" wrote: Hi there ... these are just additional sheets that are not apart of the 'named' sheets. I have a couple at the beginning of the spreadsheet ... then 150 named sheets ... then a couple extra sheets at the end of the spreadsheet. Didn't know if they would cause the issue..... "T. Valko" wrote: I have extra sheets at the beginning and end of the 'named' sheets What does "extra" sheets mean? If the sheets don't exist yet but you have their names listed in the range then you'll get the #REF! error. -- Biff Microsoft Excel MVP "Tim" wrote in message ... I have extra sheets at the beginning and end of the 'named' sheets ... would that have anything to do with it do you think? "Duke Carey" wrote: I get the Ref error only if a sheet name is mispelled or if one of the cells that is supposed to have a sheet name is empty "Tim" wrote: Hi there, I am using the following formula (see below), but wondering if the sheetnames I enter have to be in the EXACT order that there are in the spreadsheet? =SUMPRODUCT(COUNTIF(INDIRECT("'"&BA2:BA151&"'!e50" ),"x")) where BA2:BA151 is a range housing the relevant sheetnames in separate cells ... AND e50 is the cell I am referencing in every sheet. I get a #REF! error. Thanks . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF for multiple sheets | Excel Worksheet Functions | |||
SUMPRODUCT, SUMIF, COUNTIF for multiple sheets for multiple criter | Excel Worksheet Functions | |||
COUNTIF across multiple sheets with unknown names | Excel Worksheet Functions | |||
COUNTIF across multiple sheets | Excel Worksheet Functions | |||
CountIF across multiple sheets in a workbook | Excel Worksheet Functions |