lookup functions
 

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.

lookup functions
 

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" wrote in message
...
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.

lookup functions
 

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" wrote in message
...
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.

lookup functions
 

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" wrote in message
...
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" wrote in message
...
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.

lookup functions
 

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" wrote in message
...
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" wrote in message
...
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.