If Missing #, Show Letter on multiple sheets

[Pablo]

Hello:

Stumped on how to solve this. What I'm trying to do is list what
alpha-characters are missing by detecting if a number is present. All of the
worksheets are exactly the same except for the alpha character they
represent.

What I need is an algorithm which will detech what is missing from each
sheet. I have a # to identify if a alpha is missing, but I need a way to
summarize this.

For example, assume there are three worksheets: wks1, wks2, and wks3. If
cell B15 on wks1 has 1, wks2 cell B15 has 2, and wks3 cell B15 has 3 I want
the algorithm to return "". However, if I have only 1 and 3, then I want "B"
to be the outcome. If I only have 2 and 3, I'd like "A" to appear. If I only
have 3, I'd like either {A B} or "only C" to be the result.

This pattern will continue for 10+ worksheets. Any suggestions?

Much appreciation for this group!

 

[Sandy Mann]

Hi Pablo,

Would a custom function do?

if so try:

Function Foo()
Application.Volatile
c = Sheets.Count
For x = 1 To c
If Sheets(x).Range("B15").Value = "" Then Foo = Foo & Chr(x + 64) &
" "
Next x
If Foo = "" Then Foo = "All full"
End Function

HTH

Sandy

 

[Pablo]

Sandy:

Thanks for the response; however, my VBA is elementary....is this just copy
paste into vba or what would i need to mod?

THanks!

 

[Sandy Mann]

Hi Pablo,

Yes, just copy and paste it into a VBA module and then enter =Foo() in the
spreadsheet to use the function.

If you want to make the formula more versitile then try:

Function Foo(T As String)
Application.Volatile
c = Sheets.Count
For x = 1 To c
If Sheets(x).Range(T).Value = "" Then Foo = Foo & Chr(x + 64) & " "
Next x
If Foo = "" Then Foo = "All full"
End Function

and enter the function in the spreadsheet as =Foo(F1) where F1 contains the
reference to the cell address to be checked. ie if you want to check B15
then enter B15 in Cell F1 and you will check the sheets using cell B15,
change the entry in F1 to be A1 and the function will check the sheets using
A1 as the new reference to be checked.

HTH

 

[Pablo]

WOW!

Sandy: Thanks! This is really cool. The code appears very simple, but I'm
still stumped at how this works; however, it appears to be doing exactly
what I need!

Is "Foo" just something you made up, or does it represent some VBA command?