| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help to build a Look Up Function or What Ever Function Excel 2002
I am a limo driver and I have bulit a extensive monthly spread sheet. Each
monthly book has 5 to 6 different spread sheets within it. (useless info, any ways) Each spread sheet is based on my weekly runs. I will do my best to explain. B7 C7 D7 E7 F7 Event From To Car Time Departure Ancaster Toronto Sedan $20.00 What I need is a Look Up or what ever will work. Below is a table of our price list according to the event and type of car we use. Based on the results above and the info I have inputted the function to read the table below and place the price in the "Time" column Weddings and Socials are based on a hourly rate which needs to be multipled using the results in N7 VAN BUS CAR LIMO SUV Airport $20 $22 $30 Airport $30 $30 $40 Bar Run $50 $60 $70 Bar Run $50 $60 $70 Add'l $2 $3 $5 Social $10 $10 $12 Wedding $10 $10 $12 Prom $30 $40 $50 Prom $40 $40 $70 Airport $12 $12 $15 Airport $25 $25 $30 Airport $30 $30 $40 Airport $30 $30 $40 Airport $30 $30 $40 Airport $30 $30 $40 Airport $35 $35 $45 Airport $35 $35 $45 Airport $40 $40 $50 Airport $40 $40 $50 Airport $40 $40 $50 Airport $40 $40 $50 Airport $40 $40 $50 Bar Run $30 $40 $50 Bar Run $30 $50 $50 Bar Run $30 $50 $50 Bar Run $40 $50 $50 Bar Run $60 $70 $80 Bar Run $60 $70 $80 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help to build a Look Up Function or What Ever Function Excel 2002
Well first off, you need some kind of unique identifier in your table. It
could be as easy as number them 1,2,3,etc. Or you can do a numbering system that gives you an idea of what it means like Air.Event1, Air.Event2, etc. So then you could use a VLookup function in the Time column, like Vlookup(Event,YourTableList,ColNumber,FALSE) So you need to create a new column that determines what Column should be selected in the vlookup. For instance insert a new column F If(E7="Car",1,IF(E7="Limo",2,If(E7="SUV",3,"N/A"))) So your final vlookup in the time column would be Vlookup(B7,YourListArray,F7,FALSE) I might be totally off here, but let me know if you have any questions. "Carlo" wrote: I am a limo driver and I have bulit a extensive monthly spread sheet. Each monthly book has 5 to 6 different spread sheets within it. (useless info, any ways) Each spread sheet is based on my weekly runs. I will do my best to explain. B7 C7 D7 E7 F7 Event From To Car Time Departure Ancaster Toronto Sedan $20.00 What I need is a Look Up or what ever will work. Below is a table of our price list according to the event and type of car we use. Based on the results above and the info I have inputted the function to read the table below and place the price in the "Time" column Weddings and Socials are based on a hourly rate which needs to be multipled using the results in N7 VAN BUS CAR LIMO SUV Airport $20 $22 $30 Airport $30 $30 $40 Bar Run $50 $60 $70 Bar Run $50 $60 $70 Add'l $2 $3 $5 Social $10 $10 $12 Wedding $10 $10 $12 Prom $30 $40 $50 Prom $40 $40 $70 Airport $12 $12 $15 Airport $25 $25 $30 Airport $30 $30 $40 Airport $30 $30 $40 Airport $30 $30 $40 Airport $30 $30 $40 Airport $35 $35 $45 Airport $35 $35 $45 Airport $40 $40 $50 Airport $40 $40 $50 Airport $40 $40 $50 Airport $40 $40 $50 Airport $40 $40 $50 Bar Run $30 $40 $50 Bar Run $30 $50 $50 Bar Run $30 $50 $50 Bar Run $40 $50 $50 Bar Run $60 $70 $80 Bar Run $60 $70 $80 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help to build a Look Up Function or What Ever Function Excel 2
|
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help to build a Look Up Function or What Ever Function Excel 2
Well first off, you need some kind of unique identifier in your table. WHICH
TABLE the one with the prices or the one that has the event, car type..... a little confused, did you get my last response. "akphidelt" wrote: Well first off, you need some kind of unique identifier in your table. It could be as easy as number them 1,2,3,etc. Or you can do a numbering system that gives you an idea of what it means like Air.Event1, Air.Event2, etc. So then you could use a VLookup function in the Time column, like Vlookup(Event,YourTableList,ColNumber,FALSE) So you need to create a new column that determines what Column should be selected in the vlookup. For instance insert a new column F If(E7="Car",1,IF(E7="Limo",2,If(E7="SUV",3,"N/A"))) So your final vlookup in the time column would be Vlookup(B7,YourListArray,F7,FALSE) I might be totally off here, but let me know if you have any questions. "Carlo" wrote: I am a limo driver and I have bulit a extensive monthly spread sheet. Each monthly book has 5 to 6 different spread sheets within it. (useless info, any ways) Each spread sheet is based on my weekly runs. I will do my best to explain. B7 C7 D7 E7 F7 Event From To Car Time Departure Ancaster Toronto Sedan $20.00 What I need is a Look Up or what ever will work. Below is a table of our price list according to the event and type of car we use. Based on the results above and the info I have inputted the function to read the table below and place the price in the "Time" column Weddings and Socials are based on a hourly rate which needs to be multipled using the results in N7 VAN BUS CAR LIMO SUV Airport $20 $22 $30 Airport $30 $30 $40 Bar Run $50 $60 $70 Bar Run $50 $60 $70 Add'l $2 $3 $5 Social $10 $10 $12 Wedding $10 $10 $12 Prom $30 $40 $50 Prom $40 $40 $70 Airport $12 $12 $15 Airport $25 $25 $30 Airport $30 $30 $40 Airport $30 $30 $40 Airport $30 $30 $40 Airport $30 $30 $40 Airport $35 $35 $45 Airport $35 $35 $45 Airport $40 $40 $50 Airport $40 $40 $50 Airport $40 $40 $50 Airport $40 $40 $50 Airport $40 $40 $50 Bar Run $30 $40 $50 Bar Run $30 $50 $50 Bar Run $30 $50 $50 Bar Run $40 $50 $50 Bar Run $60 $70 $80 Bar Run $60 $70 $80 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help to build a Look Up Function or What Ever Function Excel 2
Actually the Airport for Arrival Or Departure are the same price, the only
difference between each is from what city you are leaving from or to and or what type of car you are using. I would really like to move on this, here is my email address: I look forward in hoping to complete this, as soon as possible. Thank You Carlo "akphidelt" wrote: Yea, I can set something up for you. The unique identifier was for the table. You have to be able to reference the table by an identifier so you can get the correct cost for that event. So if you have Airport $20 $22 $30 Airport $25 $30 $50 Excel has no way of determining which Airport you want to choose. You need something like Airport.OneWay, Airport.RoundTrip, etc. Then when you want to create the look up function you have something for excel to actually look up. "Carlo" wrote: I am quite lost. Do you know of any one interested in completing this task for about $50 to $75 "akphidelt" wrote: Well first off, you need some kind of unique identifier in your table. It could be as easy as number them 1,2,3,etc. Or you can do a numbering system that gives you an idea of what it means like Air.Event1, Air.Event2, etc. So then you could use a VLookup function in the Time column, like Vlookup(Event,YourTableList,ColNumber,FALSE) So you need to create a new column that determines what Column should be selected in the vlookup. For instance insert a new column F If(E7="Car",1,IF(E7="Limo",2,If(E7="SUV",3,"N/A"))) So your final vlookup in the time column would be Vlookup(B7,YourListArray,F7,FALSE) I might be totally off here, but let me know if you have any questions. "Carlo" wrote: I am a limo driver and I have bulit a extensive monthly spread sheet. Each monthly book has 5 to 6 different spread sheets within it. (useless info, any ways) Each spread sheet is based on my weekly runs. I will do my best to explain. B7 C7 D7 E7 F7 Event From To Car Time Departure Ancaster Toronto Sedan $20.00 What I need is a Look Up or what ever will work. Below is a table of our price list according to the event and type of car we use. Based on the results above and the info I have inputted the function to read the table below and place the price in the "Time" column Weddings and Socials are based on a hourly rate which needs to be multipled using the results in N7 VAN BUS CAR LIMO SUV Airport $20 $22 $30 Airport $30 $30 $40 Bar Run $50 $60 $70 Bar Run $50 $60 $70 Add'l $2 $3 $5 Social $10 $10 $12 Wedding $10 $10 $12 Prom $30 $40 $50 Prom $40 $40 $70 Airport $12 $12 $15 Airport $25 $25 $30 Airport $30 $30 $40 Airport $30 $30 $40 Airport $30 $30 $40 Airport $30 $30 $40 Airport $35 $35 $45 Airport $35 $35 $45 Airport $40 $40 $50 Airport $40 $40 $50 Airport $40 $40 $50 Airport $40 $40 $50 Airport $40 $40 $50 Bar Run $30 $40 $50 Bar Run $30 $50 $50 Bar Run $30 $50 $50 Bar Run $40 $50 $50 Bar Run $60 $70 $80 Bar Run $60 $70 $80 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help to build a Look Up Function or What Ever Function Excel 2
Sir I would really like to proceed
"Carlo" wrote: Well first off, you need some kind of unique identifier in your table. WHICH TABLE the one with the prices or the one that has the event, car type..... a little confused, did you get my last response. "akphidelt" wrote: Well first off, you need some kind of unique identifier in your table. It could be as easy as number them 1,2,3,etc. Or you can do a numbering system that gives you an idea of what it means like Air.Event1, Air.Event2, etc. So then you could use a VLookup function in the Time column, like Vlookup(Event,YourTableList,ColNumber,FALSE) So you need to create a new column that determines what Column should be selected in the vlookup. For instance insert a new column F If(E7="Car",1,IF(E7="Limo",2,If(E7="SUV",3,"N/A"))) So your final vlookup in the time column would be Vlookup(B7,YourListArray,F7,FALSE) I might be totally off here, but let me know if you have any questions. "Carlo" wrote: I am a limo driver and I have bulit a extensive monthly spread sheet. Each monthly book has 5 to 6 different spread sheets within it. (useless info, any ways) Each spread sheet is based on my weekly runs. I will do my best to explain. B7 C7 D7 E7 F7 Event From To Car Time Departure Ancaster Toronto Sedan $20.00 What I need is a Look Up or what ever will work. Below is a table of our price list according to the event and type of car we use. Based on the results above and the info I have inputted the function to read the table below and place the price in the "Time" column Weddings and Socials are based on a hourly rate which needs to be multipled using the results in N7 VAN BUS CAR LIMO SUV Airport $20 $22 $30 Airport $30 $30 $40 Bar Run $50 $60 $70 Bar Run $50 $60 $70 Add'l $2 $3 $5 Social $10 $10 $12 Wedding $10 $10 $12 Prom $30 $40 $50 Prom $40 $40 $70 Airport $12 $12 $15 Airport $25 $25 $30 Airport $30 $30 $40 Airport $30 $30 $40 Airport $30 $30 $40 Airport $30 $30 $40 Airport $35 $35 $45 Airport $35 $35 $45 Airport $40 $40 $50 Airport $40 $40 $50 Airport $40 $40 $50 Airport $40 $40 $50 Airport $40 $40 $50 Bar Run $30 $40 $50 Bar Run $30 $50 $50 Bar Run $30 $50 $50 Bar Run $40 $50 $50 Bar Run $60 $70 $80 Bar Run $60 $70 $80 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel 2002 Min Function | Excel Discussion (Misc queries) | |||
| Excel 2002 AVERAGE Function | Excel Discussion (Misc queries) | |||
| <REQ Can anyone help me build a function, please read.... | Excel Worksheet Functions | |||
| Build an array constant with DATE function | Excel Worksheet Functions | |||
| Merge using Excel 2002 and Hummingbird Docs Open 3.9.0 Build 199 | Excel Discussion (Misc queries) | |||
Linear Mode
