User Assigned Range

  • Thread starter Thread starter Jeff
  • Start date Start date
J

Jeff

I'm trying to set up a work sheet what would allow a user
to enter a range of cells to count how many "P's" are in
that range. Below is a simplistic illustration.

User Enters
A B

1 Start Range: w2
2 End Range: w10

W
1 P
2 P
3 F
4 P
..
10 F

I want my formula to use the range entered by the user in
in a set of cells to tabulate the total P's (we're using
the spreadsheet to track thousands of test cases, P means
that test passed). The formula for counting the P's is
simple, I can't figure out a way to assign a variable
range, so the user can enter it in a cell as opposed to
going in and editing the actual formula.

Is this possible?

Any help is appreciated.
Jeff
 
THANKS! You wouldn't believe how many variations I tried
using the Indirect function. I don't understand the
impact of using &" (but it works). It would have helped
if MS covered it in the Help section.

Jeff
 
MS is funny that way. I think the only way you would find the answer in Help
is if you knew it in the first place. ;-)

Alternatively, you could stumble upon it by searching on CONCATENATE, which
is a word I use *all* the time. ;-)
 
One more question - I tried expanding the function to
cover data residing in another work sheet. I want to set
up the reports/queries on 1 worksheet and the data on
separate sheets (there will be multiple sheets of test
data). The cells are highlighted when I step through the
equation, but I get a Ref error. Can you go 2 for 2?


=COUNTIF(INDIRECT(F3&"!"&F4&":"&F5),"P")

Jeff
 
Back
Top