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

C

ChrisOK

Well-known Member
Joined
Mar 26, 2003
Messages
601
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?
 
Last edited:
Sort by date Sort by votes
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.
 
Upvote 0

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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
 
Upvote 0
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>
 
Upvote 0
My formula expects there to be a range named in the form MMMYYYYdata, not DATATABLE. Concatenation inside INDIRECT is building the table name.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Pivot table? List? Advanced
Replies
1
Views
377
DanteAmor
DanteAmor
MrDB4Excel
  • Question Question
Populate Date to Another Workbook
Replies
7
Views
140
MrDB4Excel
MrDB4Excel
john Smith72
  • Solved
Auto filter VBA
Replies
2
Views
350
john Smith72
john Smith72
M
  • Question Question
Displaying number based on date conditions column and True/False column.
Replies
2
Views
247
mike4777
M
S
  • Question Question
Create New Sheet with Specific Name Based on Variable Cell
Replies
0
Views
175
SBExcel123
S

Forum statistics

Threads
1,223,270
Messages
6,171,103
Members
452,379
Latest member
IainTru

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top