Mr. Excel,
I have an array that 63 rows & 39 columns (Dates). The first column is the County and other columns are metrics for that columns date. I want to have a single formula that extracts first column & the last 10 columns. The number of columns is dynamic & changes daily as I add another day's figure to the array. I created a formula using choose({1,2,3,4,5,6,7,8,9,10,11},Range1, Range2, Range3,Range4, Range5......,Range11). This isn't a great formula. I tried indirect, filter, offset without success. A formula like =(A1:A10;AA1:AK10) would work but I can't make it dynamic. Any ideas of a better way to do this?
Data Looks like this:
County 3/27 3/28 3/39 ..............................................................5/1 5/2 5/3 5/4 5/5 5/6 5/7 5/8 5/9 5/10
Adams 5 7 9 51 55 67 69 75 77 78 81 89 90
Jefferson 8 9 9 .............................................................. 80 83 90 99 100 101 105 106 107 109
.
.
.
.
.
Last County 8 10 11 .............................................................. 80 83 90 99 100 101 105 106 107 109
So I want it to be:
County 5/1 5/2 5/3 5/4 5/5 5/6 5/7 5/8 5/9 5/10
Adams 51 55 67 69 75 77 78 81 89 90
Jefferson 80 83 90 99 100 101 105 106 107 109
.etc----------------------------------------------------------
I have an array that 63 rows & 39 columns (Dates). The first column is the County and other columns are metrics for that columns date. I want to have a single formula that extracts first column & the last 10 columns. The number of columns is dynamic & changes daily as I add another day's figure to the array. I created a formula using choose({1,2,3,4,5,6,7,8,9,10,11},Range1, Range2, Range3,Range4, Range5......,Range11). This isn't a great formula. I tried indirect, filter, offset without success. A formula like =(A1:A10;AA1:AK10) would work but I can't make it dynamic. Any ideas of a better way to do this?
Data Looks like this:
County 3/27 3/28 3/39 ..............................................................5/1 5/2 5/3 5/4 5/5 5/6 5/7 5/8 5/9 5/10
Adams 5 7 9 51 55 67 69 75 77 78 81 89 90
Jefferson 8 9 9 .............................................................. 80 83 90 99 100 101 105 106 107 109
.
.
.
.
.
Last County 8 10 11 .............................................................. 80 83 90 99 100 101 105 106 107 109
So I want it to be:
County 5/1 5/2 5/3 5/4 5/5 5/6 5/7 5/8 5/9 5/10
Adams 51 55 67 69 75 77 78 81 89 90
Jefferson 80 83 90 99 100 101 105 106 107 109
.etc----------------------------------------------------------