| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto populate from list/source
Sheet 1 contains bldg name(column A), bldg street address(column B),
City(column C) etc. Sheet 2 is a job/project log. I would like the user to type in only the bldg name in column A and have the other columns auto populate in the respective columns in sheet 2. How can this be accomplished? Thx! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Auto populate from list/source
Sorry, auto complete for bldg name in sheet 2. For instance if you entered in
B2 of sheet 2, the letter "a" it would filter the bldg names list to only those beginning with "a" etc. until you choose from a handful of bldg names. Thx again! "Ron Coderre" wrote: AutoFilter/Autocomplete on which cells/sheet? *********** Regards, Ron XL2003, WinXP "James" wrote: Got it...Thanks!...now is there any way for bldg name to auto complete or auto filter? "Ron Coderre" wrote: Try something like this on Sheet2: B2: (a bldg name) The street C2: =VLOOKUP($B2,Sheet1!$A:$E,2,0) The city D2: =VLOOKUP($B2,Sheet1!$A:$E,3,0) etc OR.... C2: =VLOOKUP($B2,Sheet1!$A:$E,COLUMNS($A2:B2),0) copy C2 across to the right.....each successive cell will return more of the address. Is that something you can work with? *********** Regards, Ron XL2003, WinXP "James" wrote: Sheet 1 contains bldg name(column A), bldg street address(column B), City(column C) etc. Sheet 2 is a job/project log. I would like the user to type in only the bldg name in column A and have the other columns auto populate in the respective columns in sheet 2. How can this be accomplished? Thx! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Auto populate from list/source
AutoFilter/Autocomplete on which cells/sheet?
*********** Regards, Ron XL2003, WinXP "James" wrote: Got it...Thanks!...now is there any way for bldg name to auto complete or auto filter? "Ron Coderre" wrote: Try something like this on Sheet2: B2: (a bldg name) The street C2: =VLOOKUP($B2,Sheet1!$A:$E,2,0) The city D2: =VLOOKUP($B2,Sheet1!$A:$E,3,0) etc OR.... C2: =VLOOKUP($B2,Sheet1!$A:$E,COLUMNS($A2:B2),0) copy C2 across to the right.....each successive cell will return more of the address. Is that something you can work with? *********** Regards, Ron XL2003, WinXP "James" wrote: Sheet 1 contains bldg name(column A), bldg street address(column B), City(column C) etc. Sheet 2 is a job/project log. I would like the user to type in only the bldg name in column A and have the other columns auto populate in the respective columns in sheet 2. How can this be accomplished? Thx! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Auto populate from list/source
Got it...Thanks!...now is there any way for bldg name to auto complete or
auto filter? "Ron Coderre" wrote: Try something like this on Sheet2: B2: (a bldg name) The street C2: =VLOOKUP($B2,Sheet1!$A:$E,2,0) The city D2: =VLOOKUP($B2,Sheet1!$A:$E,3,0) etc OR.... C2: =VLOOKUP($B2,Sheet1!$A:$E,COLUMNS($A2:B2),0) copy C2 across to the right.....each successive cell will return more of the address. Is that something you can work with? *********** Regards, Ron XL2003, WinXP "James" wrote: Sheet 1 contains bldg name(column A), bldg street address(column B), City(column C) etc. Sheet 2 is a job/project log. I would like the user to type in only the bldg name in column A and have the other columns auto populate in the respective columns in sheet 2. How can this be accomplished? Thx! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Auto populate from list/source
Try something like this on Sheet2:
B2: (a bldg name) The street C2: =VLOOKUP($B2,Sheet1!$A:$E,2,0) The city D2: =VLOOKUP($B2,Sheet1!$A:$E,3,0) etc OR.... C2: =VLOOKUP($B2,Sheet1!$A:$E,COLUMNS($A2:B2),0) copy C2 across to the right.....each successive cell will return more of the address. Is that something you can work with? *********** Regards, Ron XL2003, WinXP "James" wrote: Sheet 1 contains bldg name(column A), bldg street address(column B), City(column C) etc. Sheet 2 is a job/project log. I would like the user to type in only the bldg name in column A and have the other columns auto populate in the respective columns in sheet 2. How can this be accomplished? Thx! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Auto populate from list/source
I'm baaack...now the problem is this. I'm cutting and pasting from a text
file into sheet 1. When I enter the information on sheet two it gives me a "value not available" error. If I type(overwrite) the exact same information in the same cell and go to sheet 2, the VLOOKUP formula works. I have 25000 records that I don't want to enter manually obviously. Any ideas? Thx! JR "Ron Coderre" wrote: You'd think Excel could do that automatically, right? See Debra Dalgleish's instructions: http://www.contextures.com/xlDataVal10.html Is that something you can work with? *********** Regards, Ron XL2003, WinXP "James" wrote: Sorry, auto complete for bldg name in sheet 2. For instance if you entered in B2 of sheet 2, the letter "a" it would filter the bldg names list to only those beginning with "a" etc. until you choose from a handful of bldg names. Thx again! "Ron Coderre" wrote: AutoFilter/Autocomplete on which cells/sheet? *********** Regards, Ron XL2003, WinXP "James" wrote: Got it...Thanks!...now is there any way for bldg name to auto complete or auto filter? "Ron Coderre" wrote: Try something like this on Sheet2: B2: (a bldg name) The street C2: =VLOOKUP($B2,Sheet1!$A:$E,2,0) The city D2: =VLOOKUP($B2,Sheet1!$A:$E,3,0) etc OR.... C2: =VLOOKUP($B2,Sheet1!$A:$E,COLUMNS($A2:B2),0) copy C2 across to the right.....each successive cell will return more of the address. Is that something you can work with? *********** Regards, Ron XL2003, WinXP "James" wrote: Sheet 1 contains bldg name(column A), bldg street address(column B), City(column C) etc. Sheet 2 is a job/project log. I would like the user to type in only the bldg name in column A and have the other columns auto populate in the respective columns in sheet 2. How can this be accomplished? Thx! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Auto populate from list/source
It seems the pasted value and the typed value are not really the same at all.
Try this: Type the correct value in a cell to the right of the pasted value. Compare the two values using someting like =EXACT(A2,G2)......it should return TRUE, but probably won't. Edit the pasted value and check for leading or trailing spaces or any other anomalies. Let us know what you find. *********** Regards, Ron XL2003, WinXP "James" wrote: I'm baaack...now the problem is this. I'm cutting and pasting from a text file into sheet 1. When I enter the information on sheet two it gives me a "value not available" error. If I type(overwrite) the exact same information in the same cell and go to sheet 2, the VLOOKUP formula works. I have 25000 records that I don't want to enter manually obviously. Any ideas? Thx! JR "Ron Coderre" wrote: You'd think Excel could do that automatically, right? See Debra Dalgleish's instructions: http://www.contextures.com/xlDataVal10.html Is that something you can work with? *********** Regards, Ron XL2003, WinXP "James" wrote: Sorry, auto complete for bldg name in sheet 2. For instance if you entered in B2 of sheet 2, the letter "a" it would filter the bldg names list to only those beginning with "a" etc. until you choose from a handful of bldg names. Thx again! "Ron Coderre" wrote: AutoFilter/Autocomplete on which cells/sheet? *********** Regards, Ron XL2003, WinXP "James" wrote: Got it...Thanks!...now is there any way for bldg name to auto complete or auto filter? "Ron Coderre" wrote: Try something like this on Sheet2: B2: (a bldg name) The street C2: =VLOOKUP($B2,Sheet1!$A:$E,2,0) The city D2: =VLOOKUP($B2,Sheet1!$A:$E,3,0) etc OR.... C2: =VLOOKUP($B2,Sheet1!$A:$E,COLUMNS($A2:B2),0) copy C2 across to the right.....each successive cell will return more of the address. Is that something you can work with? *********** Regards, Ron XL2003, WinXP "James" wrote: Sheet 1 contains bldg name(column A), bldg street address(column B), City(column C) etc. Sheet 2 is a job/project log. I would like the user to type in only the bldg name in column A and have the other columns auto populate in the respective columns in sheet 2. How can this be accomplished? Thx! |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Auto populate from list/source
Correct again! I used fixed width instead of delimited when importing and it
got rid of all trailing blanks/spaces. I'm on my way now! Thx Again! JR "Ron Coderre" wrote: It seems the pasted value and the typed value are not really the same at all. Try this: Type the correct value in a cell to the right of the pasted value. Compare the two values using someting like =EXACT(A2,G2)......it should return TRUE, but probably won't. Edit the pasted value and check for leading or trailing spaces or any other anomalies. Let us know what you find. *********** Regards, Ron XL2003, WinXP "James" wrote: I'm baaack...now the problem is this. I'm cutting and pasting from a text file into sheet 1. When I enter the information on sheet two it gives me a "value not available" error. If I type(overwrite) the exact same information in the same cell and go to sheet 2, the VLOOKUP formula works. I have 25000 records that I don't want to enter manually obviously. Any ideas? Thx! JR "Ron Coderre" wrote: You'd think Excel could do that automatically, right? See Debra Dalgleish's instructions: http://www.contextures.com/xlDataVal10.html Is that something you can work with? *********** Regards, Ron XL2003, WinXP "James" wrote: Sorry, auto complete for bldg name in sheet 2. For instance if you entered in B2 of sheet 2, the letter "a" it would filter the bldg names list to only those beginning with "a" etc. until you choose from a handful of bldg names. Thx again! "Ron Coderre" wrote: AutoFilter/Autocomplete on which cells/sheet? *********** Regards, Ron XL2003, WinXP "James" wrote: Got it...Thanks!...now is there any way for bldg name to auto complete or auto filter? "Ron Coderre" wrote: Try something like this on Sheet2: B2: (a bldg name) The street C2: =VLOOKUP($B2,Sheet1!$A:$E,2,0) The city D2: =VLOOKUP($B2,Sheet1!$A:$E,3,0) etc OR.... C2: =VLOOKUP($B2,Sheet1!$A:$E,COLUMNS($A2:B2),0) copy C2 across to the right.....each successive cell will return more of the address. Is that something you can work with? *********** Regards, Ron XL2003, WinXP "James" wrote: Sheet 1 contains bldg name(column A), bldg street address(column B), City(column C) etc. Sheet 2 is a job/project log. I would like the user to type in only the bldg name in column A and have the other columns auto populate in the respective columns in sheet 2. How can this be accomplished? Thx! |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Auto populate from list/source
Congratulations! I'm glad you figured it out.....and thanks for the feedback.
*********** Regards, Ron XL2003, WinXP "James" wrote: Correct again! I used fixed width instead of delimited when importing and it got rid of all trailing blanks/spaces. I'm on my way now! Thx Again! JR "Ron Coderre" wrote: It seems the pasted value and the typed value are not really the same at all. Try this: Type the correct value in a cell to the right of the pasted value. Compare the two values using someting like =EXACT(A2,G2)......it should return TRUE, but probably won't. Edit the pasted value and check for leading or trailing spaces or any other anomalies. Let us know what you find. *********** Regards, Ron XL2003, WinXP "James" wrote: I'm baaack...now the problem is this. I'm cutting and pasting from a text file into sheet 1. When I enter the information on sheet two it gives me a "value not available" error. If I type(overwrite) the exact same information in the same cell and go to sheet 2, the VLOOKUP formula works. I have 25000 records that I don't want to enter manually obviously. Any ideas? Thx! JR "Ron Coderre" wrote: You'd think Excel could do that automatically, right? See Debra Dalgleish's instructions: http://www.contextures.com/xlDataVal10.html Is that something you can work with? *********** Regards, Ron XL2003, WinXP "James" wrote: Sorry, auto complete for bldg name in sheet 2. For instance if you entered in B2 of sheet 2, the letter "a" it would filter the bldg names list to only those beginning with "a" etc. until you choose from a handful of bldg names. Thx again! "Ron Coderre" wrote: AutoFilter/Autocomplete on which cells/sheet? *********** Regards, Ron XL2003, WinXP "James" wrote: Got it...Thanks!...now is there any way for bldg name to auto complete or auto filter? "Ron Coderre" wrote: Try something like this on Sheet2: B2: (a bldg name) The street C2: =VLOOKUP($B2,Sheet1!$A:$E,2,0) The city D2: =VLOOKUP($B2,Sheet1!$A:$E,3,0) etc OR.... C2: =VLOOKUP($B2,Sheet1!$A:$E,COLUMNS($A2:B2),0) copy C2 across to the right.....each successive cell will return more of the address. Is that something you can work with? *********** Regards, Ron XL2003, WinXP "James" wrote: Sheet 1 contains bldg name(column A), bldg street address(column B), City(column C) etc. Sheet 2 is a job/project log. I would like the user to type in only the bldg name in column A and have the other columns auto populate in the respective columns in sheet 2. How can this be accomplished? Thx! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Auto populate from list/source | Excel Worksheet Functions | |||
| How to Auto-populate cell based on selection of a list item | Excel Discussion (Misc queries) | |||
| Using a drop-down list along with an auto-populate drop-down | Excel Discussion (Misc queries) | |||
| auto populate cell based on previous cell drop down list selectio. | Excel Discussion (Misc queries) | |||
| Dynamic source list to auto expand | Excel Discussion (Misc queries) | |||
Linear Mode
