lookup functions

[Brent]

Hi
I have a worksheet the uses the lookup function to fill in some blanks on a
form
ie: name, address, city,state and other information that is located on sheet
2 of the workbook. This seems to work ok, My question is this
is there a way to add the above information to sheet if it does not all
ready on the steet?
ie looking for JOHN and JOHN does not exsist on sheet to.
hope you understand my question.
thank you for any help you may give. if there is a better way to accomplish
this i an open to any sugestions.

[Tom Ogilvy]

If John doesn't exist and you filled in the information in the cells, you
would then overwrite your lookup formulas - so I don't see a dual purpose
form if you are going to use Lookup.

You could have a separate form for data entry and use code to write the
information to your database.

--
Regards,
Tom Ogilvy


"Brent" <Brent@discussions.microsoft.com> wrote in message
news:D5E44568-CE0E-420C-A831-7870ADB69B23@microsoft.com...
> Hi
> I have a worksheet the uses the lookup function to fill in some blanks on
a
> form
> ie: name, address, city,state and other information that is located on
sheet
> 2 of the workbook. This seems to work ok, My question is this
> is there a way to add the above information to sheet if it does not all
> ready on the steet?
> ie looking for JOHN and JOHN does not exsist on sheet to.
> hope you understand my question.
> thank you for any help you may give. if there is a better way to
accomplish
> this i an open to any sugestions.

[doc53]

I need to restate my question.
in a work sheet I have cell A1= a name, b1 is an address that is obtained by
using a lookup. the formula is as follows in b1 =lookup(a1,sheet2!a1:a100,b1)
sheet two has the following information on it. a1-100 are names, b1-b100
are addresses. What I want to know is if there is a way to make a name typed
in a1 of sheet one take the next empty space on sheet 2 cells a1-axx if it is
not allready there. ie I type in John in cell a1 on sheet one and john say
is on sheet two in a1 the address in b1 is returned to b1 on sheet 1, but if
john does is not in the list on sheet 2 and the corsponding address then it
would be added from the eneries on page one.

"Tom Ogilvy" wrote:

> If John doesn't exist and you filled in the information in the cells, you
> would then overwrite your lookup formulas - so I don't see a dual purpose
> form if you are going to use Lookup.
>
> You could have a separate form for data entry and use code to write the
> information to your database.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Brent" <Brent@discussions.microsoft.com> wrote in message
> news:D5E44568-CE0E-420C-A831-7870ADB69B23@microsoft.com...
> > Hi
> > I have a worksheet the uses the lookup function to fill in some blanks on
> a
> > form
> > ie: name, address, city,state and other information that is located on
> sheet
> > 2 of the workbook. This seems to work ok, My question is this
> > is there a way to add the above information to sheet if it does not all
> > ready on the steet?
> > ie looking for JOHN and JOHN does not exsist on sheet to.
> > hope you understand my question.
> > thank you for any help you may give. if there is a better way to
> accomplish
> > this i an open to any sugestions.
>
>
>

[Tom Ogilvy]

Right click on the sheet tab of sheet1 and select view code.

Paste in code like the below.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng as Range
if Iserror(Range("B1")) then
set rng = worksheets("Sheet1").Cells(rows.count,1).End(xlup))(2)
rng.Value = Range("A1")
elseif len(trim(Range("B1").Text)) = 0 then
set rng = worksheets("Sheet1").Cells(rows.count,1).End(xlup))(2)
rng.Value = Range("A1")
End if
End Sub


--
Regards,
Tom Ogilvy


"doc53" <doc53@discussions.microsoft.com> wrote in message
news:E45BDC2E-51AE-40C4-AF95-35529C57C964@microsoft.com...
> I need to restate my question.
> in a work sheet I have cell A1= a name, b1 is an address that is obtained
by
> using a lookup. the formula is as follows in b1
=lookup(a1,sheet2!a1:a100,b1)
> sheet two has the following information on it. a1-100 are names, b1-b100
> are addresses. What I want to know is if there is a way to make a name
typed
> in a1 of sheet one take the next empty space on sheet 2 cells a1-axx if it
is
> not allready there. ie I type in John in cell a1 on sheet one and john
say
> is on sheet two in a1 the address in b1 is returned to b1 on sheet 1, but
if
> john does is not in the list on sheet 2 and the corsponding address then
it
> would be added from the eneries on page one.
>
> "Tom Ogilvy" wrote:
>
> > If John doesn't exist and you filled in the information in the cells,
you
> > would then overwrite your lookup formulas - so I don't see a dual
purpose
> > form if you are going to use Lookup.
> >
> > You could have a separate form for data entry and use code to write the
> > information to your database.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Brent" <Brent@discussions.microsoft.com> wrote in message
> > news:D5E44568-CE0E-420C-A831-7870ADB69B23@microsoft.com...
> > > Hi
> > > I have a worksheet the uses the lookup function to fill in some blanks
on
> > a
> > > form
> > > ie: name, address, city,state and other information that is located on
> > sheet
> > > 2 of the workbook. This seems to work ok, My question is this
> > > is there a way to add the above information to sheet if it does not
all
> > > ready on the steet?
> > > ie looking for JOHN and JOHN does not exsist on sheet to.
> > > hope you understand my question.
> > > thank you for any help you may give. if there is a better way to
> > accomplish
> > > this i an open to any sugestions.
> >
> >
> >

[doc53]

I have tryed this but get a error when i try to enter a name so i must not be
puting in the right values in the formula you gave me. can you explain what
your code is looking for and what is does? which line sets the value to
worksheet 2 and which line reads the value from worksheet 2?

"Tom Ogilvy" wrote:

> Right click on the sheet tab of sheet1 and select view code.
>
> Paste in code like the below.
>
> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> Dim rng as Range
> if Iserror(Range("B1")) then
> set rng = worksheets("Sheet1").Cells(rows.count,1).End(xlup))(2)
> rng.Value = Range("A1")
> elseif len(trim(Range("B1").Text)) = 0 then
> set rng = worksheets("Sheet1").Cells(rows.count,1).End(xlup))(2)
> rng.Value = Range("A1")
> End if
> End Sub
>
>
> --
> Regards,
> Tom Ogilvy
>
>
> "doc53" <doc53@discussions.microsoft.com> wrote in message
> news:E45BDC2E-51AE-40C4-AF95-35529C57C964@microsoft.com...
> > I need to restate my question.
> > in a work sheet I have cell A1= a name, b1 is an address that is obtained
> by
> > using a lookup. the formula is as follows in b1
> =lookup(a1,sheet2!a1:a100,b1)
> > sheet two has the following information on it. a1-100 are names, b1-b100
> > are addresses. What I want to know is if there is a way to make a name
> typed
> > in a1 of sheet one take the next empty space on sheet 2 cells a1-axx if it
> is
> > not allready there. ie I type in John in cell a1 on sheet one and john
> say
> > is on sheet two in a1 the address in b1 is returned to b1 on sheet 1, but
> if
> > john does is not in the list on sheet 2 and the corsponding address then
> it
> > would be added from the eneries on page one.
> >
> > "Tom Ogilvy" wrote:
> >
> > > If John doesn't exist and you filled in the information in the cells,
> you
> > > would then overwrite your lookup formulas - so I don't see a dual
> purpose
> > > form if you are going to use Lookup.
> > >
> > > You could have a separate form for data entry and use code to write the
> > > information to your database.
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "Brent" <Brent@discussions.microsoft.com> wrote in message
> > > news:D5E44568-CE0E-420C-A831-7870ADB69B23@microsoft.com...
> > > > Hi
> > > > I have a worksheet the uses the lookup function to fill in some blanks
> on
> > > a
> > > > form
> > > > ie: name, address, city,state and other information that is located on
> > > sheet
> > > > 2 of the workbook. This seems to work ok, My question is this
> > > > is there a way to add the above information to sheet if it does not
> all
> > > > ready on the steet?
> > > > ie looking for JOHN and JOHN does not exsist on sheet to.
> > > > hope you understand my question.
> > > > thank you for any help you may give. if there is a better way to
> > > accomplish
> > > > this i an open to any sugestions.
> > >
> > >
> > >
>
>
>