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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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)
 
Upvote 0
Ah, I misunderstood. If you want to eliminate the spaces you'll have to go for a VBA solution, I think.
 
Upvote 0
"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
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,272
Members
448,558
Latest member
aivin

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