Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Connecting Worksheets | Excel Worksheet Functions | |||
connecting multiple cells to new sheet (VLOOKUP?IF?) | Excel Worksheet Functions | |||
Connecting Worksheets | Excel Discussion (Misc queries) | |||
Connecting multiple worksheets | Excel Discussion (Misc queries) | |||
connecting multiple worksheets | Excel Worksheet Functions |