I need a strategy for combining two sheets

[revver]

I have a workbook with twelve sheets named Jan, Feb, Mar ... Nov, Dec.
Each sheet is similar and quite simple. Row 1 contains headings. Column A contains increasing dates beginning at row 2 and extending to row 29, 31 or 32 depending on the month. The next three columns (B, C, D) contain the three readings for the day. That is all that is relevant to my problem really. The rest is max, min averages etc and a graph.

I want to add a thirteenth sheet laid out in a similar way but showing a portion of two consecutive sheets.
I want to enter a date somewhere on it and have it retrieve the three daily readings for that date from the appropriate sheet(s) and so on for 30 more days.

Lets say I enter Feb 17. I want the new sheet to show the daily readings for the month which begins on that date. I realize that I will have to begin with the 'Feb' sheet and then switch to the 'Mar' sheet.

Is the best strategy to use vba to copy data from 'Feb' and 'Mar'
or is there a way to do this with formula.

I hope I have explained this satisfactorily.

 

[Peter_SSs]

Is the best strategy to use vba to copy data from 'Feb' and 'Mar'
or is there a way to do this with formula.

If I have understood correctly, below are both vba and formula methods. Note that ..
i) I have used dates from 1 Jan 2017 to 31 Dec 2017 in my sample workbook
ii) The formula method involves a number of uses of the volatile function INDIRECT so may cause your sheet to recalculate more often than normal.

Try each method in a new copy of your workbook

Formula Method
On the 13th sheet, set up row 1 as shown.
Formula in A2 is copied down to A32
Formula in B2 is copied across to D2 and down the columns to row 32.

Excel Workbook
	A	B	C	D
1	Date	17/02/2017
2	17/02/2017	19	32	56
3	18/02/2017	49	74	98
4	19/02/2017	65	19	15
5	20/02/2017	23	85	52
6	21/02/2017	50	45	60
7	22/02/2017	16	68	28
8	23/02/2017	80	95	37
9	24/02/2017	11	60	42
10	25/02/2017	20	65	16
11	26/02/2017	67	92	45
12	27/02/2017	73	80	65
13	28/02/2017	1	82	43
14	1/03/2017	18	11	28
15	2/03/2017	40	68	67
16	3/03/2017	62	12	52
17	4/03/2017	39	5	40
18	5/03/2017	26	72	48
19	6/03/2017	25	99	57
20	7/03/2017	60	24	71
21	8/03/2017	54	37	77
22	9/03/2017	93	76	29
23	10/03/2017	74	76	22
24	11/03/2017	17	45	87
25	12/03/2017	55	46	16
26	13/03/2017	93	31	51
27	14/03/2017	9	34	10
28	15/03/2017	44	65	59
29	16/03/2017	27	92	59
30	17/03/2017	36	76	35
31	18/03/2017	31	36	36
32	19/03/2017	11	97	70
Sheet13

VBA Method

1. On the 13th sheet, enter "Date" in A1 as for the formula method, but leave B1 empty.
2. Right click the sheet name tab and choose "View Code".
3. Copy and Paste the code below into the main right hand pane that opens at step 1.
4. Close the Visual Basic window & test by entering a date in B1
5. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim a(1 To 31, 1 To 4) As Variant
  Dim i As Long, j As Long
  Dim StartDate As Date
  
  If Not Intersect(Target, Range("B1")) Is Nothing Then
    Application.EnableEvents = False
    Range("A2:D32").ClearContents
    If IsDate(Range("B1").Text) Then
      StartDate = Range("B1").Value
      For i = 1 To 31
        a(i, 1) = StartDate + i - 1
        With Sheets(Format(a(i, 1), "mmm"))
          For j = 2 To 4
            a(i, j) = .Cells(Day(a(i, 1)) + 1, j).Value
          Next j
        End With
      Next i
      Range("A2:D32").Value = a
    End If
    Application.EnableEvents = True
  End If
End Sub

 

[revver]

Thanks very much Peter, G'day.
I wasn't expecting anyone to do my work for me. I was about to roll up my sleeves and unwrap my vba when it occurred to me that a formula technique might work. I figured that avoiding vba would be preferred because of being able to save as .xls and not having to explicitly launch the code. So I took to print for suggestions.

I have implemented your formula method and it works well. I will also try the other method for my education but I'll stick with the first for simplicity.

 

[Peter_SSs]

You are welcome, and using the formula version is fine by me.

Note, though, that the vba method suggested does not require "having to explicitly launch the code" as it is automatically launched when a date is entered in cell B1. The user does need to have macros enabled though.

(BTW, feels like Brisbane weather here - very warm & humid! )