FRI FUN! Auto-Populate based on Named Range w/ Simple Change to MO and YR?

[ChrisOK]

Looking for solution to achieve the following: (I created something like this a long time ago but can't find my saved example so I'm challenged with starting over w/ this Friday challenge!

==============|==============|
MONTH: JAN...|.. YEAR: 2014 | >>> (this named range H25:K34 is 'JAN2014' data)
==============================
Resource..Tech Orders...Urgent...Routine...
===========================================
Bill...........25...........5........15......
John........17...........7........10......
Bob.........40...........20.......20......
Harvey.....10...........1.........9......
Marvin......50..........25........25.....


==============|==============|
MONTH: FEB...|.. YEAR: 2014 | >>> (this named range H46:K55 is 'FEB2014' data)
==============================
Resource..Tech Orders...Urgent...Routine...
===========================================
Bill..........25...........5........15......
John.......17...........7........10......
Bob.........40.........20........20......
Harvey....10...........1.........9......
Marvin.....50.........25........25.....


The metrics analyst has a xlsm sheet that looks like the above with each month's data.

----------------------------------------------------------------------------------------------
Another sheet of that same file holds a roll up of report metrics similar to the BELOW example.
Everything on this summary sheet ultimately gets auto-fed/LINKED into a Mgmt .PPT file for monthly review.
I'd like to make it easy on them to be able to simply CHANGE the "MONTH" in (B1) to Feb and YEAR (C1) (as applicable)
and have all the numbers (B3:D7) update based on what they see on the other sheet with that named range.
.........Col A...........B...................C........D...............E.......F...
==================================================
==============================|...................G...|..O.|.
Row1..MONTH:.....JAN...|...YEAR:.2014 |....................O...|..T.|.
==============================..............G.....E...|..H.|.
Row2..Resource..Tech Orders...Urgent...Routine.|..R.....S...|..R.|.
====================================|..A..........|....|.
Row3..Bill.............25..................5.......15......|..P......H...|..G.|.
Row4..John..........17..................7.......10......|..H......E...|..R.|.
Row5..Bob...........40.................20.......20......|.........R...|..A.|.
Row6..Harvey......10...................1........9.......|.........E...|..P.|.
Row7..Marvin.......50.................25.......25......|..............|..H.|.

I'd like to streamline their labor to where, all they need to do each month is
Change the MONTH and YEAR of this summary sheet and the formulas or vba/macro be smart enough to look to
the other sheet, locate the range that matches that MONTH and YEAR and re-populate.

I created something like this 10+ yrs ago but forgot how...
Anyone know how to make the formulas/or code to be more dynamicly universal?

 

[Andrew Poulsom]

ROW(A6) returns 6 (the row number, the column reference is irrelevant) and COLUMN(B2) returns 2 (the column number, the row number is irrelevant). I wanted to return the item in row 6 (the row for Bill's data in the defined range), column 2 (the column for Tech Orders data in the defined range). I could have used:

=INDEX(INDIRECT($B$1&$C$1&"data"),6,2)

but then the row and column wouldn't change when the formula is copied down or across (or both). Using the ROW and COLUMN functions means that the formula does adjust relatively when copied.

 

[ChrisOK]

I guess what's puzzling me is -- that I was not able to use the exact refcs you gave -- I had to edit them to get it to work --- but neither makes sense.. (maybe I need more coffee - I'm hvg a 'block')

Here's my exact layouts of the sample file:
SHEET 1
A, B, C, D
The "named range" is as follows: =Sheet1!$A$1:$D$16
<style type="text/css">
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #ccc;
}
.tableizer-table th {
background-color: #104E8B;
color: #FFF;
font-weight: bold;
}
</style><table class="tableizer-table">
<tr class="tableizer-firstrow"><th>MONTH</th><th>JAN</th><th>YEAR</th><th>2014</th><th> ROW 1</th></tr>
<tr><td>RESOURCE</td><td>TECH ORDERS</td><td>URGENT</td><td>ROUTINE</td><td>ROW 2</td></tr>
<tr><td>Bill</td><td>14</td><td>2</td><td>3</td><td> ROW 3</td></tr>
<tr><td>John</td><td>8</td><td>7</td><td>10</td><td> ROW 4</td></tr>
<tr><td>Bob</td><td>40</td><td>20</td><td>8</td><td> ROW 5</td></tr>
<tr><td>Harvey</td><td>10</td><td>4</td><td>9</td><td> ROW 6</td></tr>
<tr><td>Marvin</td><td>555</td><td>4</td><td>77</td><td> ROW 7</td></tr>
<tr><td> </td><td> </td><td> </td><td> </td><td> ROW 8</td></tr>
<tr><td> </td><td> </td><td> </td><td> </td><td> ROW 9</td></tr>
<tr><td>MONTH</td><td>FEB</td><td>YEAR</td><td>2014</td><td> ROW 10</td></tr>
<tr><td>RESOURCE</td><td>TECH ORDERS</td><td>URGENT</td><td>ROUTINE</td><td> ROW 11</td></tr>
<tr><td>Bill</td><td>25</td><td>5</td><td>15</td><td> ROW 12</td></tr>
<tr><td>John</td><td>17</td><td>7</td><td>10</td><td> ROW 13</td></tr>
<tr><td>Bob</td><td>40</td><td>20</td><td>20</td><td> ROW 14</td></tr>
<tr><td>Harvey</td><td>10</td><td>1</td><td>9</td><td> ROW 15</td></tr>
<tr><td>Marvin</td><td>50</td><td>25</td><td>25</td><td> ROW 16</td></tr>
<tr><td>COL 1</td><td>COL 2</td><td>COL 3</td><td>COL 4</td><td></td></tr>
</table>
SHEET 2
(the one that's full of formulas and where the MONTH and YEAR are edited in order to suck in data from the data table on Sheet 1)
Here's what the formulas look like on ROW 3 "BILL" (which is very different from what you used)
and I'm not sure why -- one did not work and the other (below) did work?
A3 >> =INDEX(INDIRECT($B$1&$C$1&"data"),ROW(A1),COLUMN(A1))
B3 >> =INDEX(INDIRECT($B$1&$C$1&"data"),ROW(B1),COLUMN(B1))
C3 >> =INDEX(INDIRECT($B$1&$C$1&"data"),ROW(C1),COLUMN(C1))
D3 >> =INDEX(INDIRECT($B$1&$C$1&"data"),ROW(D1),COLUMN(D1))


<style type="text/css">
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #ccc;
}
.tableizer-table th {
background-color: #104E8B;
color: #FFF;
font-weight: bold;
}
</style><table class="tableizer-table">
<tr class="tableizer-firstrow"><th></th><th>JAN</th><th>2014</th><th> </th></tr>
<tr><td>RESOURCE</td><td>TECHORDERS</td><td>URGENT</td><td>ROUTINE</td></tr>
<tr><td>Bill</td><td>14</td><td>2</td><td>3</td></tr>
<tr><td>John</td><td>8</td><td>7</td><td>10</td></tr>
<tr><td>Bob</td><td>40</td><td>20</td><td>8</td></tr>
<tr><td>Harvey</td><td>10</td><td>4</td><td>9</td></tr>
<tr><td>Marvin</td><td>555</td><td>4</td><td>77</td></tr>
</table>

Assuming you are ok with the formula in A3 (which came about by accident)... Can you explain WHY it works?
A3 >> =INDEX(INDIRECT($B$1&$C$1&"data"),ROW(A1),COLUMN(A1))

B1 C1 is obviously looking to the desired MONTH and YEAR the user wishes to extract from the named range table..
ROW A1 (assuming based on what you sd, I should use the # as the ROW 1 (which would house the word "MONTH")
COLUMN A1 (assuming based on what you sd, I should use the # as the COL #1 (which would be A of the data table)
Yet, it returns "Bill" rather than the word "MONTH"...

Neither should work! - But they do!?
Hopefully you can explain based upon the above... THANKS SO MUCH

 

[Andrew Poulsom]

The row and column indexes are relative to the range not the entire worksheet.

 

[ChrisOK]

Yes, I understand that -- (or do I ?) see below...
(not sure why there's so much white space in the post) sorry in advance-- has something to do with pasting in the table examples w/ css probably...
Here's a snapshot of my orig file -- where I can't get the references/formulas working:
This is the data table named specifically: "DATATABLE" as a named range.
* The full named range is: H23:K56 (this includes the headers etc)
I've added additional range refs as I understand them-- pls correct any misconceptions...

<style type="text/css">
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #ccc;
}
.tableizer-table th {
background-color: #104E8B;
color: #FFF;
font-weight: bold;
}
</style><table class="tableizer-table">
<tr class="tableizer-firstrow"><th></th><th>Col #1 of range</th><th> #2 of range</th><th>#3 of range</th><th>#4 of range</th></tr>
<tr><td> </td><td>COL H</td><td>COL I</td><td>COL J</td><td>COL K</td></tr>
<tr><td>ROW 23 / Row 1 of RANGE</td><td>JAN</td><td>2014</td><td> </td><td> </td></tr>
<tr><td>ROW 23 / 2 of RANGE</td><td>RESOURCE</td><td>TECH ORDERS</td><td>URGENT</td><td>ROUTINE</td></tr>
<tr><td>ROW 23 / 3 of RANGE</td><td>Bill</td><td>14</td><td>2</td><td>3</td></tr>
<tr><td>ROW 23 / 4</td><td>John</td><td>8</td><td>7</td><td>10</td></tr>
<tr><td>ROW 23 / 5</td><td>Bob</td><td>40</td><td>20</td><td>8</td></tr>
<tr><td>ROW 23 / 6</td><td>Harvery</td><td>10</td><td>4</td><td>9</td></tr>
<tr><td>ROW 23 / 7</td><td>Marvin</td><td>555</td><td>4</td><td>77</td></tr>
<tr><td>ROW 23 / 8</td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>ROW 23 / 9</td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>ROW 23 / 10</td><td>FEB</td><td>2014</td><td> </td><td> </td></tr>
<tr><td>ROW 23 / 11</td><td>RESOURCE</td><td>TECH ORDERS</td><td>URGENT</td><td>ROUTINE</td></tr>
<tr><td>ROW 23 / 12</td><td>Bill</td><td>25</td><td>5</td><td>15</td></tr>
<tr><td>ROW 23 / 13</td><td>John</td><td>17</td><td>7</td><td>10</td></tr>
<tr><td>ROW 23 / 14</td><td>Bob</td><td>40</td><td>20</td><td>20</td></tr>
<tr><td>ROW 23 / 15</td><td>Harvery</td><td>10</td><td>1</td><td>9</td></tr>
<tr><td>ROW 23 / 16</td><td>Marvin</td><td>50</td><td>25</td><td>25</td></tr>
<tr><td>ROW 23 / 17</td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>ROW 23 / 18</td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>ROW 23 / 19</td><td>MAR</td><td>2014</td><td> </td><td> </td></tr>
<tr><td>ROW 23 / 20</td><td>RESOURCE</td><td>TECH ORDERS</td><td>URGENT</td><td>ROUTINE</td></tr>
<tr><td>ROW 23 / 21</td><td>Bill</td><td>14</td><td>2</td><td>3</td></tr>
<tr><td>ROW 23 / 22</td><td>John</td><td>8</td><td>7</td><td>10</td></tr>
<tr><td>ROW 23 / 23</td><td>Bob</td><td>40</td><td>20</td><td>8</td></tr>
<tr><td>ROW 23 / 24</td><td>Harvery</td><td>10</td><td>4</td><td>9</td></tr>
<tr><td>ROW 23 / 25</td><td>Marvin</td><td>555</td><td>4</td><td>77</td></tr>
<tr><td>ROW 23 / 26</td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>ROW 23 / 27</td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>ROW 23 / 28</td><td>APR</td><td>2014</td><td> </td><td> </td></tr>
<tr><td>ROW 23 / 29</td><td>RESOURCE</td><td>TECH ORDERS</td><td>URGENT</td><td>ROUTINE</td></tr>
<tr><td>ROW 23 / 30</td><td>Bill</td><td>25</td><td>5</td><td>15</td></tr>
<tr><td>ROW 23 / 31</td><td>John</td><td>17</td><td>7</td><td>10</td></tr>
<tr><td>ROW 23 / 32</td><td>Bob</td><td>40</td><td>20</td><td>20</td></tr>
<tr><td>ROW 23 / 33</td><td>Harvery</td><td>10</td><td>1</td><td>9</td></tr>
<tr><td>ROW 23 / 34</td><td>Marvin</td><td>50</td><td>25</td><td>25</td></tr>
</table>

Below is the reporting sheeet (sheet4) that the Analyst will simply change the MONTH and YEAR at the top to have it draw in data from the "DATATABLE" of (sheet3).
I don't know if the fact that some of the cells are MERGED will affect anything- but thought it important to share -- just in case... I'm thinking the column and row references of THIS TABLE don't matter --- only the references of the "DATATABLE" are of importance.... but not sure how to fix to get them to locate the correct data (ANY DATA for that matter) =-)
<style type="text/css">
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #ccc;
}
.tableizer-table th {
background-color: #104E8B;
color: #FFF;
font-weight: bold;
}
</style><table class="tableizer-table">
<tr class="tableizer-firstrow"><th></th><th>Col AI, AJ & AK (merged)</th><th>Col AL, AM & AN</th><th>Col AO</th><th>Col AP</th></tr>
<tr><td>Row 51</td><td>JAN</td><td>2014</td><td> </td><td> </td></tr>
<tr><td>Row 52</td><td>RESOURCE</td><td>TECHORDERS</td><td>URGENT</td><td>ROUTINE</td></tr>
<tr><td>Row 53</td><td>#REF!</td><td>#REF!</td><td>#REF!</td><td>#REF!</td></tr>
<tr><td>Row 54</td><td>#REF!</td><td>#REF!</td><td>#REF!</td><td>#REF!</td></tr>
<tr><td>Row 55</td><td>#REF!</td><td>#REF!</td><td>#REF!</td><td>#REF!</td></tr>
<tr><td>Row 56</td><td>#REF!</td><td>#REF!</td><td>#REF!</td><td>#REF!</td></tr>
<tr><td>Row 57</td><td>#REF!</td><td>#REF!</td><td>#REF!</td><td>#REF!</td></tr>
</table>

 

[Andrew Poulsom]

My formula expects there to be a range named in the form MMMYYYYdata, not DATATABLE. Concatenation inside INDIRECT is building the table name.