If Missing #, Show Letter on multiple sheets

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! 


0
5/4/2005 12:54:55 AM
excel 39880 articles. 2 followers. Follow

4 Replies
481 Views

Similar Articles

[PageSpeed] 25

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

-- 
to e-mail direct replace @mailinator.com with @tiscali.co.uk


"Pablo" <native_texan_03@yahoo.seeohm> wrote in message
news:%23zf2BjMUFHA.3448@TK2MSFTNGP10.phx.gbl...
> 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!
>
>


0
sandymann (252)
5/4/2005 6:46:39 PM
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" <sandymann@mailinator.com> wrote in message 
news:uFYGInNUFHA.3936@TK2MSFTNGP10.phx.gbl...
> 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
>
> -- 
> to e-mail direct replace @mailinator.com with @tiscali.co.uk
>
>
> "Pablo" <native_texan_03@yahoo.seeohm> wrote in message
> news:%23zf2BjMUFHA.3448@TK2MSFTNGP10.phx.gbl...
>> 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!
>>
>>
>
> 


0
5/4/2005 11:23:02 PM
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

-- 
to e-mail direct replace @mailinator.com with @tiscali.co.uk


"Pablo" <native_texan_03@yahoo.seeohm> wrote in message
news:OJO6FBQUFHA.2520@TK2MSFTNGP09.phx.gbl...
> 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" <sandymann@mailinator.com> wrote in message
> news:uFYGInNUFHA.3936@TK2MSFTNGP10.phx.gbl...
> > 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
> >
> > -- 
> > to e-mail direct replace @mailinator.com with @tiscali.co.uk
> >
> >
> > "Pablo" <native_texan_03@yahoo.seeohm> wrote in message
> > news:%23zf2BjMUFHA.3448@TK2MSFTNGP10.phx.gbl...
> >> 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!
> >>
> >>
> >
> >
>
>


0
sandymann (252)
5/4/2005 11:40:46 PM
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?



"Sandy Mann" <sandymann@mailinator.com> wrote in message 
news:%23F7WgLQUFHA.1432@TK2MSFTNGP09.phx.gbl...
> 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
>
> -- 
> to e-mail direct replace @mailinator.com with @tiscali.co.uk
>
>
> "Pablo" <native_texan_03@yahoo.seeohm> wrote in message
> news:OJO6FBQUFHA.2520@TK2MSFTNGP09.phx.gbl...
>> 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" <sandymann@mailinator.com> wrote in message
>> news:uFYGInNUFHA.3936@TK2MSFTNGP10.phx.gbl...
>> > 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
>> >
>> > -- 
>> > to e-mail direct replace @mailinator.com with @tiscali.co.uk
>> >
>> >
>> > "Pablo" <native_texan_03@yahoo.seeohm> wrote in message
>> > news:%23zf2BjMUFHA.3448@TK2MSFTNGP10.phx.gbl...
>> >> 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!
>> >>
>> >>
>> >
>> >
>>
>>
>
> 


0
5/5/2005 9:59:15 PM
Reply:

Similar Artilces:

GAL not showing up after 5.5 to 2003 migration
We have migrated users from 5.5 to 2003, now the users do not see the elements in the GAL. I test from a user which is DOMAIN ADMIN. In Exchange System Manager, under Recipients, All GAL, Default GAL, i checked that the "default permissions" allowed my users to read the information. "Authenticated Users" have under the "Special permission" (not inherited) Read, Execute, Read Permissions, List Contents, Read Properties, List Object, they also have Open Address List (not inherited) and List Contents (inherited). The Domain Admins have everything but Full Control...

Missing files
Hello I accidentaly deleted some folders from my E:drive. I realised what I had done, opened the recycle bin, and restored them. All Ok except for the E:Videos folder, which did not appear in the recycle bin ! It's not a disaster, as I have the folder backed up, but I'm puzzled ? Ideas pls ? Thanks KK On 19/05/2010 15:15, KRK wrote: > Hello > > I accidentaly deleted some folders from my E:drive. I realised what I > had done, opened the recycle bin, and restored them. > > All Ok except for the E:Videos folder, which did not appea...

E-mail one sheet
I have a workbook of several sheets but I want the client to be able to fill out one sheet and e-mail only that sheet. How do I do that in a very simple way. Check out your other threads please -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Lella" <Lella@discussions.microsoft.com> wrote in message news:263C8B91-A927-48A6-A7D0-13D3CF1EDE15@microsoft.com... >I have a workbook of several sheets but I want the client to be able to fill > out one sheet and e-mail only that sheet. How do I do that in a very simple > way. ...

Change the scheduling hours to show working hours by default?
When inviting attendees and looking at their schedule, it defaults to showing me 24 hours. I click on "show working hours" under options, but it doesn't stay and I can't figure out how to set it to show working hours by default. ...

Outlook 2007 Missing Mail
I have an office where we are noticing that if we look in Vista Mail that MS outlook 2007 has missed many emails. Program restarts and reboots don't seem to correct the problem, and both programs are using the same IMAP settings. Is there a "helpful" little option in Outlook that prevents it from looking a all email receieved to an account? Does anyone else have the same problem? are there any similarities between the ones it misses? -- "nano" <nano@discussions.microsoft.com> wrote in message news:DC22F7E7-ACB7-4824-A0A1-3329D3C622EA@microsoft.com.....

Windows Mail Drops 1st 2 letters of Reply-To Address
About a month ago, Windows mail started dropping the 1st 2 letters of the reply-to email address for all of my Yahoo contacts...I deleted the contacts from my list and reentered them...it still does it. I can send a new email to these people just fine...but cannot use the "reply to" feature. Any ideas? -- christinewv "christinewv" <guest@unknown-email.com> wrote in message news:133071c331d4e1bf5f974795ec9ed6d5@nntp-gateway.com... > > About a month ago, Windows mail started dropping the 1st 2 letters of > the reply-to email address for ...

Analysis section does not show on Data tab
HI all, Excel 2007 I was going through some tasks today and working on my laptop. When I needed to use the Solver I found that the Analysis section on the Data tab was not displayed. When check the same document on my desktop that section is clearly displayed. Here are the two Data tabs, one is the laptop on which it does not show up, and the other is the desktop where it does. http://datacomptech.com/Pictures/Excel%202007%20Data%20tab%20ribbon.png (image is located on my business website) The Laptop screen is 17: wide, and the desktop is 22" wide, but, I am not sure that is th...

Can I Save an Excel spread sheet as html text?
Question #1 When I select "Save as Web Page", click "Publish", check the "open published web page in browser" box, then click "Publish" again I encounter the following message: "A World Wide Web browser, such as Microsoft Internet Explorer, is required to use this feature." However, I am only interested in saving as html text, nothing more than that -so maybe I should perform a different procedure? My ultimate goal is just to be able to import html text into Excel (which I already can do), modify the text in Excel and then save the updated tex...

Show Desktop Alert only for certain emails in 2003
Is there any way to get access to the desktop alert window in Outlook 2003? I only want it to display for certain email - not any spam. Perhaps trapping an event or ??? I don't want to turn it off completely, but I don't want it poping up all the time either. Thanks in advance for any assistance. : ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com Figured it out. From M$... "If you are using message rules and a new e-mail message is moved out of the defa...

Same Text on Multiple Worksheets
I need to enter text into certain fields on a worksheet and I want that text to repeat automatically on other worksheets in the same workbook. Cell references on the other worksheets are not the same as the original worksheet. -- PB You can use formulas that point to any cell on that original worksheet. It may be a pain to set it up the first time, but once it's done, it's done. =if(sheet1!a1="","",sheet1!a1) can go in any sheet in any cell to retrieve the value from A1 on sheet1. This is nice for titles. But if you have a key unique value in a column, you m...

Outlook 2003 not saving multiple messages as text files
This is weird: when I select several messages and Save (them) As: text files, the file in question only gets the headers, not the bodies: ex: select two messages then Save As text files: From: John Doe Sent: Tuesday, 24 June 2003 09:24 To: Some (E-mail) Subject: Interesting From: Kate Bloggs Sent: Wednesday, 12 November 2003 08:31 To: Other (E-mail) Subject: See you on Monday Nothing else! Why would this very basic function change in Outlook 2003? Outlook 2002, 2000 and 97 save the messages perfectly! Outlook 2003 only saves one (full) text file per message; if I select two or more messa...

Missing Emails #2
From time to time my users report that so-and-so sent them an email but they never got it. Last night my VP sent 2 emails from home and never got them. 1. We have Exchange 2000 server, all clients are Outlook 2. We have Symantec anti-virus for exchange server now I have checked the following: 1. in the Exchange logs [reading them in Excel] I can see the 2 messages arrive and seem to process normally -- HELO,MAIL, RCPT, DATA, QUIT are the 5 lines in the log; the datum on the HELO record says =+sccrmhc13.comcast.net. the Quit record as cs-uri-query of 240. 2. I have searched through...

Missing Folders after Sync
After connecting to the Exchange Server, several folders that were created to store messages disappeared. They are not on the server or any .pst file in my system. Any idea how I can find them and restore? Where were they located? -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -What do the Outlook Icons Mean? -Create an Office 2003 CD slipstreamed with Service Pack 1 ----- "Marc" <mhyoung@yahoo.com> wrote in message news:8a8201c4854a$3d473f10$a601280a@phx.gbl... > After connecting to the Exchange Server, several folders > that were c...

Selecting Multiple Pages
Hi, I am trying to select all the drawings I have in this Visio file that has multiple pages. I know I can do it per page but I was wondering if there is a way to select all the drawings, copy and paste it in a Word document for example? Any help is appreciated. Thanks, AJ There is no easy way to do this (sorry--it's not an unusual request so I wish I had a better answer), but I can offer a couple of tips and tricks. If you don't want the Word document to get too large, do the following for each page: 1. Make sure the page you want is active 2. Make sure NOTHING is ...

Bin Transfer Entry window missing
I am trying to enable multiple bins in GP 9.0 in the sample company. I clicked on Multiple Bins, ran reconcile and checklinks, but I cannot find the window to do a Bin Transfer Entry (Bin to Bin Transfer). I am looking under Transactions >> Inventory, and "transaction entry" is listed, as well as "Transfer Entry", as well as several other options, however, "Bin Transfer Entry" is not listed. Does anyone know how I can go about getting that option? Thanks, KJ Enter a transfer entry from site A to site A and complete the line. -- Richard L. Whaley Author...

Protected Sheets
How do I allow other useres to be able to go in and insert comments on a sheet that I have certain cells protected because I do not want them to alter the formulas? Jolo, depends on what version you have, try this, when you go to protect the sheet check edit objects -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Jolo" <Jolo@discussions.microsoft.com> wrote in message news:4A493046-E0C4-4DCC-8C8D-9980A647D078@micros...

How to show customer payment?
Hi everybody. I just started using Money 2003 Deluxe for my business (services-based). I'm trying to figure out if I can somehow print a statement or receipt that would show Customer address info, when they paid, for what project, remaining balance, etc. I tried the statements but it doesn't provide all the info I need. I also tried the invoice designer with no luck. Does anyone here have any ideas or methods for getting around this? Would certainly appreciate any help with this. Jason ...

Lists on different sheet without using the filter.
Hello, In Excel 2007 I am trying to create a new sheet that will list all rows that have the same entry in it. I can not use the filter option on the main sheet as each person will only be allowed to see their own data so is there are function that might assist me? I.e. If the person type Paul into a cell and the month number in to another then the list that is created and displayed is all the rows that contain the name Paul in column R for the given month. Thanks for any asssitance offered -- Jim On the main list sheet, do you have dates or just month numbers in a column ...

Missing Fonts in Word Art
When I open up a Pub 2003 document on a computer that did not create the document, the fonts that are missing on the current computer are listed as the document is opened. These missing fonts are only those used in text boxes, not the fonts used in word art. The word art just defaults to an arial font. When I open the "edit text" menu in word art, the font box is blank. Do I have a setting wrong or is this just how it is with Publisher 2003? Thanks, -- ~~~~~~~~~~~~~~~~~~ Brad Lyon bradnospam@ldgpv.com PIPster wrote: > When I open up a Pub 2003 document on a computer ...

Missing Message Text
Yesterday, Windows Live Mail started doing something strange. I type the message, then press "send." The first lines of the message somehow are removed. The recipient sees only part of the message and only part of the message shows up in my sent messages folder. The first part is just gone. Seems to be only on messages that are replies. Any ideas on what the problem is, or how to correct it, would be appreciated. Never heard of this . . . . . are you on version 14.0.8089.0726? Regardless, try a reinstallation. "DeVere" <DeVere@discussions.microsoft...

How do I send emails showing my email address but hiding my name ?
I know it can be done cause I had it set up that way when I first opened my email account several years ago. A friend didn't believe it was me emailing him so I set it to show my name. Now it has been so long I forgot how to set it back. Can anyone help me with this please & thank you! Remove your name in the Mail Setup -- Peter Please Reply to Newsgroup for the benefit of others Requests for assistance by email can not and will not be acknowledged. "Baffled Chic-a-dee" <Baffled Chic-a-dee@discussions.microsoft.com> wrote in message news:455AD3E0-8...

Is a multiple if-then formula possible?
What I'm trying to do is figure out an excel formula that does th following: IF user enters the numbers 3 or 4 or 5 or 6 in field A3, put a 0 i cell B3 and IF user enters the number 7 or 8 in A3, put a -1 in cell B3 IF user enters the number 9 or 10 or 11 or 12, put a -2 in cell B3 No user entry below the digit 3 or above the digit 12 will be entere by the user. Any help will be greatly appreciated. I have to go take some Tyleno now : -- bort ----------------------------------------------------------------------- bortz's Profile: http://www.excelforum.com/member.php?action=getinfo...

user's messages-profile missing when connected to new domain-help
HI, Please be patient as the story is long: I migrated my users from a workgroup to a Client/Server network about a month ago. A network using Windows 2000 active Directory. All the clients workstations are Wondows 2000 professional. Before migration, I exported the emails, etc. to a .pst file and after migration imported the same file and all was fine. Last over the weekend our Active Directory server crashed by a Virus supposedly and we had to rebuil the servers, and couldn't have the users logon on to the network to the export/import scenarion again. After the server were re...

HOW do you move multiple transactions at once?
Ok, so I am going to try to play by Money's rules... I have an account that was downloaded. I can't merge this account with my old account for two reasons. 1.) It will "delete all old transactions to prevent duplicates" - Whomever approved this design needs a talking to. 2.) If I do, it will simply create the account again! So, I then tried exporting my old account and re-importing it... the problem here is that Transfers and Payees are all messed up, requiring a LOT of manual work! So now I am trying "Cut & paste to Account" which works, but I only seem ...

How do I get my website created with publisher to show properly
Can you help, please I'm desperate: i created my website in publisher 2003, it seems to work great in IE, i'v gotten it to work in Opera, Mozilla etc., but I'm having problems with the bottom nav buttons, they won't work, the nav on the left side does. Is there a way of me importing my website into another program so I can make changes and update in an easy manner? Thanks any help would be much appreciated. I presume that the bottom navbar works when viewed with IE, but doesn't when you use another browser. One workaround I know about is to replace the bottom navb...