Prev: highlighting different line in a cell
Next: sorting multiple columns
From: Duke Carey on 18 Feb 2010 07:13
'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" <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
> > >> >
> > >> >
> >
> >
> > .
> >
First  |  Prev  | 
Pages: 1 2
Prev: highlighting different line in a cell
Next: sorting multiple columns