using vlookup and sum functions and connecting two worksheets
 

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

using vlookup and sum functions and connecting two worksheets
 

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

thanx
 

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

thanx
 

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

using vlookup and sum functions and connecting two worksheets
 

Adi,

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

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

--
patel
-----------------------------------------------------------------------
patele's Profile: http://www.excelforum.com/member.php...fo&userid=3584
View this thread: http://www.excelforum.com/showthread.php?threadid=56122