return worksheet names that do not meet criteria

Hi,

I'm using this code from another thread here to count the number of worksheets where a specific cell value is equal to a value i provide:

Please Login or Register  to view this content.

so here's my question...how would i return the names of the worksheets that do NOT meet this criteria? it would be easy enough to grab the # of sheets that do not with an else statement, but i'm not sure how to return the actual names of the sheets, outputting the list of names to a cell in my main worksheet (or even a different worksheet)

thanks

Hello kmfdm515,

The UDF currently returns the number of sheets that match the criteria. If you want to return the names of the sheets that don't match, the UDF needs to be changed to return a String of the Worksheet names...

Please Login or Register  to view this content.

Sincerely,
Leith Ross

hi,

i got delayed and wasn't able to come back to this until now. i tried a modified version of what you gave me:

Please Login or Register  to view this content.

mainly changed two things - 1) i am comparing a percentage in a specified cell, and 2) having multiple cases so it only checks worksheets of the sepcified case.

but this returns an error saying a value in the formula is the wrong data type...when i show the calculation steps, it's underlining the Percent_Value portion. not sure what changed with this, because when i was simply counting the number of sheets instead of sheet names, it worked fine.

any help would be appreciated. thanks!

Hello kmfdm515,

Here are 2 problems I see with code...
1) You changed the return value of the function to Long and are assigning a String to it.

Please Login or Register  to view this content.

2) You reference an undefined variable as a cell address

Please Login or Register  to view this content.

Sincerely,
Leith Ross

thanks, that was it! i simply forgot to remove that line with the undefined variable, and forgot to change the Long to a String.

the problem i have now is that for various reasons i won't be able to format the cells to wrap text so that more than around 3 worksheets names are visible.

what i'd like to do is change it so that the names are actually placed into the comments of a cell one column to the left of the one the formula is actually in.

how difficult would that be?