I have a table that is set up with actual monthly dates from D5 through AC15 (defaults to first day of month).
Data is at various rows beneath from D6:D9 through AC6:AC9.
I want to create a second lookup table that will transpose the dates and associated data in vertical format listing the transposed dates (my month). However I want to only transpose those 12 months based on the year selected in AD5. The header in AD5 is a drop down for “Total 2015”, “Total 2016” etc. So I only need the numeric year portion of the text string. I thought it would be easy to isolate the year portion and match to the header rows, but I was unable to do that.
For the transposed dates, I tried using the following to create the months:
drag down.
It works if I force the range just right (ie., only the months in 2016). In this case starting with P5 = 1/1/2016.
The adjacent cell starting in AJ6 grabs the corresponding data:
which works fine.
So I need the lookup table (with transposed dates) to be flexible to allow the user to pick any year and have all of the months in that year transposed and its corresponding data in adjacent columns.
Excel 2010.
Excel 2010
<tbody>
</tbody>
<tbody>
</tbody>
Excel 2010
<tbody>
</tbody>
<tbody>
</tbody>
Data is at various rows beneath from D6:D9 through AC6:AC9.
I want to create a second lookup table that will transpose the dates and associated data in vertical format listing the transposed dates (my month). However I want to only transpose those 12 months based on the year selected in AD5. The header in AD5 is a drop down for “Total 2015”, “Total 2016” etc. So I only need the numeric year portion of the text string. I thought it would be easy to isolate the year portion and match to the header rows, but I was unable to do that.
For the transposed dates, I tried using the following to create the months:
Code:
[FONT=Calibri][COLOR=#0000cd]AV6 = INDEX($P$5:$AA6,1,ROW()-5[/COLOR][COLOR=#000000])[/COLOR][/FONT]
drag down.
It works if I force the range just right (ie., only the months in 2016). In this case starting with P5 = 1/1/2016.
The adjacent cell starting in AJ6 grabs the corresponding data:
Code:
AJ6 =INDEX($D$8:$AC$8,MATCH(AI6,$D$5:$AC$5,0))
which works fine.
So I need the lookup table (with transposed dates) to be flexible to allow the user to pick any year and have all of the months in that year transposed and its corresponding data in adjacent columns.
Excel 2010.
Excel 2010
B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
5 | Customer | Metrics | Jan 2015 | Feb 2015 | Mar 2015 | Apr 2015 | May 2015 | Jun 2015 | Jul 2015 | Aug 2015 | Sep 2015 | Oct 2015 | Nov 2015 | Dec 2015 | Jan 2016 | Feb 2016 | Mar 2016 | Apr 2016 | May 2016 | Jun 2016 | Jul 2016 | Aug 2016 | Sep 2016 | Oct 2016 | Nov 2016 | Dec 2016 | Jan 2017 | Feb 2017 | Total 2016 |
6 | Anderson | Sector 1 | 45 | 76 | 59 | 57 | 58 | 57 | 59 | 60 | 49 | 53 | 55 | 58 | 57 | 53 | 56 | 54 | 54 | 17 | 52 | 54 | 17 | 55 | 53 | 53 | 17 | 45 | 575 |
7 | Kirkpatrick | Sector 1 | 21 | 63 | 51 | 49 | 51 | 39 | 36 | 50 | 51 | 57 | 67 | 67 | 65 | 62 | 62 | 53 | 45 | 40 | 46 | 43 | 50 | 62 | 41 | 34 | 32 | 569 | |
8 | Johnson | Sector 2 | 23 | 29 | 34 | 35 | 36 | 31 | 29 | 27 | 27 | 26 | 34 | 41 | 39 | 32 | 34 | 31 | 32 | 32 | 32 | 28 | 29 | 30 | 32 | 43 | 46 | 92 | 394 |
9 | Kiefer | Sector 2 | 31 | 15 | 23 | 27 | 22 | 11 | 6 | 4 | 5 | 17 | 45 | 56 | 30 | 18 | 23 | 22 | 21 | 12 | 14 | 15 | 7 | 8 | 13 | 39 | 42 | 14 | 222 |
<tbody>
</tbody>
ACU Data
Worksheet Formulas
<tbody> </tbody> |
<tbody>
</tbody>
Excel 2010
AI | AJ | AK | |
---|---|---|---|
5 | Date | Johnson | Kiefer |
6 | Jan 2016 | 39 | 30.0 |
7 | Feb 2016 | 32 | 17.9 |
8 | Mar 2016 | 34 | 23.0 |
9 | Apr 2016 | 31 | 21.7 |
10 | May 2016 | 32 | 20.6 |
11 | Jun 2016 | 32 | 12.2 |
12 | Jul 2016 | 32 | 14.5 |
13 | Aug 2016 | 28 | 15.4 |
14 | Sep 2016 | 29 | 7.0 |
15 | Oct 2016 | 30 | 7.6 |
16 | Nov 2016 | 32 | 12.9 |
17 | Dec 2016 | 43 | 39.5 |
<tbody>
</tbody>
ACU Data
Worksheet Formulas
<tbody> </tbody> |
<tbody>
</tbody>