Test for string across multiple cells/sheets... further questions
Try replacing A253 by REPT(A253&"*",{1,2,3,4,5,6,7,8,9,10})) in your
countif formula to count up to 10 repitions of a non-blank search
string. Use e.g. transpose(indirect(row(1:99))) to extend to more
rows.
On Mar 8, 8:24 pm, wrote:
On Mar 8, 2:37 pm, "T. Valko" wrote:
First problem:
Assume:
A253 = this
Sheet1 I28 = this
Sheet2 I28 = is this
Sheet3 I28 = is this all
The wildcard version result = 3 which is correct.
Second problem: Good luck with that one! I would suggest that you put one
value per cell instead of x, x, z all in one cell. Then you could just
expand the range in the Countif:
.....&"'!I28:N28"),.....
Biff
Thanks for the reply.
PROBLEM 1
I just rechecked and yes, mistake was mine, it seems the cell
reference for the test wasn't updating and I was checking for terms
where they wouldn't possibly exist. Multiple versions works
fantastically.
PROBLEM 2
ARGH, not the response I was looking for. I have been struggling with
this for about 3.5 hours now to no avail. There seems to be a
workable solution involving length that I found "(total length of
cells in a range) - (total length of cells substituting nothing for a
specific term)/(term length). However, I can't get it to work in
three dimensions (multiple sheets).
There HAS to be a way to do this... my eternal gratitude to the person
that can figure it out!
-rt
|