And a macro solution to go with A.A.'s fine formula set -
Data
Book1 |
---|
|
---|
| A | B | C | D | E | F |
---|
1 | | Jan-05 | Feb-05 | Mar-05 | Apr-05 | May-05 |
---|
2 | Cust 1 | 10 | 20 | 5 | 6 | |
---|
3 | Cust 2 | 20 | 5 | 6 | 10 | |
---|
4 | Cust 3 | 5 | 6 | 10 | 20 | |
---|
5 | Cust 4 | 6 | 10 | 20 | 5 | |
---|
6 | | | | | | |
---|
|
---|
You input -
Book1 |
---|
|
---|
| A | B | C | D | E |
---|
1 | Start Dt= | Feb-05 | End Dt= | Mar-05 | Run Now |
---|
2 | | | | | |
---|
|
---|
After clicking on cell E1, you get -
Book1 |
---|
|
---|
| A | B | C | D | E |
---|
1 | Start Dt= | Feb-05 | End Dt= | Mar-05 | Run Now |
---|
2 | | Feb-05 | Mar-05 | | |
---|
3 | Cust 1 | 20 | 5 | | |
---|
4 | Cust 2 | 5 | 6 | | |
---|
5 | Cust 3 | 6 | 10 | | |
---|
6 | Cust 4 | 10 | 20 | | |
---|
|
---|
Code --<font face=Courier New><SPAN style="color:#00007F">Private</SPAN><SPAN style="color:#00007F">Sub</SPAN> Worksheet_SelectionChange(<SPAN style="color:#00007F">ByVal</SPAN> Target<SPAN style="color:#00007F">As</SPAN> Range)<SPAN style="color:#00007F">If</SPAN> Intersect(Target, [E1])<SPAN style="color:#00007F">Is</SPAN><SPAN style="color:#00007F">Nothing</SPAN><SPAN style="color:#00007F">Then</SPAN><SPAN style="color:#00007F">Exit</SPAN><SPAN style="color:#00007F">Sub</SPAN><SPAN style="color:#007F00">' execute only on E1</SPAN>
Application.EnableEvents =<SPAN style="color:#00007F">False</SPAN>
Application.ScreenUpdating =<SPAN style="color:#00007F">False</SPAN><SPAN style="color:#00007F">Dim</SPAN> i<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Integer</SPAN>, j<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Integer</SPAN>, k<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Long</SPAN><SPAN style="color:#00007F">With</SPAN> Application.WorksheetFunction<SPAN style="color:#007F00">' find the columns of interest in the data table</SPAN>
Range("A2:M" & .Max(2, Cells(Rows.Count, "A").End(xlUp).Row)).Clear<SPAN style="color:#007F00">' get rid of prior data</SPAN>
i = .Match(CLng([B1].Value), Sheets("Sheet1").Range("A1:M1"), 0)<SPAN style="color:#007F00">' find 1st column</SPAN>
j = .Match(CLng([D1].Value), Sheets("Sheet1").Range("A1:M1"))<SPAN style="color:#007F00">' find ending column</SPAN><SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">With</SPAN>
k = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row<SPAN style="color:#007F00">' end of data down in table</SPAN><SPAN style="color:#00007F">With</SPAN> Sheets("Sheet1")
.Range("A1:A" & k).Copy Range("A2")<SPAN style="color:#007F00">' copy the customers over</SPAN>
.Range(.Cells(1, i), .Cells(k, j)).Copy Range("B2")<SPAN style="color:#007F00">' and the months</SPAN><SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">With</SPAN>
Application.CutCopyMode =<SPAN style="color:#00007F">False</SPAN><SPAN style="color:#007F00">' get rid of marching ants</SPAN>
Application.Goto [B1]<SPAN style="color:#007F00">' return to cell</SPAN>
Application.ScreenUpdating =<SPAN style="color:#00007F">True</SPAN>
Application.EnableEvents =<SPAN style="color:#00007F">True</SPAN><SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Sub</SPAN></FONT>