Formula Help!!!!

D

daniellepp2

New Member
Joined
May 6, 2011
Messages
16
Hello,

I hope someone can help me with this issue.

1 sheet has all of my data. The other sheets have a custom template. I am trying to get Excel to populate a cell based on certain criteria. I have the formula....

=IF(AND( Data!B40>=DATE(2011,3,1), Data!B40<=DATE(2011,3,31)),Data!B40,"")

The above formula only works if the data sheet remains the same. If I add rows to the data sheet then the formula is not correct.

I am looking for a formula that looks at an entire column or worksheet and if it meets a certain date range then enter the row.

Does that make sense? Can anyone help?

Thank you very much,
D
 
Sort by date Sort by votes
Hi,

The following is a solution that may help it is mainly VB that has been borrowed & ammended from others in this forum.....

You need to add one column but the split of the data is done automatically, retain all headers etc and drops the data into 12 different worksheets for you.....

Using your data, add column G as shown below, the formatting needs to show the month & year (click on cell, Control & 1>>Format>>Custom>> "mmm-yy") and copy down for the length of your data. NB you must add a header as shown for the VB to run!!

Excel 2007<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #efefef">Subject</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #efefef">StartDate</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #efefef">StartTime</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #efefef">EndTime</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #efefef">Description</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #efefef">Location</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #efefef">Month</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #efefef">Meeting</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #efefef; TEXT-ALIGN: right">01/04/2011</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #efefef; TEXT-ALIGN: right">9:00 AM</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #efefef; TEXT-ALIGN: right">3:00 PM</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #efefef">Tinkerbell Pre-School Fundraiser</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #efefef">Office</TD><TD style="BORDER-LEFT: black 1px solid; TEXT-ALIGN: right">Apr-11</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #efefef">Tour</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #efefef; TEXT-ALIGN: right">02/05/2011</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #efefef; TEXT-ALIGN: right">9:00 AM</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #efefef; TEXT-ALIGN: right">3:00 PM</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #efefef">GP Little League Assoc Sign-ups</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #efefef">Office</TD><TD style="BORDER-LEFT: black 1px solid; TEXT-ALIGN: right">May-11</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #efefef">Meeting</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #efefef; TEXT-ALIGN: right">03/09/2011</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #efefef; TEXT-ALIGN: right">9:00 AM</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #efefef; TEXT-ALIGN: right">3:00 PM</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #efefef">Tinkerbell Pre-School Fundraiser</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #efefef">Office</TD><TD style="BORDER-LEFT: black 1px solid; TEXT-ALIGN: right">Sep-11</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #efefef">Tour</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #efefef; TEXT-ALIGN: right">04/10/2011</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #efefef; TEXT-ALIGN: right">9:00 AM</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #efefef; TEXT-ALIGN: right">3:00 PM</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #efefef">GP Little League Assoc Sign-ups</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #efefef">Office</TD><TD style="BORDER-LEFT: black 1px solid; TEXT-ALIGN: right">Oct-11</TD></TR></TBODY></TABLE>
Sheet1​


<TABLE style="BORDER-RIGHT: black 2px solid; PADDING-RIGHT: 0.4em; BORDER-TOP: black 2px solid; PADDING-LEFT: 0.4em; PADDING-BOTTOM: 0.4em; BORDER-LEFT: black 2px solid; PADDING-TOP: 0.4em; BORDER-BOTTOM: black 2px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all width="85%"><TBODY><TR><TD style="PADDING-RIGHT: 6px; PADDING-LEFT: 6px; PADDING-BOTTOM: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center" cellPadding=2 rules=all width="100%"><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0"><TH width=10>Cell</TH><TH style="PADDING-LEFT: 5px; TEXT-ALIGN: left">Formula</TH></TR></THEAD><TBODY><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>G2</TH><TD style="TEXT-ALIGN: left">=B2</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>G3</TH><TD style="TEXT-ALIGN: left">=B3</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>G4</TH><TD style="TEXT-ALIGN: left">=B4</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>G5</TH><TD style="TEXT-ALIGN: left">=B5</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Copy the following code into a new module:

Code: 
Sub Split_retain_headers()
Dim lastrow As Long, LastCol As Integer, i As Long, iStart As Long, iEnd As Long
Dim ws As Worksheet, r As Range, iCol As Integer, t As Date
On Error Resume Next
Set r = Application.InputBox("Click in the column to extract by", Type:=8)
On Error GoTo 0
If r Is Nothing Then Exit Sub
iCol = r.Column
t = Now
Application.ScreenUpdating = False
With ActiveSheet
    lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
    LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
    .Range(.Cells(2, 1), Cells(lastrow, LastCol)).Sort Key1:=Cells(2, iCol), Order1:=xlAscending, _
        Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    iStart = 2
    For i = 2 To lastrow
        If .Cells(i, iCol).Value <> .Cells(i + 1, iCol).Value Then
            iEnd = i
            Sheets.Add after:=Sheets(Sheets.Count)
            Set ws = ActiveSheet
            On Error Resume Next
            ws.Name = .Cells(iStart, iCol).Value
            On Error GoTo 0
            .Range(.Cells(1, 1), .Cells(1, LastCol)).Copy Destination:=ws.Range("A1")
            .Range(.Cells(iStart, 1), .Cells(iEnd, LastCol)).Copy Destination:=ws.Range("A2")
            iStart = iEnd + 1
        End If
    Next i
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
MsgBox "Completed in " & Format(Now - t, "hh:mm:ss.00"), vbInformation
If MsgBox("Do you want to save the separated sheets as workbooks", vbYesNo + vbQuestion) = vbYes Then
    Prefix = InputBox("Enter a prefix (or leave blank)")
    Application.ScreenUpdating = False
    For Each sh In ThisWorkbook.Worksheets
        If sh.Name <> Master Then
            sh.Copy
            ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & Prefix & sh.Name & ".xls"
            ActiveWorkbook.Close
        End If
     Next sh
     Application.ScreenUpdating = True
End If
End Sub

If you don't know how to do this, save a COPY of your data then press Alt & F11

Insert>>Module

Then paste the code into the new window that opens.

Press Alt & Q to close and go back to your sheet.

Press Alt & F8 then hit "Run"

The pop up box will ask you which column to click in, select one of the cells in column G and follow the instructions of next popups.

HTH - post back if you have any problems.

Ian


PS - if you want to save this in 2007 you must choose a macro enabled workbook ;-)
 
Last edited:
Upvote 0

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Thanks Ian. But if I have to add Column G like you suggested it will be labor intensive and will defeat the purpose of what I am trying to achieve.

I export my Outlook appointments to an Excel spreadsheet. If I add Column G after the import I would have to then look at each appointment date and copy them to Column G....I would have to add this column every time I import my updated appointments which is every week.
 
Upvote 0
Hi,

Delete the code posted previously and try with this code - it is rough as I need to sleep....!!

Code: 
Sub Split_retain_headers()
Dim lastrow As Long, LastCol As Integer, i As Long, iStart As Long, iEnd As Long
Dim ws As Worksheet, r As Range, iCol As Integer, t As Date
Range("G1").Select
    ActiveCell.FormulaR1C1 = "Month"
    With ActiveCell.Characters(Start:=1, Length:=5).Font
        .Name = "Verdana"
        .FontStyle = "Regular"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .Color = -16777216
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "=RC[-5]"
    Range("G2").Select
    Selection.NumberFormat = "mmm-yy"
    Selection.AutoFill Destination:=Range("G2:G10")
    Range("G2:G10").Select
 
On Error Resume Next
Set r = Application.InputBox("Click in the column to extract by", Type:=8)
On Error GoTo 0
If r Is Nothing Then Exit Sub
iCol = r.Column
t = Now
Application.ScreenUpdating = False
With ActiveSheet
    lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
    LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
    .Range(.Cells(2, 1), Cells(lastrow, LastCol)).Sort Key1:=Cells(2, iCol), Order1:=xlAscending, _
        Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    iStart = 2
    For i = 2 To lastrow
        If .Cells(i, iCol).Value <> .Cells(i + 1, iCol).Value Then
            iEnd = i
            Sheets.Add after:=Sheets(Sheets.Count)
            Set ws = ActiveSheet
            On Error Resume Next
            ws.Name = .Cells(iStart, iCol).Value
            On Error GoTo 0
            .Range(.Cells(1, 1), .Cells(1, LastCol)).Copy Destination:=ws.Range("A1")
            .Range(.Cells(iStart, 1), .Cells(iEnd, LastCol)).Copy Destination:=ws.Range("A2")
            iStart = iEnd + 1
        End If
    Next i
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
MsgBox "Completed in " & Format(Now - t, "hh:mm:ss.00"), vbInformation
If MsgBox("Do you want to save the separated sheets as workbooks", vbYesNo + vbQuestion) = vbYes Then
    Prefix = InputBox("Enter a prefix (or leave blank)")
    Application.ScreenUpdating = False
    For Each sh In ThisWorkbook.Worksheets
        If sh.Name <> Master Then
            sh.Copy
            ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & Prefix & sh.Name & ".xls"
            ActiveWorkbook.Close
        End If
     Next sh
     Application.ScreenUpdating = True
End If
End Sub

This will automate the complete process and split the data as requested into months without any further input from yourself

Regards,
Ian
 
Last edited:
Upvote 0
Thanks Ian. I am still trying to figure out how to use this Visual Basic code. Not sure I have Visual Basic running with my Excel. I am also on a MAC so have to figure out how to use my Function keys.

I will try your new code as soon as I figure out how. Thanks for your help.
 
Upvote 0
No worries - would have been nice to know that you were on a MAC earlier....:banghead: :)

If you are running anything except 2008 you will be OK but if you are running 2008 VB is not supported.....

Google MAC excel VBA if you get stuck or post back - bedtime for me now

POst back if you get stuck and I will pick up in the morning!!

GN

Ian
 
Upvote 0
so sorry about that. I thought Excel was Excel whether on MAC or PC.

My Excel is 2007 and I checked through the menu, I do not have Visual Basic. On Monday I will check in with my IT department to either install it or let me borrow a PC. The person who will be using this spreadsheet is on a PC so she'll be fine. I am the person who is trying to get it set up for her. I am finished the spreadsheet and calendar but have run into this one last issue.
 
Upvote 0
daniellepp2 said:
File, Export, then choose excel. All data is exported to an Excel sheet.
Click to expand...
After doing this, how does the resultant worksheet end up in a workbook that also has "January', 'February' etc worksheets?
 
Upvote 0
Peter....just a quick cut and paste into my workbook as Sheet1. The subsequent sheets are Jan, Feb, Mar, etc.

I'm stuck still trying to figure this out:confused:
 
Upvote 0
daniellepp2 said:
Peter....just a quick cut and paste into my workbook as Sheet1. The subsequent sheets are Jan, Feb, Mar, etc.

I'm stuck still trying to figure this out:confused:
Click to expand...
If you are just cutting and pasting the data from your exported sheet into your existing workbook then I don't see why you couldn't use my original suggestion. You could have your 'Data' sheet set up with the formulas/hidden columns way off to the right somewhere well out of the range your pasted data is ever likely to use. The monthly sheets also already have their formula populated. So it is not 'labour-intensive' in that the set-up only has to be done once.

Then each time you paste new data into the Data sheet the formulas will draw that data in to the relevant monthly sheets.
 
Upvote 0
Hi Peter.

You have a good point. Perhaps I should just insert the copied columns instead of pasting the entire worksheet, then I can keep the formulas.

So I am attempting to use the formulas you listed previously. Would you mind explaining what your formulas mean just so I can trouble shoot this.

Data sheet. Formulas in E2:F2 copied down.

Data

<table style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:93px;"><col style="width:27px;"><col style="width:22px;"><col style="width:79px;"><col style="width:86px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td> </td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td></tr><tr style="height:21px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td style="font-size:10pt; text-align:right; ">Dates</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td style="font-size:10pt; text-align:right; ">1/05/2011</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">May-2011</td><td style="font-size:10pt; ">May-2011|1</td></tr><tr style="height:21px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td style="font-size:10pt; text-align:right; ">23/04/2010</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">Apr-2010</td><td style="font-size:10pt; ">Apr-2010|1</td></tr><tr style="height:21px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td style="font-size:10pt; text-align:right; ">15/05/2009</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">May-2009</td><td style="font-size:10pt; ">May-2009|1</td></tr><tr style="height:21px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td style="font-size:10pt; text-align:right; ">28/05/2011</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">May-2011</td><td style="font-size:10pt; ">May-2011|2</td></tr><tr style="height:21px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td><td style="font-size:10pt; text-align:right; ">1/05/2011</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">May-2011</td><td style="font-size:10pt; ">May-2011|3</td></tr><tr style="height:21px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">7</td><td style="font-size:10pt; text-align:right; ">2/06/2011</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">Jun-2011</td><td style="font-size:10pt; ">Jun-2011|1</td></tr><tr style="height:21px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">8</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr></tbody></table>
<table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family:Arial; font-size:9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color:#cacaca; font-size:10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>E2</td><td>=TEXT(B2,"mmm-yyyy")</td></tr><tr><td>F2</td><td>=E2&"|"&COUNTIF(E$2:E2,E2)</td></tr></tbody></table></td></tr></tbody></table>
Cell E2 = the text in Column B2 is mmm-yyyy
Cell F2 = the column E2.....


Then for, say, May 2011 sheet. Formulas in A2 and D2 copied down. Any of columns B:D can be hidden if you want.

May 2011

<table style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:86px;"><col style="width:83px;"><col style="width:30px;"><col style="width:29px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td> </td><td>A</td><td>B</td><td>C</td><td>D</td></tr><tr style="height:21px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td style="font-size:10pt; ">Dates</td><td style="font-size:10pt; text-align:right; ">May-2011</td><td style="font-size:10pt; text-align:right; ">3</td><td style="font-size:10pt; text-align:right; ">0</td></tr><tr style="height:21px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td style="font-size:10pt; text-align:right; ">1/05/2011</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">1</td></tr><tr style="height:21px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td style="font-size:10pt; text-align:right; ">28/05/2011</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">2</td></tr><tr style="height:21px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td style="font-size:10pt; text-align:right; ">1/05/2011</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">3</td></tr><tr style="height:21px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr></tbody></table>
<table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family:Arial; font-size:9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color:#cacaca; font-size:10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>B1</td><td>="May-2011"</td></tr><tr><td>C1</td><td>=COUNTIF(Data!E2:E1000,B1)</td></tr><tr><td>A2</td><td>=IF(D2="","",INDEX(Data!B$2:B$1000,MATCH(B$1&"|"&D2,Data!F$2:F$1000,0)))</td></tr><tr><td>D2</td><td>=IF(ROWS(D$2:D2)>C$1,"",D1+1)</td></tr></tbody></table></td></tr></tbody></table>
B1 = the date is May-2011
C1 =
A2 =
D2 =
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
VBA Code Copy/Replace Values base on Date
Replies
1
Views
107
Anthony47
A
dezibluenose
  • Solved
Countifs formula not picking up dates, when reference columns show as dates?!
Replies
7
Views
404
Alex Blakenburg
A
J
  • Solved
Sum up columns based on values (date and time) in more than one column
Replies
3
Views
260
myall_blues
myall_blues
D
  • Question Question
Clickable on Filter Formula
Replies
5
Views
135
Peter_SSs
Peter_SSs
R
  • Question Question
Linked dynamic named ranges - unexpected results.
Replies
2
Views
105
Ramses505
R

Forum statistics

Threads
1,223,270
Messages
6,171,103
Members
452,379
Latest member
IainTru

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top