#### Help with setting up a formula

```Hi. I need some help setting a formula for the following:

Info on Sheet1

Operation      Item A   Item B   Item C
1.Cut            10 min   12 min   15 min
2.Trim          22 min   19 min   17 min
3. Polish       13 min    14 min   16 min

Info on Sheet2
01/11    02/11     03/11
Item A         9 units   5 units   5 units
Item B         0 units   2 units   3 units
Item C         0 units   2 units   1 unit

Info required on Sheet3
01/11      02/11       03/11
1. Cut          (tot min)  (tot min)   (tot min)
2. Trim        (tot min)  (tot min)   (tot min)
3. Polish      (tot min)  (tot min)   (tot min)

Sheet1 contains the info for each operation for the different items
Sheet2 contains a schedule of how many per day of each item is built
Sheet3 must contain a daily summary of operations times per item x build
schedule.

been working on.

Thank you & kind regards
Greg
```
 0
Utf
11/25/2009 10:38:01 AM
excel.misc 78881 articles. 5 followers.

2 Replies
461 Views

Similar Articles

[PageSpeed] 18

```Hi Greg,

Instead of mentioning the required total Minutes as (tot min) in the below
example, just mention the Total Minutes (Values) which should be retrieved
using the formula for our easy reference.

Info required on Sheet3
01/11      02/11       03/11
1. Cut          (tot min)  (tot min)   (tot min)
2. Trim        (tot min)  (tot min)   (tot min)
3. Polish      (tot min)  (tot min)   (tot min)

--------------------
(Ms-Exl-Learner)
--------------------

"Greggo G" wrote:

> Hi. I need some help setting a formula for the following:
>
> Info on Sheet1
>
> Operation      Item A   Item B   Item C
> 1.Cut            10 min   12 min   15 min
> 2.Trim          22 min   19 min   17 min
> 3. Polish       13 min    14 min   16 min
>
> Info on Sheet2
>                    01/11    02/11     03/11
> Item A         9 units   5 units   5 units
> Item B         0 units   2 units   3 units
> Item C         0 units   2 units   1 unit
>
> Info required on Sheet3
>                   01/11      02/11       03/11
> 1. Cut          (tot min)  (tot min)   (tot min)
> 2. Trim        (tot min)  (tot min)   (tot min)
> 3. Polish      (tot min)  (tot min)   (tot min)
>
> Sheet1 contains the info for each operation for the different items
> Sheet2 contains a schedule of how many per day of each item is built
> Sheet3 must contain a daily summary of operations times per item x build
> schedule.
>
> Let me know if you need more info and I'll forward you a spreadsheet if have
> been working on.
>
> Thank you & kind regards
> Greg
```
 0
Utf
11/25/2009 11:28:07 AM
```I would lay out those tables differently.

On Sheet1:

Column A       ColB       ColC (in minutes)
Cut            ItemA        10
Trim           ItemA        12
Polish         ItemA        15
....

Each item/operation has its own entry/row.

On Sheet2:

ColA     ColB (full date)     ColC (Units)
ItemA      01/11/2010           9

.....

Each Item/date has its own entry/row.

And then I'd add more columns to Sheet2...

ColA     ColB (full date)     ColC (Units)   ColD(Cut) ColE(trim) ColF(polish)
ItemA      01/11/2010           9            (Time)    (time)     (Time)

Each of these additional columns would contain formulas that determine that
value.

For the Cut column with "Cut" in D1
=sumproduct(--(sheet1!\$a\$1:\$a\$99=d\$1),
--(sheet1!\$b\$1:\$b\$99=\$a2),
sheet1!\$c\$1:\$c\$99)

(more on this formula later)

And drag to the right for Trim and Polish and drag all 3 formulas down as far as
you need.

This will build a table that you can use to create a pivottable that looks like
the layout you want.

If you've never used pivottables, here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx

================

The =sumproduct() formula...

Adjust the ranges to match--but you can't use whole columns (except in xl2007+).

=sumproduct() likes to work with numbers.  The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

And if you're using xl2007, take a look at =sumifs() in xl's help.  The formula
may be easier to understand.

Greggo G wrote:
>
> Hi. I need some help setting a formula for the following:
>
> Info on Sheet1
>
> Operation      Item A   Item B   Item C
> 1.Cut            10 min   12 min   15 min
> 2.Trim          22 min   19 min   17 min
> 3. Polish       13 min    14 min   16 min
>
> Info on Sheet2
>                    01/11    02/11     03/11
> Item A         9 units   5 units   5 units
> Item B         0 units   2 units   3 units
> Item C         0 units   2 units   1 unit
>
> Info required on Sheet3
>                   01/11      02/11       03/11
> 1. Cut          (tot min)  (tot min)   (tot min)
> 2. Trim        (tot min)  (tot min)   (tot min)
> 3. Polish      (tot min)  (tot min)   (tot min)
>
> Sheet1 contains the info for each operation for the different items
> Sheet2 contains a schedule of how many per day of each item is built
> Sheet3 must contain a daily summary of operations times per item x build
> schedule.
>
> Let me know if you need more info and I'll forward you a spreadsheet if have
> been working on.
>
> Thank you & kind regards
> Greg

--

Dave Peterson
```
 0
Dave
11/25/2009 2:02:26 PM

Similar Artilces:

hide formula in cells till data is available for calc
I recall being able to copy a formula in a column and hiding it until data becomes available to complete the calculation. This way you don't have a series of #value# in the work sheet. The formula contains an absolute reference as well. Thanks, Kiwi A look at your formula would have been nice. =IF(cellref="","",do something) traps for cellref having no data. Gord Dibben MS Excel MVP On Sat, 3 Apr 2010 15:02:01 -0700, kiwi <kiwi@discussions.microsoft.com> wrote: >I recall being able to copy a formula in a column and hiding it u...

sumifs help
I have the following formula. =SUMIFS(Table1[2],\$A\$11:\$A\$22,\$A38,\$B\$11:\$B\$22,\$B38) It now needs to be changed to a formula that can handle text instead of numbers. How do i do it? Use Countif instead of Sumif from =SUMIF(Table1[2],\$A\$11:\$A\$22,\$A38,\$B\$11:\$B\$22,\$B38) to =CountIF(Table1[2],\$A\$11:\$A\$22,\$A38,\$B\$11:\$B\$22,\$B38) Do you really have a function Countifs with an "S" at the end? thie maybe an UDF that needs to be modified. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this th...

Help !
I need to create a data input screen on excel where multiple users at the same time will use them & input data. This data then needs to be stored as a database as well, where i can use it to understand trends Thank you. and the question is ...? <abrahamsaj@gmail.com> wrote in message news:1132155054.927936.191640@z14g2000cwz.googlegroups.com... >I need to create a data input screen on excel where multiple users > at the same time will use them & input data. > This data then needs to be stored as a database as well, where i can > use it to understand trends >...

Setting the range of a dialog box slider
Can anyone help me to set the min and max values of a slider control on a slider control in a dialog box, I created the control with the resource editor and do not know how to access the class of the control (the class wizard only creates a class for the dialog box), is there a way to attach a CSliderCtrl class to the resource or is there some other way to access the object in C++. SiBorg wrote: > Can anyone help me to set the min and max values of a slider control on a slider control in a dialog box, I created the control with the resource editor and do not know how to access the class of...

Help with this thing
It was working in the window "Transactions >> Sales >> Transactions of Sales", but exactly were publishing a Quotation, which i wanna print, more nevertheless was shut up to me network, then I closed the window and it threw several messages to me of error, and from that then it was blocked the quotation that was working. My question is as I can unblock this document? ...

Help with Outlook Setup!!!
I have XP Professional installed and Office XP Professional. I have several users setup for kids, as well as my admin account. I want to setup a Limited Account in addition to the Admin account for myself for daily use. Am I just suppose to pick a different account name and login password and then setup my MSN Messenger and Outlook POP email with same username and password that I used to setup the Admin account? I don't need to share any contacts or anything, I just setup the admin email in order to setup Outlook. I want to input my contact info etc., into the Limited account and use ...

HELP !!! I have a ARRAY Formula HELP !!!
Hello, Here is the ARRAY Formula I have and this is what I am using it for. The situation is that it worked 1 time and than not again. =INDEX(D48:K48,,MAX(IF(D48:K48<>"",COLUMN(D48:K48)))-COLUMN(D48)+1 Duty: I have a row of number that appear hourly (DOLLAR AMOUNTS), the numbe are anything from nothing to 10000. I want the hourly number to appea in specified cell. Here is an example. (I am using EXCEL 2000) Row D48:K48 answer in cell G2 1st hour D48 = \$100.00 G2 Should be \$100.00 2nd Hour D48 = \$100.00 E48 = (nothing) G2 Should be (nothing) 3rd Hour D48 = \$1...

exch 5.5 help
I am in a progress to upgrade Exchange 5.5 (on NT4) to Exhange 2k3 (on 2k3). I setup a test machine and upgrade the OS to w2k3. 1st I want to connect the 5.5 to AD, so I should install ADC. Can anyone tell me the steps? Frorestprep, domainprep, setup adc, and upgrade to exchange 2k3? If you run through the steps in the E2K3 deployment tools they will walk you through everything. -- Hope that helps. ------------------------- Jaclynn Hiranaka Enterprise Messaging Support This posting is provided "AS IS" with no warranties, and confers no rights. � 2004 Microsoft Corporation. Al...

Possible to set up a timer in Outlook?
I would like to have one of Outlook's reminders appear. Then, when I acknowlege it, I would like Outlook to, in effect, 'count down' until a certain period of time has elapsed, then pop up another reminder. In short, rather than a reminder that occurs at a specific time of day, I'd like a recurring reminder that only appears when a certain amount of time has elapsed after I manually acknowlege the last reminder. Is there an easy way to set up such a thing in Outlook? Thanks for any answers or suggestions. Schuh is my hero <no@mail> wrote: > I would like to ha...

Need a Formula for the following: Data Table A B C D E F G H I 1 Tom A W 2 H 30 84 30 2 Peter A W 3 H 3 Nick B L 1 A 70 Columns F1:I3 from Data Table has break scores for each player. Below is the Result Table where I need to show a summary report for high breaks. I have no problem with Break as I use the Large function. I need a formula to insert in A1 and A2 to place the name for the corresponding breaks below. Result Table High Breaks A B Name Break 1 ...

how do i set up a excel program for seniors membership/dues, etc
I need to download a program that will be easy to use. i sign in as confused senior One start is to browse the free templates at MS': http://office.microsoft.com/en-us/templates/default.aspx -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "completely confused" <completelyconfused@discussions.microsoft.com> wrote in message news:B304EB05-8C65-46EF-9D44-1A2E12E318B8@microsoft.com... >I need to download a program that will be easy to use. i sign in as >confused > senior ...

Need help in data copying.
Hi I have an invoicing file in excel (Sheet1). I need to store the dat which is invoiced into another sheet. My Invoice Data starting from Ro 8 and column B to F (The first item is from B8-F8, second item i B9-F9). B-Item Code, C-Item Name, D-Qty, E-Price, F-Total. Once I print the invoice, I need to transfer the data to another shee (Sheet2) . When I create another invoice, the new data should be added below t the previous data in Sheet2. So that I can have all the items I sol in Sheet2. Can someone help me sending a macro for it??? I will be grateful to you. Thanks in advance Tom -...

help...help...help
I just installed Microsoft Office XP Professionaql with no problem. However, whenever I try to perform any task such as opening contacts area in order to create an entry, I receive a dialog box with Microsoft Outlook and a yellow exclamation point. Also, included in the dialog box are the words could not open the item, try again. Other information that might be important is that I use a pst file. The error message also occurs when I try to open the Contact folder from the folder list as well as when I try to perform any function. It was a clean install not an upgrade. Could you ...

OT: Setting for posting here
I'm using Outlook Express 6 to access this newsgroup as well as a few others. Where is the setting that I can put jibberish in my email addy that will force people to manually edit it before sending me mail. For example jl_amerson@hotnospammail.com TIA Please explain! -- tool/accounts/news/email address -- "Display tolerance & kindness to those with less knowledge than you because there is ALWAYS someone with more" "JL Amerson" <JL_Amerson@hotmail.com> wrote in message news:vtf04vqp2qfi88@corp.supernews.com... > I'm using Outlook Express 6 ...

Macro Help
Hi, I had alot of help yesterday from Jacob with the following macro, but am getting a 'run time error 13' when trying to run the macro, and i cannot see why. Any help much appreciated Sub OLApp() Dim objOL As Object, objApp As Object, lngRow As Long Set objOL = CreateObject("Outlook.Application") For lngRow = 9 To Cells(Rows.Count, "A").End(xlUp).Row If Range("E" & lngRow) = "" Then Set objApp = objOL.CreateItem(1) With objApp ..Subject = "Change Password for system" & Range("A" & lngRow)...

Set curdir from where template was opened
I have an app where there is a templete that resides in a directory, under this directory is a data storage folder, the location of the template can change. Part of the code builds the require directory structure below the template location. Is there a way to set the curdir to where the workbook was opened from ie: where template sits? I am interupting the save process so I can set the name of the file depending on values within a worksheet, it needs to default to the "place where template is"\datastore\"Name I define".xls TIA W Code below FYI: Sub Workbook_Be...

help me #2
how to restrict entering of same values or data in excel cell Hi if you mean the 'preventing of duplicate entries' you may check the following site http://www.cpearson.com/excel/NoDupEntry.htm -- Regards Frank Kabel Frankfurt, Germany mangesh khati wrote: > how to restrict entering of same values or data in excel > cell ...

Create and set up custom forms in OWA
Hey there! I read, that it is possible to convert custom forms to asp so that they can be used in OWA. Is there another tool than Microsoft Outlook HTML Form Converter to do the conversion-part? Do you know of an article that describes exactely how to set up such a form and where to install it etc. Like: What to do after I turned the Outlook custom form into an ActiveServerPage? Thanks for any help, Reto. ...

Problem with LeadTools CreateWindow inCFormView -Help !!!
Hi , I have been using LeadTools in Visual Studio 2005. I have a tabctrl and dialogs in each tab and developed using CFormView. So a tabCtrl is a child of CFromView and tab1 is a child of TabCtrl. I want to insert a LEAD control in one of the tabs .i.e, Dialog.I am unable to insert a leadcontrol but inserting a control in the view was easy.The problem is I am not able to get the HWND associated to a particular dialog which is super child of View. Can somebody help me in creating a lead control in the dialog configdlg .h LAnnotationWindow m_LAnnoWnd; FormView.cpp CMyTabCtrl m_myCtrlTab; ...

HelpProvider and HTML Help interaction
I have an application with a .chm help-file. But I have some questions about the behaviour of the help-window. I use a modal application window and I can start the help. The help windows appears but it is allways in foreground of my application window. I can set the input focus on my window, the help window becomes inactive but I cannot move my window over the help window. So I have to close the help window or minimize it or move it aside of my application window when I want to go on in my application. The other problem is when I minimize the help window and then open a dialog...

CString help
I'm looking at a website on CString Management: http://www.codeproject.com:80/string/cstringmgmt.asp In the section entitled, "CString to char * II: Using GetBuffer," the author stresses calling ReleaseBuffer after calling GetBuffer. Is this always necessary? I often use CString::GetBuffer when using CStrings in MessageBox dialogs like so: MessageBox(m_hWnd, cString.GetBuffer(0), lpTitle, MB_OK); Should I be adding a ReleaseBuffer after a MessageBox call? Should I be passing my string data to the MessageBox in another way? Regards, Joe > Is this always necessary?...