Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Function Help
I am developing a workbook to track mileage of company vehicle. First
worksheet of book has customer's last name, first name, street address, city, and zip in columns A through E. Each additional worksheet is for one month of the year with date, from customer last name, from customer address, to customer last name, to customer address, odometer start, odometer end, and total mileage in columns A through H. From and To Customers last names are selected from a drop down list based on the first worksheet. I want the customers corresponding address to be pulled from the first worksheet named "Customers" and placed into the cell adjacent to their name when I select their name from the drop down list on the monthly worksheet. I'm going in circles trying to figure this out as I am new to Excel but it seems to me that there has got to be a way to do it. Please, any suggestions or help would be greatly appreciated. Thank you. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Function Help
You will have to use vlookup for this.
Use this formula on your each month's worksheet =VLOOKUP(A2,Customers!$A$2:$B$22,2,FALSE) Enter this formula in cell B2 on your first month where you want the address. I am assuming that you have cust last name in cell A2 onwards. You can change the range from A22 to whatever you have. Hope this helps!! -- Pranav Vaidya VBA Developer PN, MH-India If you think my answer is useful, please rate this post as an ANSWER!! "Bruce" wrote: I am developing a workbook to track mileage of company vehicle. First worksheet of book has customer's last name, first name, street address, city, and zip in columns A through E. Each additional worksheet is for one month of the year with date, from customer last name, from customer address, to customer last name, to customer address, odometer start, odometer end, and total mileage in columns A through H. From and To Customers last names are selected from a drop down list based on the first worksheet. I want the customers corresponding address to be pulled from the first worksheet named "Customers" and placed into the cell adjacent to their name when I select their name from the drop down list on the monthly worksheet. I'm going in circles trying to figure this out as I am new to Excel but it seems to me that there has got to be a way to do it. Please, any suggestions or help would be greatly appreciated. Thank you. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Function Help
Assuming you have a header row in both worksheets, and your first
worksheet contains data to row 100, try this in B2 of the second worksheet: =VLOOKUP(A2,Sheet1!A$2:C$100,3,0) If you want the full address then you will need to string three of these together: =VLOOKUP(A2,Sheet1!A$2:E$100,3,0)&", "&VLOOKUP(A2,Sheet1!A$2:E $100,4,0)&", "&VLOOKUP(A2,Sheet1!A$2:E$100,4,0) You can then copy the formula down the column. You would have the same formula for the to customer address, although the first parameter would be C2 instead of A2. Hope this helps. Pete On Aug 30, 5:18 pm, Bruce wrote: I am developing a workbook to track mileage of company vehicle. First worksheet of book has customer's last name, first name, street address, city, and zip in columns A through E. Each additional worksheet is for one month of the year with date, from customer last name, from customer address, to customer last name, to customer address, odometer start, odometer end, and total mileage in columns A through H. From and To Customers last names are selected from a drop down list based on the first worksheet. I want the customers corresponding address to be pulled from the first worksheet named "Customers" and placed into the cell adjacent to their name when I select their name from the drop down list on the monthly worksheet. I'm going in circles trying to figure this out as I am new to Excel but it seems to me that there has got to be a way to do it. Please, any suggestions or help would be greatly appreciated. Thank you. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Function Help
Thanks Pranav! Appreciate it greatly. "Pranav Vaidya" wrote: You will have to use vlookup for this. Use this formula on your each month's worksheet =VLOOKUP(A2,Customers!$A$2:$B$22,2,FALSE) Enter this formula in cell B2 on your first month where you want the address. I am assuming that you have cust last name in cell A2 onwards. You can change the range from A22 to whatever you have. Hope this helps!! -- Pranav Vaidya VBA Developer PN, MH-India If you think my answer is useful, please rate this post as an ANSWER!! "Bruce" wrote: I am developing a workbook to track mileage of company vehicle. First worksheet of book has customer's last name, first name, street address, city, and zip in columns A through E. Each additional worksheet is for one month of the year with date, from customer last name, from customer address, to customer last name, to customer address, odometer start, odometer end, and total mileage in columns A through H. From and To Customers last names are selected from a drop down list based on the first worksheet. I want the customers corresponding address to be pulled from the first worksheet named "Customers" and placed into the cell adjacent to their name when I select their name from the drop down list on the monthly worksheet. I'm going in circles trying to figure this out as I am new to Excel but it seems to me that there has got to be a way to do it. Please, any suggestions or help would be greatly appreciated. Thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
how do I write a vlookup function within an iserror function so t. | Excel Worksheet Functions | |||
Nested IF Function, Date Comparing, and NetworkDays Function | Excel Worksheet Functions |