 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / May 2008Using List details within a sum
Hi, I am using a worksheet to record vehicle usage hours and the cost. I have a simple worksheet. In the first column there is a drop down list for the registration numbers of the vehicles. The list this is drawn from is on the same page as the rest of the worksheet, just completely out of the way. In the 3rd Column I will be listing the job numbers and then entering the hours for each job in the 5th column and onwards (5th and on is a columnn per day, 4th is a total for each job). What I want the 2nd column to do is, dependant on the reg number selected, to use the hourly cost (in the same list as the reg numbers) and multiply it by the hours I am entering. I have no idea what formula to use or how to get it to notice the right information. The reg number will not always be the same as some weeks some of the vehicles aren't used, otherwise I could have just listed all of them and put in a =SUM(TOTALHOURS*HOURLYCOST). This, however, if all vehicles are not being used, will not be particularly clear. Can you help? Thank you in advance =SUM(TOTALHOURS*VLOOKUP(reg_number,reg_and_hours_list,2,FALSE))  SignatureHTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Hi, > [quoted text clipped - 29 lines] > > Thank you in advance Hi Bob, I'm having problems. This is what I have put in: =F5*VLOOKUP(reg,CA5:CC19,4,FALSE) 'F' is the column the total hours are in reg is the heading in my list for the registration number. CA5:CC19 is the lists area (the list isn't named as I don't know how to... is that important?) '4' is the column I want the answer to go in (I'm pretty sure this one is wrong, but I'm not too sure what I am supposed to be referring to) 'FALSE' is the word you put at the end so I copied it, but again I'm not sure what it means. Got a couple of minutes to help me out again? > =SUM(TOTALHOURS*VLOOKUP(reg_number,reg_and_hours_list,2,FALSE)) > [quoted text clipped - 31 lines] > > > > Thank you in advance Let's take it from my example =SUM(TOTALHOURS*VLOOKUP(reg_number,reg_and_hours_list,2,FALSE)) and let's assume that we are looking at row 2 data in this instance TOTALHOURS is the hours to multiply by - no problem there I assume reg_number would be the registration number in row 2 reg_and_hours_list would be that list that shows the registration numbers and hourly cost. I assumed that registration numbers would be one column, the hourly cost would be the next, that is why I used 2, column 2 within that list. The FALSE just indicates that the list might not be sorted. SignatureHTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Hi Bob, > [quoted text clipped - 63 lines] >> > >> > Thank you in advance Hi Bob, I pretty much get all that, and yet its still not working. I've read that the V stands for vertical. Does that mean that the list I want to take the info from HAS to be above where I want the answer? > Let's take it from my example > [quoted text clipped - 78 lines] > >> > > >> > Thank you in advance No it just means that the reg numbers are in a column (not a row) as are the hourly costs. SignatureHTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Hi Bob, > [quoted text clipped - 92 lines] >> >> > >> >> > Thank you in advance |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.