named Range Referenced vai single cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Graham said:
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
Click to expand...

Hi Graham

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

=COUNTIF(INDIRECT(Sheet1!B6))
 
Back
Top