Date table

alone

New Member
Joined
Mar 9, 2011
Messages
17
Hi
in Cell A2 Header with name Date
A3 date For example 2/15/2011
cells J2:J8 i wrote days name to fill in columnA
J2 = Sun
J3 = Tue
J4 = Fri
from J5 to J8 empty
names of days here is changable for table to table
could you write formula to fill days from A4:A32 to fill just dates Only days that match the criteria in the range J2:J8
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Ruddles

Well-known Member
Joined
Aug 24, 2010
Messages
5,786
Place this in A4 and copy down to A32:-
Code:
=IF(ISNA(MATCH(TEXT(A$3+ROW()-3,"ddd"),J$2:J$8,FALSE)),"",A$3+ROW()-3)
 

alone

New Member
Joined
Mar 9, 2011
Messages
17
Hi Ruddles
thank you it is working , but i don't want empty cells
 

Ruddles

Well-known Member
Joined
Aug 24, 2010
Messages
5,786
Ah, I misunderstood. If you want to eliminate the spaces you'll have to go for a VBA solution, I think.
 

Ruddles

Well-known Member
Joined
Aug 24, 2010
Messages
5,786
"We" can try this:-
Code:
Option Explicit
 
Public Sub FillDateColumn()
 
  Dim ws As Worksheet
  Dim dtNext As Date
  Dim iRow As Integer
  Dim wDay As String
  
  Set ws = ThisWorkbook.Sheets("Sheet1")
  dtNext = ws.Range("A3")
  iRow = 4
  Do Until iRow > 32
    dtNext = dtNext + 1
    wDay = Format(dtNext, "ddd")
    If Not IsError(Application.Match(wDay, ws.Range("J2:J8"), False)) Then
      ws.Cells(iRow, 1) = dtNext
      iRow = iRow + 1
    End If
  Loop
 
End Sub
That goes in a new standard code module. As it stands you would have to run it manually every time the table in J2:J8 changed...

or...

you could trigger it automatically any time the table was edited by adding this to your worksheet code module:-
Code:
Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
  If Not Intersect(Target, Range("J2:J8")) Is Nothing Then
    Call FillDateColumn
  End If
 
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,130,448
Messages
5,642,218
Members
417,262
Latest member
andrewd1

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
Top