Poblems Using Calculated Strings in Other Functions

[KBSpec]

Hi All,

I'm having trouble pulling a calculated string in another function. I have a table with a list of tests performed in a laboratory. I have created a dynamic (it changes as data changes) list of unique test names. I now want to count the number of each unique test in the data. I'm trying to do a countif() for each unique test name.

=COUNTIF(LALData[Product Name],"Alcohol*") works just fine, but
=COUNTIF(LALData[Product Name],Sheet3!A3&"*") returns 0

While trouble shooting I tried =Sheet3!A3 which also returns 0. I don't know what the circumstances were, but I did get Alcohol a couple of times while using =Sheet3!A3 (and now can't get it to do it again).

I suspect it is really simple, but I can't figure it out. Please help.

Kristyn

 

[Mackers]

Hi

I just tried using COUNTIF() with a randomised target for both the range and the criteria (using INDEX and RANDBETWEEN) as well as a wildcard and it seemed to work for me. Could it be something as simple as having auto calculation off, or needing to press F9? Alternatively have you dynamically named your range in a way that makes the range drop off for some reason, e.g. it is from A1 down to COUNT(A:A) rather than to COUNTA(A:A) or similar?

If I've got the wrong end of the stick let me know.

 

[KBSpec]

When I use F9, it calculates the funtion (with the correct value) and replaces the function with that value. How do I use it dynamically? I'm missing something important here.

 

[KBSpec]

I seem to be struggling with a circular reference. In my data, I have a list of all the tests performed. From that list I have dynamically created the possible tests list. I am then using the dynamic list to count how many times that particular test was run.

This seems to be why F9 is required to do the calculations.

I'm going to try doing the count at the same time I'm creating the dynamic list to avoid the circular reference problem. If anyone has any suggestions, I would be most appreciative. Thanks

 

[KBSpec]

Since I am in control of populating the data, I just added a second column of test names. I'm using one to create the dynamic list and the second to calculate the frequency. It is not elegant, but it does the job. If anybody has a better solution, again, I would appreciate hearing about it. Thanks again.