Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup functions | Excel Worksheet Functions | |||
Help on lookup functions | Excel Discussion (Misc queries) | |||
Lookup and IF functions I think?? Need Help!!!! | Excel Discussion (Misc queries) | |||
If ,AND, LOOKUP, Functions | Excel Worksheet Functions | |||
Lookup functions | Excel Discussion (Misc queries) |