software

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.

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?

DaveODave - 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

quot;Dave Oquot; wrote:

gt; Hi, Penny-
gt; I started working on a solution for you and got sidetracked (my boss is
gt; so impetuous that way). Your earlier post said you're working with
gt; Bates numbering- are your document numbers contiguous, or do they have
gt; gaps? Also, by any chance are the documents related to a client
gt; entered as two numbers in two cells (the lowest document number and the
gt; highest document number), or are they entered in a single cell in the
gt; format 12003-12212?
gt;
gt; More globally speaking, do you have the latitude within your
gt; organization to re-arrange the data if you need to, in order to make
gt; this lookup idea possible?
gt;
gt; DaveO
gt;
gt;

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:

110client 1
2025client 2
6062client 3
3337client 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(quot;A:Aquot;))

j = r.Rows.Count
k = --InputBox(quot;Enter document number:quot;)

For l = 1 To j
If k gt;= Cells(l, 1).Value Then
If k lt;= Cells(l, 2).Value Then
MsgBox (quot;Document Number quot; amp; k amp; quot; Client quot; amp; Cells(l, 3).Value)
Exit Sub
End If
End If
Next
MsgBox (quot;Document Number quot; amp; k amp; quot; not assignedquot;)
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 Studentquot;Pennyquot; wrote:

gt; Dave - You are such a sweetheart! Thank you. Sorry to have reposted. I
gt; don't post often enough to know how best to do this. And how dare that boss
gt; of yours! Aren't they all the same way.
gt;
gt; I'm still using docs with Bates numbers. For the lack of a better
gt; explanation from me, I'm trying to inventory all the docs. I do have
gt; complete control over the data and how it is entered and manipulated. For
gt; now, I have the bates number data in one field, but that can be changed.
gt; Some records have one doc, some have hundreds within the field. Should that
gt; be my first step, separate the beginning and ending of the range of docs?
gt;
gt; Hope this isn't too much info. I have not had enough caffeine this morning.
gt; Thanks again for your time. It is so very much appreciated.
gt;
gt; Penny
gt;
gt; quot;Dave Oquot; wrote:
gt;
gt; gt; Hi, Penny-
gt; gt; I started working on a solution for you and got sidetracked (my boss is
gt; gt; so impetuous that way). Your earlier post said you're working with
gt; gt; Bates numbering- are your document numbers contiguous, or do they have
gt; gt; gaps? Also, by any chance are the documents related to a client
gt; gt; entered as two numbers in two cells (the lowest document number and the
gt; gt; highest document number), or are they entered in a single cell in the
gt; gt; format 12003-12212?
gt; gt;
gt; gt; More globally speaking, do you have the latitude within your
gt; gt; organization to re-arrange the data if you need to, in order to make
gt; gt; this lookup idea possible?
gt; gt;
gt; gt; DaveO
gt; gt;
gt; gt;

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.First, I would separate your numbers into two columns, say A and B, then on
cell A1, do Format gt; ConditionalFormat gt; FormulaIs gt; and in the right window,
put
=AND($A1lt;=$G$1,$B1gt;=$G$1) gt; and set the Format as Patterns gt; 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

quot;Pennyquot; wrote:

gt; I'm working with numbered documents. Some clients have a range of documents
gt; (12003-12212) I want to search for document 12,198 and pull up the specific
gt; client tied to that document number.
gt;
gt; Know of any possible way to make this happen? Thanks again for your time
gt; and assistance.

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.

quot;Dave Oquot; wrote:

gt; No such thing as too much caffeine. I forgot to ask: are your Bates
gt; numbers *always* numeric, or might they have alpha characters?You guys are amazing. Thanks so much. I'm trying this now. I really
appreciate it.

Penny

quot;Gary''s Studentquot; wrote:

gt; Just a sample:
gt;
gt; Lets say you have a row for each block of documents, with the number of the
gt; first doc in col A and the number of the last doc in column B and the Client
gt; name in column C:
gt;
gt; 110client 1
gt; 2025client 2
gt; 6062client 3
gt; 3337client 2
gt;
gt; So docs numbered 1 thru 10 are assign to client 1, etc.
gt;
gt; First enter this small macro:
gt;
gt; Sub Macro1()
gt; Dim r As Range
gt; Dim j, k, l As Long
gt; Set r = Intersect(ActiveSheet.UsedRange, Columns(quot;A:Aquot;))
gt;
gt; j = r.Rows.Count
gt; k = --InputBox(quot;Enter document number:quot;)
gt;
gt; For l = 1 To j
gt; If k gt;= Cells(l, 1).Value Then
gt; If k lt;= Cells(l, 2).Value Then
gt; MsgBox (quot;Document Number quot; amp; k amp; quot; Client quot; amp; Cells(l, 3).Value)
gt; Exit Sub
gt; End If
gt; End If
gt; Next
gt; MsgBox (quot;Document Number quot; amp; k amp; quot; not assignedquot;)
gt; End Sub
gt;
gt; When run, the macro will ask for a document number and return the client
gt; name. If the number entered is not assigned, the macro will tell you so.
gt; --
gt; Gary's Student
gt;
gt;
gt; quot;Pennyquot; wrote:
gt;
gt; gt; Dave - You are such a sweetheart! Thank you. Sorry to have reposted. I
gt; gt; don't post often enough to know how best to do this. And how dare that boss
gt; gt; of yours! Aren't they all the same way.
gt; gt;
gt; gt; I'm still using docs with Bates numbers. For the lack of a better
gt; gt; explanation from me, I'm trying to inventory all the docs. I do have
gt; gt; complete control over the data and how it is entered and manipulated. For
gt; gt; now, I have the bates number data in one field, but that can be changed.
gt; gt; Some records have one doc, some have hundreds within the field. Should that
gt; gt; be my first step, separate the beginning and ending of the range of docs?
gt; gt;
gt; gt; Hope this isn't too much info. I have not had enough caffeine this morning.
gt; gt; Thanks again for your time. It is so very much appreciated.
gt; gt;
gt; gt; Penny
gt; gt;
gt; gt; quot;Dave Oquot; wrote:
gt; gt;
gt; gt; gt; Hi, Penny-
gt; gt; gt; I started working on a solution for you and got sidetracked (my boss is
gt; gt; gt; so impetuous that way). Your earlier post said you're working with
gt; gt; gt; Bates numbering- are your document numbers contiguous, or do they have
gt; gt; gt; gaps? Also, by any chance are the documents related to a client
gt; gt; gt; entered as two numbers in two cells (the lowest document number and the
gt; gt; gt; highest document number), or are they entered in a single cell in the
gt; gt; gt; format 12003-12212?
gt; gt; gt;
gt; gt; gt; More globally speaking, do you have the latitude within your
gt; gt; gt; organization to re-arrange the data if you need to, in order to make
gt; gt; gt; this lookup idea possible?
gt; gt; gt;
gt; gt; gt; DaveO
gt; gt; gt;
gt; gt; gt;

Thank you Chuck for your response. I'm giving this a try now. I really
appreciate all of the help.

Best wishes,
Penny

quot;CLRquot; wrote:

gt; First, I would separate your numbers into two columns, say A and B, then on
gt; cell A1, do Format gt; ConditionalFormat gt; FormulaIs gt; and in the right window,
gt; put
gt; =AND($A1lt;=$G$1,$B1gt;=$G$1) gt; and set the Format as Patterns gt; Green, or
gt; whatever.........then just type the number you're looking for in Cell G1 and
gt; the correct combination of cells will light up...........clearcontents on G1
gt; and they go white again............of course, you can use any unused cell in
gt; place of G1, just change the formula accordingly........
gt;
gt; hth
gt; Vaya con Dios,
gt; Chuck, CABGx3
gt;
gt;
gt;
gt;
gt; quot;Pennyquot; wrote:
gt;
gt; gt; I'm working with numbered documents. Some clients have a range of documents
gt; gt; (12003-12212) I want to search for document 12,198 and pull up the specific
gt; gt; client tied to that document number.
gt; gt;
gt; gt; Know of any possible way to make this happen? Thanks again for your time
gt; gt; and assistance.

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?