SumProduct help

[gmc2k2]

Hi i have a formula im using, =SUMPRODUCT(H10:H200="Yes")--(MONTH(J10:J200)=M2)

what im trying to do is if the range of h10:h200 has a yes in and the month of the range in the same row of dates from j10:j200 is this month then to sum the total

but cant seem to get it working, M2 is a cell where i have the formula =Month(M1)
and M1=Today()

how would i do this?

thanks

[Colin Legg]

Hi,

You nearly got it - just a couple of little tweaks:

Please Login or Register  to view this content.

[gmc2k2]

thanks very much worked a treat

[gmc2k2]

one more question about this, i would like the cell m1 which is =Today()
once its updated to todays date, to not change again and be locked at that date

[Colin Legg]

Hi,

TODAY() is a volatile function so it will automatically update whenever the sheet is recalculated. Your only option would be to copy --> pastespecial values (ie. hard code) the date.

[gmc2k2]

i cant upload the workbook as its over the filesize rules, is there anyway i can datestamp the cell when a new sheet is created based on that months date?i have a button where it creates a new worksheet and names it based on a month:

Please Login or Register  to view this content.

and then a cell where it has the formula =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)

and that determines the month and name of the sheet, is there anyway where at the moment the Cell O1 and O2 has the formulas =TODAY() and =MONTH(O1) and then possibly not have O2 update automatically?
cheers

[NBVC]

Please remember to use code tags around any code snippets next time.

[Colin Legg]

Sure, if you're using VBA then it should be quite easy to put the current month in the cell when you create the worksheet. For example:

Please Login or Register  to view this content.

Hope that helps...

[gmc2k2]

thanks colin, how would i incorporate the code that i have with the code that you have, so that the code you have also copies the sheet and creates a new one named the month based on the code you've wrote?

[gmc2k2]

i suppose what im trying to ask is where would the code
ActiveSheet.Copy Sheets(Sheets.Count)
go to copy the sheet that is active before its created and renamed based on the month?

thanks again, im not good with VBA

[gmc2k2]

another question, sorry to be a pain....how do i then for the cell "m2" use that as a date for my formulas where i have the month e.g february as a number "2"

[Colin Legg]

Hi,

another question, sorry to be a pain....how do i then for the cell "m2" use that as a date for my formulas where i have the month e.g february as a number "2"

If you want 2 in the cell instead of February, just change this line:

Please Login or Register  to view this content.

to this:

Please Login or Register  to view this content.

Does that help?

[gmc2k2]

Yeah that helps but now i need the cell "m2" to show 2 and the sheet name to show february?

[Colin Legg]

Hi,

VBA.Date() is a function that returns today's date.

VBA.Month() is a function that returns the month (as an integer) of a given date, so VBA.Month(VBA.Date) would return 2.

VBA.MonthName() is a function that returns a string indicating the specified month, so VBA.MonthName(VBA.Month(VBA.Date)) would return "February" on my computer.

Hopefully that makes it clearer how we get the two things you need:

Please Login or Register  to view this content.

The next thing you need to think about is duplicate worksheet names. If someone generates the new worksheet twice in the same month, or after 12 months have passed, you will get an error?

[gmc2k2]

All done sorry colin,
i used a bit of initiative

Sub AddSheet()

Dim wstNew As Worksheet
Dim strThisMonth As String
Dim strmonthname As String

strThisMonth = VBA.Month(VBA.Date)

strmonthname = VBA.MonthName(VBA.Month(VBA.Date))
Set wstNew = Worksheets.Add(after:=Worksheets(Worksheets.Count))

wstNew.Name = strmonthname
wstNew.Range("M2").Value = strThisMonth


End Sub

but how do i get it to copy the sheet that is currently active and then do the above code?

[Colin Legg]

but how do i get it to copy the sheet that is currently active and then do the above code?

Try this:

Please Login or Register  to view this content.

[gmc2k2]

thank you very much colin, your a legend, with this of course i assume the sheetname cannot be duplicated and the sheetname will only change for the new sheet as long as its a new month

[gmc2k2]

thanks again colin, another question, im using the =SUMPRODUCT(--(C10:C200="Value"),--(H10:H200<>""),--(MONTH(H10:H200)=O2))
In one of the formulas for a cell, obviously O2=the month of that worksheet name as discussed above, what i would like for it to do is if the sheet for the month before has a cell with a date which is equal to this months date then to be included within this months total aswell,
e.g if januarys sheet has a cell which is a date in february, then on february =SUMPRODUCT(--(C10:C200="Mortgage"),--(H10:H200<>""),--(MONTH(H10:H200)=O2)) total to be included within that even though its on januarys worksheet
any help would be great again