Countif across multiple sheets - sheet NAME issue?
in [Functions]
|
From: Tim on 17 Feb 2010 15:11 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
From: Duke Carey on 17 Feb 2010 15:35 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 > >
From: Tim on 17 Feb 2010 15:44 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 > > > >
From: T. Valko on 17 Feb 2010 16:10 >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" <Tim(a)discussions.microsoft.com> wrote in message news:62126CBC-78E9-47E7-A7AD-AC6A534EC273(a)microsoft.com... >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 >> > >> >
From: Tim on 17 Feb 2010 17:09
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" <Tim(a)discussions.microsoft.com> wrote in message > news:62126CBC-78E9-47E7-A7AD-AC6A534EC273(a)microsoft.com... > >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 > >> > > >> > > > > . > |