Return data based on day of the week

[NullSpot]

Sheet 1, Column A, Rows 1:7 have data. Row 1 is Sunday, Row 2 is Monday, etc. I would like the file, when opened, to check the current day of the week, look at the data contained in sheet 1, Column A, Rows 1:7 and return the correct data (based on current day of the week) to Sheet 2, Cell A1.

Thanks for your help.

[mrice]

Try adding this code to the ThisWorkbook tab

Private Sub Workbook_Open()
Sheets("Sheet2").Cells(1, 1) = Sheets("Sheet1").Cells(Weekday(Date), 1)
End Sub

[jeffreybrown]

Something like this...

Private Sub Workbook_Open()
    Dim wsSrc As Worksheet: Set wsSrc = Sheets("Sheet1")
    Dim wsDest As Worksheet: Set wsDest = Sheets("Sheet2")
    Dim rngSrc As Range: Set rngSrc = wsSrc.Range("A1:A7")
    Dim rngDest As Range: Set rngDest = wsDest.Range("A1")
    Dim found As Range
    With wsSrc
        Set found = rngSrc.Find(What:=Date, LookIn:=xlValues)
        If Not found Is Nothing Then
            rngDest.Value = found.Value
        Else
            MsgBox "No Match"
        End If
    End With
End Sub

...but it's not entirely clear to me if you have a date (1/7/2018) or just the day of the week which is not a date.

[NullSpot]

I guess I figured it would use TODAY() in the code? Of course, I could add the Days of the Week next to the stretches, if necessary

[NullSpot]

Hey, I noticed you Live in Cibolo, I used to live in Schertz.

[NullSpot]

Here is the document

[jeffreybrown]

How about this...

=INDEX(Sheet1!A5:A11,WEEKDAY(TODAY(),2))

Today() is a volatile function which will return the date upon the opening of the spreadsheet.

Schertz, you are a long way from there now.

[NullSpot]

Thanks, I’ll give a try. 6 hours North.

[NullSpot]

That worked well. Thanks. Under the same conditions, if I wanted to return a random choice from the list of stretches, what would I change?

This is what I've tried

=INDEX(DATA!A19:A21,RANDBETWEEN(1, ROWS(DATA!A19:A21)),1)

[jeffreybrown]

Try...

=INDEX(DATA!A19:A21,RANDBETWEEN(1,3))

With this formula, every time you make a change to the spreadsheet or select F9, the formula will recalculate.

Randbetween is Volatile

[NullSpot]

Ok. Is the 3 in this formula representing the 3 rows? If I add more rows I would change this number?

[jeffreybrown]

Correct.

Since A19:A21 covers three rows, I figured you had three different responses to return and they return randomly.

Just adjust based on the number of different possible responses.