data and dates

[ashgull80]

hi i have a work book for entering data on different dates, the dates are input in to A1 then the relevant data in A2!
on another ws i the have all the dates for the next 10 years in cell A1,B1 ect i was wondering if there is a formula/code to in A2,B2 ect to go to the previous ws and search the data entered and then in it relevant date cell 2 accumalte all the data found to give a total for that date?
thanks ash

[Bob Phillips]

Ash,

What you ask seems eminently do-able, but can you post a workbook so that we can get it right?

[ashgull80]

here is a very basic sample sheet but shud give u an idea of what i would like!
the data in columns 'b' and 'c' will continue down the sheet until the end of the year and so every month will have a total box!
thanks ash

[Bob Phillips]

=SUMPRODUCT(--(MONTH($B$5:$B$16)=3),$C$5:$C$16)

=SUMPRODUCT(--(MONTH($B$5:$B$16)=4),$C$5:$C$16)

etc.

[ashgull80]

that is super thanks very much xld just what i needed!
thanks again ash!

[ashgull80]

quick question can i make this formula check the month and the year?
thanks ash

[Bob Phillips]

=SUMPRODUCT(--(YEAR($B$5:$B$16)=2007),--(MONTH($B$5:$B$16)=3),$C$5:$C$16)

for example

[ashgull80]

ah cool thanks for that.
just one more quick question i have the date in column 'b' as you know but in column 'a' is the week number of the date in column 'b'. is it possible for the formula to look for week number and then the year?
as there will be data collected for week 1 in 2006 and week 1 in 2007 ect....?
thanks ash

[Bob Phillips]

=SUMPRODUCT(--(YEAR($B$5:$B$16)=2007),--($A$5:$A$16)=21),$C$5:$C$16)

for example

[ashgull80]

how can i change this formula to vba code so it can go in a text box?
thanks ash

[Bob Phillips]

You could leave in on a spreadsheet and just drop the value of the cell into the textbox.

[ashgull80]

makes sense an its easy!
in the userform i have a combo box, what is the code to change text in a text box when something is selected from the list?
eg dates in combo box, 2007, 2008 ect....
2007 is selected textbox has a value from 'a1'
2008 is selected textbox has a value form 'b1'
ect ...
thanks ash

[Bob Phillips]

Select Case ComboBox1.Value
Case 2007: TextBox1.Text = Range("A1").Text
Case 2008: TextBox1.Text = Range("B1").Text
'etc.
End Select

[ashgull80]

thank u but just realised i havent used a combo box i have a text box which displays the year, and there is a 'next' and 'previous' button to change the year in the text box.
but i dont know how to code these buttons!
so can u explain how please, and if poss i dont want it to go to a year that hasent started yet,
eg if we r in 2007 you cant go to 2008 until 01/01/2008
thnak you very much
ash

[Bob Phillips]

Private Sub cmdNext_Click()
Dim iYear As Long

iYear = Clng(txtYear.Text)
If iYear < Year(Date) Then
iYear = iYear + 1
txtYear.Text = iYear
End If
End Sub

Private Sub cmdPrev_Click()
Dim iYear As Long

iYear = Clng(txtYear.Text)
iYear = iYear - 1
txtYear.Text = iYear
End Sub

[ashgull80]

hi
just a small question bout this code, i need to be able 2 to back years as far as 2006 but only forward to the current year how is that possible? as at the mo it starts on 2007 will go forward 2 2008 and no further but will not go back any years.
thanks ash

[ashgull80]

also how can i set the text box so when the user form is opened it starts on the current year?
thanks again

[ashgull80]

anyone got any ideas
thanks