Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Entering a range of numbers and searching same
I'm working with numbered documents. Some clients have a range of documents
(12003-12212) I want to search for document 12,198 and pull up the specific client tied to that document number. Know of any possible way to make this happen? Thanks again for your time and assistance. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Entering a range of numbers and searching same
Hi, Penny-
I started working on a solution for you and got sidetracked (my boss is so impetuous that way). Your earlier post said you're working with Bates numbering- are your document numbers contiguous, or do they have gaps? Also, by any chance are the documents related to a client entered as two numbers in two cells (the lowest document number and the highest document number), or are they entered in a single cell in the format 12003-12212? More globally speaking, do you have the latitude within your organization to re-arrange the data if you need to, in order to make this lookup idea possible? DaveO |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Entering a range of numbers and searching same
Dave - You are such a sweetheart! Thank you. Sorry to have reposted. I
don't post often enough to know how best to do this. And how dare that boss of yours! Aren't they all the same way. I'm still using docs with Bates numbers. For the lack of a better explanation from me, I'm trying to inventory all the docs. I do have complete control over the data and how it is entered and manipulated. For now, I have the bates number data in one field, but that can be changed. Some records have one doc, some have hundreds within the field. Should that be my first step, separate the beginning and ending of the range of docs? Hope this isn't too much info. I have not had enough caffeine this morning. Thanks again for your time. It is so very much appreciated. Penny "Dave O" wrote: Hi, Penny- I started working on a solution for you and got sidetracked (my boss is so impetuous that way). Your earlier post said you're working with Bates numbering- are your document numbers contiguous, or do they have gaps? Also, by any chance are the documents related to a client entered as two numbers in two cells (the lowest document number and the highest document number), or are they entered in a single cell in the format 12003-12212? More globally speaking, do you have the latitude within your organization to re-arrange the data if you need to, in order to make this lookup idea possible? DaveO |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Entering a range of numbers and searching same
Just a sample:
Lets say you have a row for each block of documents, with the number of the first doc in col A and the number of the last doc in column B and the Client name in column C: 1 10 client 1 20 25 client 2 60 62 client 3 33 37 client 2 So docs numbered 1 thru 10 are assign to client 1, etc. First enter this small macro: Sub Macro1() Dim r As Range Dim j, k, l As Long Set r = Intersect(ActiveSheet.UsedRange, Columns("A:A")) j = r.Rows.Count k = --InputBox("Enter document number:") For l = 1 To j If k = Cells(l, 1).Value Then If k <= Cells(l, 2).Value Then MsgBox ("Document Number " & k & " Client " & Cells(l, 3).Value) Exit Sub End If End If Next MsgBox ("Document Number " & k & " not assigned") End Sub When run, the macro will ask for a document number and return the client name. If the number entered is not assigned, the macro will tell you so. -- Gary's Student "Penny" wrote: Dave - You are such a sweetheart! Thank you. Sorry to have reposted. I don't post often enough to know how best to do this. And how dare that boss of yours! Aren't they all the same way. I'm still using docs with Bates numbers. For the lack of a better explanation from me, I'm trying to inventory all the docs. I do have complete control over the data and how it is entered and manipulated. For now, I have the bates number data in one field, but that can be changed. Some records have one doc, some have hundreds within the field. Should that be my first step, separate the beginning and ending of the range of docs? Hope this isn't too much info. I have not had enough caffeine this morning. Thanks again for your time. It is so very much appreciated. Penny "Dave O" wrote: Hi, Penny- I started working on a solution for you and got sidetracked (my boss is so impetuous that way). Your earlier post said you're working with Bates numbering- are your document numbers contiguous, or do they have gaps? Also, by any chance are the documents related to a client entered as two numbers in two cells (the lowest document number and the highest document number), or are they entered in a single cell in the format 12003-12212? More globally speaking, do you have the latitude within your organization to re-arrange the data if you need to, in order to make this lookup idea possible? DaveO |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Entering a range of numbers and searching same
No such thing as too much caffeine. I forgot to ask: are your Bates
numbers *always* numeric, or might they have alpha characters? By any chance do the clients have account numbers that are also *always* numeric? Is it possible that a client might have document numbers from 1 - 50, 60-75, and 1002-1009, for example? Separating the start number and end number of a given range will likely be helpful, but we don't have to decide that yet. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Entering a range of numbers and searching same
First, I would separate your numbers into two columns, say A and B, then on
cell A1, do Format ConditionalFormat FormulaIs and in the right window, put =AND($A1<=$G$1,$B1=$G$1) and set the Format as Patterns Green, or whatever.........then just type the number you're looking for in Cell G1 and the correct combination of cells will light up...........clearcontents on G1 and they go white again............of course, you can use any unused cell in place of G1, just change the formula accordingly........ hth Vaya con Dios, Chuck, CABGx3 "Penny" wrote: I'm working with numbered documents. Some clients have a range of documents (12003-12212) I want to search for document 12,198 and pull up the specific client tied to that document number. Know of any possible way to make this happen? Thanks again for your time and assistance. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Entering a range of numbers and searching same
NEVER too much caffeine. Grabbed some while I was eagerly awaiting another
response. :-) Since my post yesterday, I've made things even more complicated (not by choice!) I'm going to have two distinct bates stamps for each document. And they do have alphas in them. (One set from Respondent and one from Claimant - some overlap between docs.) For now, I have the alpha characters in a sep field. And I did correct the range within one field, made it two sep fields. I do have account numbers that are alpha and numeric. The account numbers all start with a number. You are correct on the client example. If it matters, there are single docs in the range as well. Example: Brewer has docs labeled 5319, 5325-5898, 6315-6594, etc. Can't thank you enough. I battled it for too long before turning to you guys for help. "Dave O" wrote: No such thing as too much caffeine. I forgot to ask: are your Bates numbers *always* numeric, or might they have alpha characters? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Entering a range of numbers and searching same
You guys are amazing. Thanks so much. I'm trying this now. I really
appreciate it. Penny "Gary''s Student" wrote: Just a sample: Lets say you have a row for each block of documents, with the number of the first doc in col A and the number of the last doc in column B and the Client name in column C: 1 10 client 1 20 25 client 2 60 62 client 3 33 37 client 2 So docs numbered 1 thru 10 are assign to client 1, etc. First enter this small macro: Sub Macro1() Dim r As Range Dim j, k, l As Long Set r = Intersect(ActiveSheet.UsedRange, Columns("A:A")) j = r.Rows.Count k = --InputBox("Enter document number:") For l = 1 To j If k = Cells(l, 1).Value Then If k <= Cells(l, 2).Value Then MsgBox ("Document Number " & k & " Client " & Cells(l, 3).Value) Exit Sub End If End If Next MsgBox ("Document Number " & k & " not assigned") End Sub When run, the macro will ask for a document number and return the client name. If the number entered is not assigned, the macro will tell you so. -- Gary's Student "Penny" wrote: Dave - You are such a sweetheart! Thank you. Sorry to have reposted. I don't post often enough to know how best to do this. And how dare that boss of yours! Aren't they all the same way. I'm still using docs with Bates numbers. For the lack of a better explanation from me, I'm trying to inventory all the docs. I do have complete control over the data and how it is entered and manipulated. For now, I have the bates number data in one field, but that can be changed. Some records have one doc, some have hundreds within the field. Should that be my first step, separate the beginning and ending of the range of docs? Hope this isn't too much info. I have not had enough caffeine this morning. Thanks again for your time. It is so very much appreciated. Penny "Dave O" wrote: Hi, Penny- I started working on a solution for you and got sidetracked (my boss is so impetuous that way). Your earlier post said you're working with Bates numbering- are your document numbers contiguous, or do they have gaps? Also, by any chance are the documents related to a client entered as two numbers in two cells (the lowest document number and the highest document number), or are they entered in a single cell in the format 12003-12212? More globally speaking, do you have the latitude within your organization to re-arrange the data if you need to, in order to make this lookup idea possible? DaveO |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Entering a range of numbers and searching same
Thank you Chuck for your response. I'm giving this a try now. I really
appreciate all of the help. Best wishes, Penny "CLR" wrote: First, I would separate your numbers into two columns, say A and B, then on cell A1, do Format ConditionalFormat FormulaIs and in the right window, put =AND($A1<=$G$1,$B1=$G$1) and set the Format as Patterns Green, or whatever.........then just type the number you're looking for in Cell G1 and the correct combination of cells will light up...........clearcontents on G1 and they go white again............of course, you can use any unused cell in place of G1, just change the formula accordingly........ hth Vaya con Dios, Chuck, CABGx3 "Penny" wrote: I'm working with numbered documents. Some clients have a range of documents (12003-12212) I want to search for document 12,198 and pull up the specific client tied to that document number. Know of any possible way to make this happen? Thanks again for your time and assistance. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Entering a range of numbers and searching same
Penny-
In retrospect it probably was a good idea to separate the beginning and ending document numbers, since it will make the search and comparison easier. For clients with only one document number, the beginning and ending doc numbers will match. The responses above from Chuck and Gary's Student will work where the document numbers are purely numeric with no alpha characters. How much trouble is it to keep the alphas separate from the numerics? |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Entering a range of numbers and searching same
Dave (and Chuck and Gary's Student),
I kept the original entry with the range in one field. I do have it separate now as well. Obviously, I wasn't thinking when I entered them in one. I have kept the alpha characters out for now. That can be worked around. I'm trying both of their solutions now. I was having trouble with Chuck's option (user error) and I just made it happen! Yeah!!! You guys are soooo wonderful! I still want to try the solution from Gary's Student. I love learning new things. A question though, the Conditional Format gives me the exact field formatted how I want it... and then I scroll through the doc to find the one selected. Is there a way to have the cursor move to that field? Sorry if I'm asking too much. Just really excited to have made this progress. Thanks to all of you!!!! Penny "Dave O" wrote: Penny- In retrospect it probably was a good idea to separate the beginning and ending document numbers, since it will make the search and comparison easier. For clients with only one document number, the beginning and ending doc numbers will match. The responses above from Chuck and Gary's Student will work where the document numbers are purely numeric with no alpha characters. How much trouble is it to keep the alphas separate from the numerics? |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Entering a range of numbers and searching same
Hi Penny........glad to have been of some bit of help..........please
clarify your statement for me... "the Conditional Format gives me the exact field formatted how I want it... and then I scroll through the doc to find the one selected. " Exactly what does "scroll through the doc" mean.....are all the numbers listed across the same ROW that the CF found?......... tks Vaya con Dios, Chuck, CABGx3 "Penny" wrote: Dave (and Chuck and Gary's Student), I kept the original entry with the range in one field. I do have it separate now as well. Obviously, I wasn't thinking when I entered them in one. I have kept the alpha characters out for now. That can be worked around. I'm trying both of their solutions now. I was having trouble with Chuck's option (user error) and I just made it happen! Yeah!!! You guys are soooo wonderful! I still want to try the solution from Gary's Student. I love learning new things. A question though, the Conditional Format gives me the exact field formatted how I want it... and then I scroll through the doc to find the one selected. Is there a way to have the cursor move to that field? Sorry if I'm asking too much. Just really excited to have made this progress. Thanks to all of you!!!! Penny "Dave O" wrote: Penny- In retrospect it probably was a good idea to separate the beginning and ending document numbers, since it will make the search and comparison easier. For clients with only one document number, the beginning and ending doc numbers will match. The responses above from Chuck and Gary's Student will work where the document numbers are purely numeric with no alpha characters. How much trouble is it to keep the alphas separate from the numerics? |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Entering a range of numbers and searching same
That makes NO sense!!!!
What I mean to say ... the Conditional Format finds the record for me. Formats it exactly how you showed me to do so. Once it does this, I manually page up/down to scroll through the doc and find that record. I was asking if there was a way to tell it to go to that record/field it has found/formatted. Sorry for the confusion. Thanks for asking me to clarify. Penny "CLR" wrote: Hi Penny........glad to have been of some bit of help..........please clarify your statement for me... "the Conditional Format gives me the exact field formatted how I want it... and then I scroll through the doc to find the one selected. " |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Entering a range of numbers and searching same
If I recall correctly, Penny needs to go to the row that contains the
document number so she can click on a link to the document. That's why she needs to physically move to that cell. Penny, did you have any luck running Gary's macro? If so we can amend it to move to the cell in question. |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Entering a range of numbers and searching same
Cool.........gotcha now.....
Here's a mod on Gary's code that will locate the cursor to the cell just to the right of the second column (where possibly your link to the doc yo're after may be)..........it's set up to be used in conjunction with the Conditional Formatting thing I gave you yesterday.....just typing your Doc number in the InputBox that pops up will automatically put it in the cell G1 and control the CF......... Sub FindDoc() Dim r As Range Dim j, k, l As Long Set r = Intersect(ActiveSheet.UsedRange, Columns("A:A")) j = r.Rows.Count k = --InputBox("Enter document number:") Range("g1").Value = k For l = 1 To j If k = Cells(l, 1).Value Then If k <= Cells(l, 2).Value Then Cells(l, 3).Select ' MsgBox ("Document Number " & k & " Client " & Cells(l, 3).Value) Exit Sub End If End If Next MsgBox ("Document Number " & k & " not assigned") End Sub hth Vaya con Dios Chuck, CABGx3 "Penny" wrote: That makes NO sense!!!! What I mean to say ... the Conditional Format finds the record for me. Formats it exactly how you showed me to do so. Once it does this, I manually page up/down to scroll through the doc and find that record. I was asking if there was a way to tell it to go to that record/field it has found/formatted. Sorry for the confusion. Thanks for asking me to clarify. Penny "CLR" wrote: Hi Penny........glad to have been of some bit of help..........please clarify your statement for me... "the Conditional Format gives me the exact field formatted how I want it... and then I scroll through the doc to find the one selected. " |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
Entering a range of numbers and searching same
You guys are amazing. I'm working with Gary's code now. I was out of the
office this AM... sorry for not responding before now. Thank you so very much!!! I couldn't have made it this far without you. Hope to post great news soon! Penny "CLR" wrote: Cool.........gotcha now..... Here's a mod on Gary's code that will locate the cursor to the cell just to the right of the second column (where possibly your link to the doc yo're after may be)..........it's set up to be used in conjunction with the Conditional Formatting thing I gave you yesterday.....just typing your Doc number in the InputBox that pops up will automatically put it in the cell G1 and control the CF......... Sub FindDoc() Dim r As Range Dim j, k, l As Long Set r = Intersect(ActiveSheet.UsedRange, Columns("A:A")) j = r.Rows.Count k = --InputBox("Enter document number:") Range("g1").Value = k For l = 1 To j If k = Cells(l, 1).Value Then If k <= Cells(l, 2).Value Then Cells(l, 3).Select ' MsgBox ("Document Number " & k & " Client " & Cells(l, 3).Value) Exit Sub End If End If Next MsgBox ("Document Number " & k & " not assigned") End Sub hth Vaya con Dios Chuck, CABGx3 "Penny" wrote: That makes NO sense!!!! What I mean to say ... the Conditional Format finds the record for me. Formats it exactly how you showed me to do so. Once it does this, I manually page up/down to scroll through the doc and find that record. I was asking if there was a way to tell it to go to that record/field it has found/formatted. Sorry for the confusion. Thanks for asking me to clarify. Penny "CLR" wrote: Hi Penny........glad to have been of some bit of help..........please clarify your statement for me... "the Conditional Format gives me the exact field formatted how I want it... and then I scroll through the doc to find the one selected. " |
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
Entering a range of numbers and searching same
Let us know!
|
#18
Posted to microsoft.public.excel.misc
|
|||
|
|||
Entering a range of numbers and searching same
It's me again!!! Ugh! I have Chuck's latest code entered and I'm having a
little trouble with it. (User error on my part, I'm certain.) When I run the Macro, it does find the document number and it does format it as I have specified. It does not go to that specific cell/row that it found. It also gives me the message "Document Number XX not assigned" even when it is assigned. Sorry to bother you guys, again and again. If you have any suggestions for me, that would be so wonderful. Thanks for all of your help. Penny "Dave O" wrote: Let us know! |
#19
Posted to microsoft.public.excel.misc
|
|||
|
|||
Entering a range of numbers and searching same
Hi, Penny-
When I mocked up some sample data and used Chuck's code it worked properly- IF the list of documents started in cell A1. By any chance does your document list start in a cell other than A1? Dave O |
#20
Posted to microsoft.public.excel.misc
|
|||
|
|||
Entering a range of numbers and searching same
Forgot to add that it's Ok if your data starts on a different row-
we'll just modify the code a bit, no worries. |
#21
Posted to microsoft.public.excel.misc
|
|||
|
|||
Entering a range of numbers and searching same
I'm wrong again. The code works even with gaps at the top (I was
entering an invalid doc number). The code works properly, so there must be something else going on. Does it work properly sometimes, and not others? |
#22
Posted to microsoft.public.excel.misc
|
|||
|
|||
Entering a range of numbers and searching same
That would make life too easy for me (and you!) It doesn't start in cell A1.
The beginning range of numbers starts in D2. Ending Range in E2. (I have a header row). And R1 is where I have the field that I type in the document number I'm searching for. This is what I have as of now: Sub FindDoc() Dim r As Range Dim j, k, l As Long Set r = Intersect(ActiveSheet.UsedRange, Columns("A:A")) j = r.Rows.Count k = --InputBox("Enter document number:") Range("r1").Value = k For l = 1 To j If k = Cells(l, 1).Value Then If k <= Cells(l, 2).Value Then Cells(l, 3).Select MsgBox ("Document Number " & k & " Client " & Cells(l, 3).Value) Exit Sub End If End If Next MsgBox ("Document Number " & k & " not assigned") End Sub Thanks for being so patient with me. I'm learning but it's not quick enough for me! Penny "Dave O" wrote: Forgot to add that it's Ok if your data starts on a different row- we'll just modify the code a bit, no worries. |
#23
Posted to microsoft.public.excel.misc
|
|||
|
|||
Entering a range of numbers and searching same
Hello again! I just realized something. It doesn't give me the "doc number
not assigned" if I'm looking up a number that is in the beginning or in the ending range. But it does give me the message if it's a number within the range. (Hope I'm making sense?) If it's a number within the range, it does format it as specified. With either outcome, it doesn't automatically go to that row. Thanks again for all of your time. Penny |
#24
Posted to microsoft.public.excel.misc
|
|||
|
|||
Entering a range of numbers and searching same
Try this instead. We should be getting close, now!
Sub FindDoc() Dim r As Range Dim j, k, l As Long Set r = Intersect(ActiveSheet.UsedRange, Columns("d:d")) j = r.Rows.Count k = --InputBox("Enter document number:") Range("r1").Value = k For l = 1 To j If k = Cells(l, 4).Value Then If k <= Cells(l, 5).Value Then Cells(l, 6).Select MsgBox ("Document Number " & k & " Client " & Cells(l, 6).Value) Exit Sub End If End If Next MsgBox ("Document Number " & k & " not assigned") End Sub |
#25
Posted to microsoft.public.excel.misc
|
|||
|
|||
Entering a range of numbers and searching same
Absolutely Amazing!!!! It works and works wonderfully!!!! Thank you so much
for all of your help. Can't say it enough. Wish I could return the favor. I am so grateful. "Dave O" wrote: Try this instead. We should be getting close, now! Sub FindDoc() Dim r As Range Dim j, k, l As Long Set r = Intersect(ActiveSheet.UsedRange, Columns("d:d")) j = r.Rows.Count k = --InputBox("Enter document number:") Range("r1").Value = k For l = 1 To j If k = Cells(l, 4).Value Then If k <= Cells(l, 5).Value Then Cells(l, 6).Select MsgBox ("Document Number " & k & " Client " & Cells(l, 6).Value) Exit Sub End If End If Next MsgBox ("Document Number " & k & " not assigned") End Sub |
#26
Posted to microsoft.public.excel.misc
|
|||
|
|||
Entering a range of numbers and searching same
Have to thank Chuck for that in a big way. Glad it worked out for you!
|
#27
Posted to microsoft.public.excel.misc
|
|||
|
|||
Entering a range of numbers and searching same
I meant to send that as a thanks to all of you. I really do appreciate it.
Hope you have a great and relaxing afternoon! Penny "Dave O" wrote: Have to thank Chuck for that in a big way. Glad it worked out for you! |
#28
Posted to microsoft.public.excel.misc
|
|||
|
|||
Entering a range of numbers and searching same
Thanks for the thanks, Penny and Dave O. I've been home sick today, sorry I
missed out on the fun. Glad it's all working for you Penny........and, you done good Dave O. Vaya con Dios, Chuck, CABGx3 "Penny" wrote in message ... I meant to send that as a thanks to all of you. I really do appreciate it. Hope you have a great and relaxing afternoon! Penny "Dave O" wrote: Have to thank Chuck for that in a big way. Glad it worked out for you! |
#29
Posted to microsoft.public.excel.misc
|
|||
|
|||
Entering a range of numbers and searching same
This is starting to feel like a group hug. Yuk!
Kidding, of course. Chuck, hope you're feeling better. Penny, hope you had a good experience with the Usenet- let us know if we can help with anything else. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Working with range of numbers in a cell and searching same | Excel Discussion (Misc queries) | |||
Working with range of numbers in a cell and searching same | Excel Worksheet Functions | |||
searching a range for a sum... | Excel Worksheet Functions | |||
Searching a range of numbers for a specified total | Excel Worksheet Functions | |||
Searching a Range for a number over 40 | Excel Worksheet Functions |