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

## Related Results

### table to show the average price 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...

### How to count the total number of specific word in a column

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’.

### Inputting percentage amount and not the total

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

### Total value of certain items in a column - help

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

### Calculatin a total population, using logic

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.

### Comparing 2 worksheets and counting the total number of customers who appear on both

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

### Setting the radius / area of a pie chart?

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.

### Can I tally the total number of entries in a column?

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?

### User Form That Calculates Time Length and Fills In Other Informati

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!

### Can I calculate S&H on a sliding scale in an order form?

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.

### Updating the changing price of product by date

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.

Thanks in advance for any help.

Roy

### Can I calculate S&H on a sliding scale in an order form?

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.

### Add the values of raw data in column 2 and 3 and show the answer 5 in datagridview?

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 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.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.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.MinimumWidth = 20
Me.Column1.Name = "Column1"
Me.Column1.Resizable = System.Windows.Forms.DataGridViewTriState.[True]
Me.Column1.Width = 123
'
'Column2
'
Me.Column2.MinimumWidth = 10
Me.Column2.Name = "Column2"
'
'Column3
'
Me.Column3.MinimumWidth = 10
Me.Column3.Name = "Column3"
'
'Column4
'
Me.Column4.MinimumWidth = 10
Me.Column4.Name = "Column4"
'
'Column5
'
Me.Column5.Name = "Column5"
'
'Column6
'
Me.Column6.Name = "Column6"
'
'Form1
'
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.ClientSize = New System.Drawing.Size(740, 456)
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
.FileName = ""
.DefaultExt = ".xlsx"
.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

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)
'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
For i As Integer = 0 To DataGrid1.ColumnCount - 1
Next
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
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

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 want to get the sum of a total number of fields on a weekly basis.

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 ?

### Can You 'count' check boxes and add the sums on another sheet?

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

### Need to Find 3 Column Values and Add the Total per Condition

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.00 How can I append the total number of instances of a string in each row -

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).

### How do I get the total number of items that meet 2 criteria in Exc

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.

### Change the column I to D and sum the totals.

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.

### Comboboxes, userforms and class modules

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.

### If statement and countif statement in an array

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!!

### Vlookup and find the total for a part number

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

### Filter between 2 dates and show sum of

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

### Automatically lock cells

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?