named Range Referenced vai single cell

[Graham]

Hi I am trying to create a report that anyone in my office can then generate.
I have created named ranges and then use the names in equations, I Have
linked the criteria to a seperate sheet so all anyone has to do is type in
the criteria and the report will work.
I can't however reference named ranges via the other worksheet.
The named ranges are created using column titles, so what i am trying to do
is, when the user types in the column title in the worksheet say in cell B6
the equation in another worksheet will use this as it's reference and count
the number of times the give criteria appears in the named range e.g.
=COUNTIF(Overall_Status,"Not Started"), this returns the correct answer 112
but if I use =COUNTIF(B6,"Not Started") or =COUNTIF(INDIRECT(B6),"Not
Started") they return zero or REF#error.
Is this possible and Thanks in advance.
Graham

[Leo Heuser]

"Graham" <Graham@discussions.microsoft.com> skrev i en meddelelse
news:BBF617CB-5202-4012-808E-4A943D93792E@microsoft.com...
> Hi I am trying to create a report that anyone in my office can then
> generate.
> I have created named ranges and then use the names in equations, I Have
> linked the criteria to a seperate sheet so all anyone has to do is type in
> the criteria and the report will work.
> I can't however reference named ranges via the other worksheet.
> The named ranges are created using column titles, so what i am trying to
> do
> is, when the user types in the column title in the worksheet say in cell
> B6
> the equation in another worksheet will use this as it's reference and
> count
> the number of times the give criteria appears in the named range e.g.
> =COUNTIF(Overall_Status,"Not Started"), this returns the correct answer
> 112
> but if I use =COUNTIF(B6,"Not Started") or =COUNTIF(INDIRECT(B6),"Not
> Started") they return zero or REF#error.
> Is this possible and Thanks in advance.
> Graham

Hi Graham

If B6 is on sheet1 and COUNTIF() on sheet2, you have
to use the sheet name:

=COUNTIF(INDIRECT(Sheet1!B6))


--
Best regards
Leo Heuser

Followup to newsgroup only please.