Free Microsoft Excel 2013 Quick Reference

Form that calculates and shows the total price of the products selected in an order

Hi,
I use Excel 2003 and I have some experience with macros but I am totally new to forms.

I would like some guides to design a form that calculates and shows the total price of the products selected in an order as follows:

Each product is identified by:
1) a label,
2) a checkbox,
3) a listbox for the Quantity,
4) a textbox for the Price.

- The product unit prices used in the form are stored in a table.
- When a product is checked the form shows "1" in the Quantity listbox and the calculated product price (quantity * product unit price) in the Price textbox.
- When a product is unchecked the form clears both Quantity listbox and Price textbox.
- If the user modifies the Quantity of a checked product the form shows the new calculated price (quantity * product unit price) in the Price textbox.
- The user can also modify the calculated price by entering a value in the Price textbox.

The total price of the order is shown in a textbox adding the PRICE textboxes of all procucts.
By pressing a command button "PLACE ORDER" the user records all fields of the order in a table.
By pressing a command button "CLEAR FORM" the user clears the form.

Suggestions will be greatly appreciated.

Regards,
Didier


A problem about excel macro.
If the price of stock price is keep on updateing every business days,( colume A is the date ,colume B is stock price). And i need to tablet a table to show the average price of each month from May 2007 to May 2008, how can i use the excel VB to do that? For the unknow date and share price which are updating automatically, how can i make the excel to show the average table is changed follow by the updated data? if adding a chart to show the average price, how is the chart keep on changing.

I am totally confused and need helps...

count.JPG

From the picture I uploaded, there is two letters, ’Y’ and ‘N’ in column C. We can count there are total three ‘Y’s and four ‘N’. But instead of me counting them manually, I want to create a function that can

1) calculate the total number of ‘Y’s and total number of ‘N’s in column C.

2) The function must also be able update the total counts automatically if in future I makes changes in column C, e.g. changing cell C:3 from ‘Y’ to ‘N’.

Hi I have managed to figure out how to add a percentage and show the total in a field eg: =C3*1.3 but what I would like is to have the percentage amount in the field and not the total. I would add the total in another field.

is this understandable?

all very basic I am sure but I cannot figure it out. any help would be greatly appreciated.

thank you so much

Hi All

I'm stuck again with what I'm sure is a small problem but one I just can't think of a solution for.

I have a column (H) that has items in it with corresponding values in column D.

What I'd like to do is add up the values in column D of all the items that are the same in column H.

For example if I wanted to know the total value of all 'cars' in column H I could place this value in a particular cell (noting that not all the cars are the same price)

Can anyone point me in the right direction?

Thanks

I am trying to tally the number of products retired after a certain life. Can I use excel to track this for me? I do not have much experience with logic in Excel. Basically I have four worksheets in my excel document now, each has entries for the years 1990-1999. The four worksheets are: # new product sold, life (years) of a product made in that year, # retired (I need a logic statement here that relates the life of the product built in one year to the number retired that year), and then a total population of product still in service (easy equation: prev. year’s total population - # retired + # sold that year.) My current statement isn't working because the life of the product improves as time goes on. Can someone please help me out? Thank you.

Hi I have 2 worksheets with customer numbers in 'column a' of both worksheets

I want to count the total number of customers who appear on both workseets

e.g. the count would be 2 in the example below
Worksheet 1

Customer number
105
110
115

Worksheet 2
Customer number
100
105
110

is there a way of setting the radius or the area of a pie chart in
centimetres, or some other equivalent?

i am asking this as i need to create a series of pie charts that are
proportional to the total amount of data included in them.

So I have three columns. I put info in one of the three on each line entry. I want to be able to tally just the total number of entries entered in each column, regardless of whether text or a number was entered. I'd like this to appear at the bottom of each column. Is this possible?

Requirements:

I'm creating a user request form. Users need to be able to enter the start
and end dates of requests. I also need there to be three separate fields
that will automatically have the average hours of sunlight per day in the
month requested and average number of overcast days, depending on which month
the request is for and the total day length of the request.

Problems:

I can't figure out how to get the total number of days calculated AND the
average day length and the average number of overcast days entered
automatically. If I have a drop-down list for the dates that users will
select, I can figure out how to get the averages filled in automatically
using VLOOKUP, but I don't know how I can get it to calculate the number of
days in the request.

Example of what I have as of right now:

(Requested Start Dates)
Cell A1 = Drop down list - Jan thru Dec
Cell B1 = Drop down list - 1 thru 31
Cell C1 = Drop down list - 2005 thru 2010

(Requested End Dates)
Cell A2 = Drop down list - Jan thru Dec
Cell B2 = Drop down list - 1 thru 31
Cell C2 = Drop down list - 2005 thru 2010

What I need in other fields:

Cell D1 = Should calculate total number of days in request
Cell D2 = Should automatically fill in the average amount of sunlight per
day in the request, depending on the month selected
Cell D3 = Should automatically fill in the average number of overcast days
in the month requested

I hope this isn't confusing.

Thanks!

I would like to create a sales order form that calculates a total of the unit
price, sales tax and S&H. S&H varies according to the number of units
purchased. I am using excel 2003.

My apologies if this is a repost, I clicked something accidentally in the middle of composing and it disappeared. I looked to see If I could find it but couldn't unless there is a delay in posting. That said:

Here is the situation I have: under the "Diesel Tab", section 1 (Diesel Cost and Usage) I track the daily (and cumulative) cost of diesel used on a daily basis at a set price. In section 2 (Diesel Loads) I track the loads of diesel I receive every 1 to 3 days with changing cost of diesel. What I would like to do and can't seem to figure out is how to have section 1 check section 2 and adjust the price from that point (date) until the next load. My problem is I don't know how to check for loads on an occasional basis. If I received a load every day I could simply go line to line on a daily basis and adjust the cost daily.

Moreover, if there were a way to track the quantity of diesel used before actually changing the price, I would like to incorporate that into the formula. Example: If I have a total of 5000 gallons of diesel that cost $3.50/gal and I received a load of 1000 gallons the next day at $3.25/gal, MY cost would be well under the actual cost because the whole 6000 gallons would be calculated @ $3.25

An error check that would show if I have used more diesel than I have actually received would also be helpful.

And finally, is there any way of protecting cell formatting (such as border line styles) when someone selects a row of data and copies it to the next day, other than instructing them to use the "right-click" - "copy here as values only" option?

Any suggestions in improvements of formulas in other areas of the spreadsheet would be greatly appreciated also.

The attached spreadsheet is password protected with "no password".

Thanks in advance for any help.

Roy

I would like to create a sales order form that calculates a total of the unit
price, sales tax and S&H. S&H varies according to the number of units
purchased. I am using excel 2003.

PLEASE HELP I HAVE TO SUBMIT THIS IN 2 WEEKS TIME!!!

I'm using visual basic 2010 and i am able to import the raw data from excel and into my datagirdview but i am unable to get the code to add the values of raw data in column 2 and 3 and show the answer in column 5. Do i need to loop it? All help and examples will be greatly Appreciated PLEASE HELP!!!

The entire project is here please have a look: http://www.ozgrid.com/forum/showthre...082#post562082

This is the entire code i have now:


	VB:
	
Imports System.Data.SqlClient 
Imports System.IO 
Imports System 
Imports System.Data 
Imports System.Text 
Imports Excel = Microsoft.Office.Interop.Excel 
Imports System.Windows.Forms 
 
 
Public Class Form1 
Inherits System.Windows.Forms.Form 
Dim sqlconn As SqlConnection 
Dim sqlcmd As SqlCommand 
Dim DA As SqlDataAdapter 
Dim DR As SqlDataReader 
Dim DS As System.Data.DataSet 
Dim DT As System.Data.DataTable 
 
 'common variable
Dim connstr As String 
Dim excelPathName As String = String.Empty 
Friend WithEvents Column1 As System.Windows.Forms.DataGridViewTextBoxColumn 
Friend WithEvents Column2 As System.Windows.Forms.DataGridViewTextBoxColumn 
Friend WithEvents Column3 As System.Windows.Forms.DataGridViewTextBoxColumn 
Friend WithEvents Column4 As System.Windows.Forms.DataGridViewTextBoxColumn 
Friend WithEvents Column5 As System.Windows.Forms.DataGridViewTextBoxColumn 
Friend WithEvents Column6 As System.Windows.Forms.DataGridViewTextBoxColumn 
Dim bolUpdate As Boolean = False 
 
#Region " Windows Form Designer generated code " 
 
Public Sub New() 
    MyBase.New() 
     
     'This call is required by the Windows Form Designer.
    InitializeComponent() 
     
     'Add any initialization after the InitializeComponent() call
     
End Sub 
 
 'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean) 
If disposing Then 
    If Not (components Is Nothing) Then 
        components.Dispose() 
    End If 
End If 
MyBase.Dispose(disposing) 
End Sub 
 
 'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer 
Friend WithEvents Txt_Path As System.Windows.Forms.TextBox 
Friend WithEvents Btn_Browse As System.Windows.Forms.Button 
Friend WithEvents Btn_Import As System.Windows.Forms.Button 
Friend WithEvents Btn_Export As System.Windows.Forms.Button 
Friend WithEvents DataGrid1 As System.Windows.Forms.DataGridView 
 
 'NOTE: The following procedure is required by the Windows Form Designer
 'It can be modified using the Windows Form Designer.
 'Do not modify it using the code editor.
Friend WithEvents Opn As System.Windows.Forms.OpenFileDialog 
 Private Sub InitializeComponent() 
Me.Txt_Path = New System.Windows.Forms.TextBox() 
Me.Btn_Browse = New System.Windows.Forms.Button() 
Me.Btn_Import = New System.Windows.Forms.Button() 
Me.Opn = New System.Windows.Forms.OpenFileDialog() 
Me.Btn_Export = New System.Windows.Forms.Button() 
Me.DataGrid1 = New System.Windows.Forms.DataGridView() 
Me.Column1 = New System.Windows.Forms.DataGridViewTextBoxColumn() 
Me.Column2 = New System.Windows.Forms.DataGridViewTextBoxColumn() 
Me.Column3 = New System.Windows.Forms.DataGridViewTextBoxColumn() 
Me.Column4 = New System.Windows.Forms.DataGridViewTextBoxColumn() 
Me.Column5 = New System.Windows.Forms.DataGridViewTextBoxColumn() 
Me.Column6 = New System.Windows.Forms.DataGridViewTextBoxColumn() 
CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).BeginInit() 
Me.SuspendLayout() 
 '
 'Txt_Path
 '
Me.Txt_Path.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle 
Me.Txt_Path.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold,
System.Drawing.GraphicsUnit.Point, CType(0, Byte)) 
Me.Txt_Path.Location = New System.Drawing.Point(105, 6) 
Me.Txt_Path.Name = "Txt_Path" 
Me.Txt_Path.ReadOnly = True 
Me.Txt_Path.Size = New System.Drawing.Size(200, 20) 
Me.Txt_Path.TabIndex = 1 
 '
 'Btn_Browse
 '
Me.Btn_Browse.FlatStyle = System.Windows.Forms.FlatStyle.Flat 
Me.Btn_Browse.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold,
System.Drawing.GraphicsUnit.Point, CType(0, Byte)) 
Me.Btn_Browse.Location = New System.Drawing.Point(329, 6) 
Me.Btn_Browse.Name = "Btn_Browse" 
Me.Btn_Browse.Size = New System.Drawing.Size(75, 23) 
Me.Btn_Browse.TabIndex = 2 
Me.Btn_Browse.Text = "Browse" 
 '
 'Btn_Import
 '
Me.Btn_Import.FlatStyle = System.Windows.Forms.FlatStyle.Flat 
Me.Btn_Import.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold,
System.Drawing.GraphicsUnit.Point, CType(0, Byte)) 
Me.Btn_Import.Location = New System.Drawing.Point(410, 6) 
Me.Btn_Import.Name = "Btn_Import" 
Me.Btn_Import.Size = New System.Drawing.Size(75, 23) 
Me.Btn_Import.TabIndex = 3 
Me.Btn_Import.Text = "Import" 
 '
 'Btn_Export
 '
Me.Btn_Export.FlatStyle = System.Windows.Forms.FlatStyle.Flat 
Me.Btn_Export.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold,
System.Drawing.GraphicsUnit.Point, CType(0, Byte)) 
Me.Btn_Export.Location = New System.Drawing.Point(491, 6) 
Me.Btn_Export.Name = "Btn_Export" 
Me.Btn_Export.Size = New System.Drawing.Size(75, 23) 
Me.Btn_Export.TabIndex = 4 
Me.Btn_Export.Text = "Export" 
Me.Btn_Export.UseVisualStyleBackColor = True 
 '
 'DataGrid1
 '
Me.DataGrid1.AllowUserToOrderColumns = True 
Me.DataGrid1.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize 
Me.DataGrid1.Columns.AddRange(New System.Windows.Forms.DataGridViewColumn() {Me.Column1, Me.Column2, Me.Column3, Me.Column4,
Me.Column5, Me.Column6}) 
Me.DataGrid1.Location = New System.Drawing.Point(19, 37) 
Me.DataGrid1.MinimumSize = New System.Drawing.Size(50, 25) 
Me.DataGrid1.Name = "DataGrid1" 
Me.DataGrid1.Size = New System.Drawing.Size(709, 407) 
Me.DataGrid1.TabIndex = 7 
 '
 'Column1
 '
Me.Column1.AutoSizeMode = System.Windows.Forms.DataGridViewAutoSizeColumnMode.AllCells 
Me.Column1.HeaderText = "Operation Description" 
Me.Column1.MinimumWidth = 20 
Me.Column1.Name = "Column1" 
Me.Column1.Resizable = System.Windows.Forms.DataGridViewTriState.[True] 
Me.Column1.Width = 123 
 '
 'Column2
 '
Me.Column2.HeaderText = "Quantity IN" 
Me.Column2.MinimumWidth = 10 
Me.Column2.Name = "Column2" 
 '
 'Column3
 '
Me.Column3.HeaderText = "Quantity Completed" 
Me.Column3.MinimumWidth = 10 
Me.Column3.Name = "Column3" 
 '
 'Column4
 '
Me.Column4.HeaderText = "% Yield" 
Me.Column4.MinimumWidth = 10 
Me.Column4.Name = "Column4" 
 '
 'Column5
 '
Me.Column5.HeaderText = "Scrapped Wafers" 
Me.Column5.Name = "Column5" 
 '
 'Column6
 '
Me.Column6.HeaderText = "Product Number" 
Me.Column6.Name = "Column6" 
 '
 'Form1
 '
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13) 
Me.ClientSize = New System.Drawing.Size(740, 456) 
Me.Controls.Add(Me.DataGrid1) 
Me.Controls.Add(Me.Btn_Export) 
Me.Controls.Add(Me.Btn_Import) 
Me.Controls.Add(Me.Btn_Browse) 
Me.Controls.Add(Me.Txt_Path) 
Me.Name = "Form1" 
Me.Text = "Excel Connectivity" 
CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit() 
Me.ResumeLayout(False) 
Me.PerformLayout() 
 
End Sub 
 
#End Region 
 
Private Sub Btn_Browse_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btn_Browse.Click 
     'prompt user to select Excel name and folder path
     
    Dim openFileDialog1 As System.Windows.Forms.OpenFileDialog 
    openFileDialog1 = New System.Windows.Forms.OpenFileDialog 
     
    With openFileDialog1 
        .Title = "Excel Spreadsheet" 
        .FileName = "" 
        .DefaultExt = ".xlsx" 
        .AddExtension = True 
        .Filter = "Excel  (*.xlsx)| *.xlsx|All File(*.xlsx)|.xlsx" 
         
        If .ShowDialog = Windows.Forms.DialogResult.OK Then 
            excelPathName = (CType(.FileName, String)) 
             
            If (excelPathName.Length)  0 Then 
                Me.Txt_Path.Text = excelPathName 
            Else 
                 
            End If 
        End If 
    End With 
     
End Sub 
 
Private Sub Btn_Import_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btn_Import.Click 
     
    Dim objExcel As New Excel.Application 
    Dim objBook As Excel.Workbook = objExcel.Workbooks.Open(excelPathName) 
    Dim objSheet As Excel.Worksheet = objBook.Worksheets(1) 
     
    Dim Column1 As Integer 
    Dim Column2 As Integer 
    Dim Column3 As Integer 
    Dim Column4 As Integer 
    Dim Column6 As Integer 
    Dim Answer As Integer = (Column2.ToString - Column3.ToString) 
    Dim Column5 As Integer = Answer 
    Dim Check As Integer 
     
    If Check = Answer Then 
        MessageBox.Show("There are no errors, you can continue.") 
    Else 
         'Check = (Column2.Parse  Column3.Parse)
        MessageBox.Show("There are errors.") 
    End If 
     
     
    objExcel.Visible = True 
     
     
     'Do
     '    Answer = Column2.ToString - Column3.ToString
     'Loop
     
     
    Dim bolFlag As Boolean = True 
    Dim excelRow As Integer = 1 
    Dim excelCol As Integer = 5 
    Dim DGVRow As Integer = 1 
     
    Dim strCell1 As String 
    Dim strCell2 As String 
    Dim strCell3 As String 
    Dim strCell4 As String 
    Dim strCell5 As String 
    Dim strCell6 As String 
    Dim strCell7 As String 
    Dim strCell8 As String 
    Dim strCell9 As String 
    Dim strCell10 As String 
    Dim strCell11 As String 
    Dim strCell12 As String 
    Dim strCell13 As String 
    Dim strCell14 As String 
    Dim strCell15 As String 
    Dim strCell16 As String 
    Dim strCell17 As String 
     
     
     
    Try 
    Do While bolFlag = True 
         
        If Convert.ToString(objSheet.Cells(excelRow, 1).value) = "" Then 
            bolFlag = False 
            Exit Do 
        End If 
         
        With DataGrid1 
             
            strCell1 = CType(objSheet.Cells(excelRow, 1).value, String) 
            strCell2 = CType(objSheet.Cells(excelRow, 2).value, String) 
            strCell3 = CType(objSheet.Cells(excelRow, 3).value, String) 
            strCell4 = CType(objSheet.Cells(excelRow, 4).value, String) 
            strCell5 = CType(objSheet.Cells(excelRow, 5).value, String) 
            strCell6 = CType(objSheet.Cells(excelRow, 6).value, String) 
            strCell7 = CType(objSheet.Cells(excelRow, 7).value, String) 
            strCell8 = CType(objSheet.Cells(excelRow, 8).value, String) 
            strCell9 = CType(objSheet.Cells(excelRow, 9).value, String) 
            strCell10 = CType(objSheet.Cells(excelRow, 10).value, String) 
            strCell11 = CType(objSheet.Cells(excelRow, 11).value, String) 
            strCell12 = CType(objSheet.Cells(excelRow, 12).value, String) 
            strCell13 = CType(objSheet.Cells(excelRow, 13).value, String) 
            strCell14 = CType(objSheet.Cells(excelRow, 14).value, String) 
            strCell15 = CType(objSheet.Cells(excelRow, 15).value, String) 
            strCell16 = CType(objSheet.Cells(excelRow, 16).value, String) 
            strCell17 = CType(objSheet.Cells(excelRow, 17).value, String) 
             
            .Rows.Add(New String() {strCell1, strCell2, strCell3, strCell4, strCell5, strCell6, strCell7, strCell8, strCell9,
strCell10, strCell11, strCell12, strCell13, strCell14, strCell15, strCell16, strCell17}) 
             
             
            DGVRow += 1 
            excelRow += 1 
             
            strCell1 = "" 
            strCell2 = "" 
            strCell3 = "" 
            strCell4 = "" 
            strCell5 = "" 
            strCell6 = "" 
            strCell7 = "" 
            strCell8 = "" 
            strCell9 = "" 
            strCell10 = "" 
            strCell11 = "" 
            strCell12 = "" 
            strCell13 = "" 
            strCell14 = "" 
            strCell15 = "" 
            strCell16 = "" 
            strCell17 = "" 
             
        End With 
         
    Loop 
     
    Catch ex As Exception 
    MessageBox.Show(ex.Message) 
     
    Finally 
     
    objBook.Close() 
    objExcel.Quit() 
    End Try 
     
     
     'Dim Source As String = "C:Testing01.xlsx"
     'MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data
Source='C:Testing01.xlsx';Extended Properties=Excel 8.0;")
     'MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection)
     'MyCommand.TableMappings.Add("Table", "Net-informations.com")
     'DtSet = New System.Data.DataSet
     'MyCommand.Fill(DtSet)
     'DataGrid1.DataSource = DtSet.Tables(0)
     'MyConnection.Close()
     
End Sub 
 
Private Sub Btn_Export_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btn_Export.Click 
     
     'verfying the datagridview having data or not
    If ((DataGrid1.Columns.Count = 0) Or (DataGrid1.Rows.Count = 0)) Then 
        Exit Sub 
    End If 
     
     'Creating dataset to export
    Dim dset As New DataSet 
     'add table to dataset
    dset.Tables.Add() 
     'add column to that table
    For i As Integer = 0 To DataGrid1.ColumnCount - 1 
        dset.Tables(0).Columns.Add(DataGrid1.Columns(i).HeaderText) 
    Next 
     'add rows to the table
    Dim dr1 As DataRow 
    For i As Integer = 0 To DataGrid1.RowCount - 1 
        dr1 = dset.Tables(0).NewRow 
        For j As Integer = 0 To DataGrid1.Columns.Count - 1 
            dr1(j) = DataGrid1.Rows(i).Cells(j).Value 
        Next 
        dset.Tables(0).Rows.Add(dr1) 
    Next 
     
    Dim excel As New Microsoft.Office.Interop.Excel.ApplicationClass 
    Dim wBook As Microsoft.Office.Interop.Excel.Workbook 
    Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet 
     
    wBook = excel.Workbooks.Add() 
    wSheet = wBook.ActiveSheet() 
     
    Dim dt As System.Data.DataTable = dset.Tables(0) 
    Dim dc As System.Data.DataColumn 
    Dim dr As System.Data.DataRow 
    Dim colIndex As Integer = 0 
    Dim rowIndex As Integer = 0 
     
    For Each dc In dt.Columns 
        colIndex = colIndex + 1 
        excel.Cells(1, colIndex) = dc.ColumnName 
    Next 
     
    For Each dr In dt.Rows 
        rowIndex = rowIndex + 1 
        colIndex = 0 
        For Each dc In dt.Columns 
            colIndex = colIndex + 1 
            excel.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName) 
             
        Next 
    Next 
     
    wSheet.Columns.AutoFit() 
    Dim strFileName As String = "C:Testing01.xlsx" 
    Dim blnFileOpen As Boolean = False 
    Try 
    Dim fileTemp As System.IO.FileStream = System.IO.File.OpenWrite(strFileName) 
    fileTemp.Close() 
    Catch ex As Exception 
    blnFileOpen = False 
    End Try 
     
    If System.IO.File.Exists(strFileName) Then 
        System.IO.File.Delete(strFileName) 
    End If 
     
    wBook.SaveAs(strFileName) 
    excel.Workbooks.Open(strFileName) 
    excel.Visible = True 
     
End Sub 
 
End Class 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I have a form that states in 1 row a total number of hours a person worked on a day. Above this row i have a row where i place the date of the certain day this person worked these hours( with TODAY()).

example:
Date: 21-5-2006 23-5-2006 25-5-2006
hours worked daily: 3 , 5 , 4,

WHat i want is that iwant to generate the total number of hours a person worked weekly.
I want to generate this from the dates.

In short i want excell to look at these dates (for instance: 21-5-2006, 23-5-2006, 25-5-2006. ANd take these dates beginning from monday to calculate a week and than add the numbers that i place next to the hours worked a day row and add them up getting an hours worked a week total on the bottom.

Total number of hours worked this week: (if 21-5-2006 is monday than this number should be: 12)

And i want these number to change every week when a new week is beginning.

Can anyone point me in the right direction to work this out ?

Hello,

I am using Excel 2010. I have a sales spread sheet that I input some basic info and price of different products. The first sheet is a 'total' page that shows the totals of each sheet and a grand total. I call it a snapshot of the sales.

On each sheet, there are tick boxes for various reasons (i.e. online sale, payment plan, book only, external candidate). Not every sheet has each option.

I would like to have the first page (snap shot) show the total number of boxes ticked for each category of box. For example, if there are 10 'online sales' from all of the sheets, I would like to have the front page show me "Buy Onlines - 10".

Is this possible to do?

I have attached the workbook I am working with so you can see what I am up against.

Thanks for any help!

Cheers,

Dave

Hello, I am trying to make a Profit Sheet for my Online sales

Basically, there are 3 Worksheets (Products / Trans / Reports)

Attatched is the Excel File

Products: my Sku and product details with profit for each itemTrans: Daily transactions downloaded from online storeReports: This is where all of the Sum data calculated will be sent to

I need "Reports" to take the SKU's listed on Row A, and search "Trans" for any of the Skus that sold, qty, and shipping speed, then add the total amount of the SKU sold, then Multiple that number by the Profit for that SKU which is found on "Products" Sheet, then return the final results to "Reports" in columns B and C (Standard Shipping) (Expedited Shipping)

Sorry if this sounds confusing

PROFIT_REPORT.xlsx

GBP 10. How can I append the total number of instances of a string in each row to the end of each string instance using VBA code?
I use the newspaper selection box from The Racing Post to make horse racing selections. I use an excel worksheet of which a sample is attached.

Each line represents a race and columns G to W shows each newspapers tipster’s selection. Some of the cells in each row have a number after the selection which represents the total number of tips for that horse.
I would like some VBA code that can loop through the cells in each line and append the total number to the cells that contain the same selection but not the total number. To make things a bit more difficult some of the cells have ‘(b)’ or ‘(nb) ‘, indicating best or next best ,after the selection’s name and I would like the total number to be inserted after the selections name but before the (b) or (nb).

In an excel spreadsheet I am attempting to count the number of items that
meet two criteria, i.e.,

(Prod #) (Date Sold)
Column D Column N
11 04/11/04
11 03/15/04
11 05/23/04
18 03/15/04
18 07/04/05
18 08/19/04

I have the total number of the products sold to date. I am attempting to
get the total of product 11 sold before 05/21/04. I am also attempting to
get the total of product 11 sold after 05/21/04. I need the same figures for
product 18. All of the figures are on one spreadsheet, so nothing fancy
there. I've tried "count", "Sum(IF)" "sumproduct" and nothing seems to work.
I'm probably leaving something out or adding too much in, but I get a "0" as
my answer. Any assistance would be much appreciated. Thank you.

I am new, so i would try to explain my problem as best as possible.

I have a column "i" and i have done a =COUNTIF(SKU!I12:I184,">3") what i would like to do know is sum the rows that i have selected in the countif formula but from column d.

So i want to replicate the rows in the countif, change the column I to D and sum the totals.

I hope it could be done and i bet its easy, once you know how.

I have been reading through the forum history on these topics, and am
beginning to understand how to use a class module to trap events on a
userform, but as a pretty raw beginner i am sure there is a lot i am
missing. so i hope someone can help me.

I have multiple userforms, with multiple comboboxes (between 9 and 33,
i think but the specific number shouldn't matter). each userform also
has a single textbox, and a single commandbutton. without getting into
specifics, the textbox shows the total price of all the selections from
the comboboxes, and the commandbutton writes a receipt of all the
purchases.

so right now i have a procedure called recalc, which takes one
argument, which is the userform itself. the code in recalc look likes
this:

Public Sub recalc(ByVal MyUserForm As Object)
Dim customerprice As Integer
Dim ctl As Control

MyUserForm.TextBox1.Value = 0
customerprice = 0
For Each ctl In MyUserForm.Controls
If TypeName(ctl) = "ComboBox" Then
customerprice = customerprice + Price(ctl.Tag, (2 +
ctl.ListIndex))
End If
Next ctl
MyUserForm.TextBox1.Value = Round((1.05 * customerprice), 0)
End Sub

and my code for each "change" event in the comboboxes looks like this:

Private Sub ComboBox1_Change()
Call recalc(Me)
End Sub

what i want to do in the class module is something like this:

Option Explicit

Public WithEvents mCBGroup As msforms.ComboBox

Private Sub mCBGroup_Change()
Call recalc(Me)
End Sub

but i understand that the (Me) is not working, because that will only
work from the "userform" code module itself. so after all that, the
question is how to write the code in the class module so that after the
event procedure ALL the comboboxes are grabbed, not just the one that
changed.

I guess i also understand that there may be a more efficient way to
write this code, then looping through all of the comboboxes every time.
That worked for me, because it allowed the user to change her mind
midstream and change a previous selection.

Any and all suggestions are greatly appreciated. Thanks in advance.

I am creating a shift schedule for my company. I would like to create a formula (using an array, I believe, is the only way to do this...but if there is another way I would love to hear it!) which will determine if the employee is entitled to a one hour lunch, based on the number of hours worked that day. The problem I am running in to is that the standard shifts vary (either 8 hour shifts or 10 hour shifts).

Basically, I want colum AJ "Lunch Hour" to know if the employee took a one hour lunch that day, based on the total number of hours they worked (AH) and their standard shift (AI). I want it to show a "1" in AJ when the standard shift is 10 hours and the person worked 5 or more hours (you don't get a lunch if you work a half day) OR if the standard shift is 8 hours, then they only get the one hour lunch when they work 4 or more hours.

The way I have the AJ colum calculating now, it cannot distinguish between people who worked a full day and people who worked a half day.

I am using Excel 2007, but I have uploaded a 2003 compatible file to this post.
Thanks!!

Hi

I have a spreadsheet with thousands of lines and need to easily be able to do a Vlookup and return the total of each part number.

I have tried using the data subtotal option, but I receive the first amount instead of the total.

Part number in row A
count in row B

I need a to return a total for each part number and do a Vlookup to put that amount into another worksheet.

Thanks

Lostinformulas

Hi guys, I am wasting my time doing a job manually that is should be possible I would have thought to automate.
However, my knowledge of Excel doesn't extend to this so would appreciate some advise if you can spare the time

The data base:

I have a spreadsheet that interrogates sage and returns a list of all the parts on all the purchase orders for 2012.

This list includes parts complete and those yet to be worked on.

There is a column H which shows the due date for the part as indicated in the attached image.

There is also a column M as indicated that shows the total value of all the parts once they have been delivered to stores.

Column M shows as a zero until the parts are received by our main stores.

What do I need to achieve?

As part of my KPI reporting I need to report on my weekly labour, my weekly throughput and running total of each.

What am I doing at the moment?

I am manually selecting all of the parts within the two dates relevant for the week number and noting the sum for that week in £'s
I then put this into my spread sheet as the throughput for that week.

This can take around 30 minutes as it is a large database.

What do I need

I have created a basic spreadsheet with a column that lists week 1 to week 52.

I would like a formulae that I can put into the adjacent column.

This formula would look at column H on the database and return the sum of all the values in Column M between two dates.

The two dates for week 1 would be: 2nd January to 8th January for example

I could put two columns at the side of the week number column if that helps for the start and finish date for each week.

Sorry for the long post guys but hopefully if you haven't lost the will to live and topped yourself you can assist me with this

Sincerely

Martyn

Hi,

Its a pretty simple thing what I want to do, but I have no idea how to do it!

Basically:

Ive got a sheet for the Sales of certain products. Its got a list of products, each with their cost and selling price.

Together with that, weve got a set of columns (going from jan to dec) each with a figure. That figure represents the sales of a certain product in a certain month.

After the (jan-dec) columns, Ive got another set of jan-dec columns, but now each have a formula to calculate the money coming in from the sales.
so these new set of cells basically multiply the selling price of a product by the number sold.

What I want to do:

Lets say, I want to modify the selling price of a product the next month - technically If i do this, the past months figure will change as its using the selling price figure to multiply with the number sold..

so is there anyway of "Locking" the past months cells so that the values dont change even if the selling price is changed?

ATTACHED SAMPLE.

QUOTE of a post I made below which explains a way of doing what I asked for.

Originally Posted by excelit Did think about that.

Im going to attach a sample workbook for you to see. Its slightly more complicated than what I thought actually.

IF I end up using a copy/paste method then this is what I think Im going to do. (P.S: Open the workbook before reading the following as it wont make sense if not!)

1) - Ok check any PrSalesXX sheet. If you scroll to the right, youll see these columns full of values. These simply multiply the product's selling price and multiply it by the number sold per product. - More like the total income per month of a product.

Lets assume Im talking about Product called 1 right now. (Check the worksheet, there are loads of products each with a number)

Now, if I use a copy/paste method,my idea is to keep those columns there BUT
once the selling price of a product is wanting to be changed (which will be done via a form AND can be done only at the end of the month), a macro will have to copy the products sales income and paste it somewhere else.

In simpler terms,
If I wanted to change the selling price of Product 1 in February, the macro would copy the value of Product 1's January Sales (which is at Q2) and paste it someplace else to store. This STORED value then would be used for my other sheets like the CashFlow_XX which calculates total income. This way, I would be able to use different prices per month and still have the correct figures.

Understand what I mean?