Using List details within a sum

[forevertrying]

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

 

[Bob Phillips]

=SUM(TOTALHOURS*VLOOKUP(reg_number,reg_and_hours_list,2,FALSE))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[forevertrying]

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?

 

[Bob Phillips]

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.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[forevertrying]

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?

 

[Bob Phillips]

No it just means that the reg numbers are in a column (not a row) as are the
hourly costs.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)