using vlookup and sum functions and connecting two worksheets

[adi]

hi...
i am trying to create a database for a company, now the problem i am
facing is this.... i want to write a formula which would sum up certain
cells but that data is in another worksheet. so i wanna combine the
sum function and the vlookup function i guess .. but i cannot
understand how. Consider this ..

Machine hours Tonnage

12 90
20 120
5 120
7 210
25 320

Both these columns are in one worksheet say 'data' I want to
display the sum of the m/c hrs corresponding to the tonnage (for 120 it
is 25) in another worksheet say 'machine hrs'. how can I do that ?
Also if any of you ppl can tell me how to use vlookup for combining
two worksheets.. it would be a great help if anyone can shed some
llight on this... thanx in advance ...

adi

[patele]

I'm sure there's other ways but here's one:

On sheet 'machine hrs' put the following:

=SUMIF(Data!B1:B5,120,Data!A1:A5)

This will look up the 120 on the data sheet and returns 25 as the total.

Hope this helps you.

[adi]

hey ...
thanx for the info... that definetly helped. i was hoping if u could
help me with one more problem... when we create a drop down menu in
excel.. how can we make the data that needs to be present in the list
appear only in the drop-down list and not in the cells? thanx again in
advance...
adi
patele wrote:
> I'm sure there's other ways but here's one:
>
> On sheet 'machine hrs' put the following:
>
> =SUMIF(Data!B1:B5,120,Data!A1:A5)
>
> This will look up the 120 on the data sheet and returns 25 as the
> total.
>
> Hope this helps you.
>
>
> --
> patele
> ------------------------------------------------------------------------
> patele's Profile: http://www.excelforum.com/member.php...o&userid=35849
> View this thread: http://www.excelforum.com/showthread...hreadid=561228

[adi]

hey ...
thanx for the info... that definetly helped. i was hoping if u could
help me with one more problem... when we create a drop down menu in
excel.. how can we make the data that needs to be present in the list
appear only in the drop-down list and not in the cells? thanx again in
advance...
adi
patele wrote:
> I'm sure there's other ways but here's one:
>
> On sheet 'machine hrs' put the following:
>
> =SUMIF(Data!B1:B5,120,Data!A1:A5)
>
> This will look up the 120 on the data sheet and returns 25 as the
> total.
>
> Hope this helps you.
>
>
> --
> patele
> ------------------------------------------------------------------------
> patele's Profile: http://www.excelforum.com/member.php...o&userid=35849
> View this thread: http://www.excelforum.com/showthread...hreadid=561228

[patele]

Adi,

If I'm understanding you correctly you're wanting the data in the drop down list (data validation list) to appear in the list only and nowhere else on the sheet. If this is correct you can do this by going to Data>Validation. In the Allow box click on List. In the Source box enter the data you want followed by a comma after each entry to seperate them. For instance:

1, 2, 3, 4, 5 or January, February, March, April, May. However, if you have a lot of data it may be best to use a defined range name that's hidden somewhere on the worksheet. Let me know if this is what you're needing.