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]

You can use a formula like:

=INDEX(INDIRECT(B1&C1&"data"),ROW(A6),COLUMN(B2))

Note that in Excel 2007 and above you can't have the Names JAN2014 and FEB2014 (because they are valid cell references), so I appended data to them.

 

[ChrisOK]

Thx for the expeditious reply Andrew - I see your point on the Named Range (named it "JANFOURTEEN" and "FEBFOURTEEN" since I could not use a mix of numbers and letters as intended). However, I'm not sure where you'd use that formula?

Are you saying to replace the word "data" with the named range "FEBFOURTEEN" and place this formula throughout all the cells in the the bottom report summary table? (Sheet2)

If that's the case, the analyst will have to search/replace all the refs of "FEBFOURTEEN" with "MARFOURTEEN" and so forth to get every data cell to look at the correct month's table on Sheet1.

I was hoping to just change those 2 cells at the top (MONTH AND YR) and have all the formulas referring to those 2 -- when they go to Shee1 to extract the data and bring it over to the Sheet2 Summary reporting tab...

I'm probably just not following you --- if you could be a little more detailed on this particular offering - I'd greatly appreciate it =-)

 

[Andrew Poulsom]

I named H46:K55 FEB2014data. In B1 I entered FEB and in C1 I entered 2014. I entered my formula in B3 and it returned 25. Isn't that what you want?

To be able to copy the formula down and across a slight amendment is required:

=INDEX(INDIRECT($B$1&$C$1&"data"),ROW(B6),COLUMN(B2))

here's the resultant table:

Excel 2010
	A	B	C	D
1		FEB	2014
2
3	Bill	25	5	15
4	John	17	7	10
5	Bob	40	20	20
6	Harvey	10	1	9
7	Marvin	50	25	25
Sheet1

Cell Formulas
Range	Formula
A3	=INDEX(INDIRECT($B$1&$C$1&"data"),ROW(A6),COLUMN(A2))
B3	=INDEX(INDIRECT($B$1&$C$1&"data"),ROW(B6),COLUMN(B2))
C3	=INDEX(INDIRECT($B$1&$C$1&"data"),ROW(C6),COLUMN(C2))
D3	=INDEX(INDIRECT($B$1&$C$1&"data"),ROW(D6),COLUMN(D2))

 

[ChrisOK]

There are 2 sheets involved.
Sheet 1 holds the monthly tables full of data. (in my new test file, data sits in A1:D7 for JAN) (A10:D16 for FEB)
....................this Sheet1 is the one that has the named ranges (I named them as you did: "JAN2014data" and "FEB2014data")
Sheet 2 is the Summary/Reporting table where only 1 table that is going to be presented this month is visible.

This Sheet2 would be the one that should hold the formulas that would INDEX through Sheet1 to locate a table that holds "FEB 2014" data (assuming the user has typed "FEB" and "2014" into cell B1 and C1 of your Sheet2's cells)

Hopefully this makes better sense?

I attempted what you show above but get #REF!s.
If I edit it to look to Sheet1, with something like this: =INDEX(INDIRECT(Sheet1!$B$1&$C$1&"data"),ROW(B6),COLUMN(B2)) I still get #REF!s..

>> It seems like your formulas on Sheet2 should be referring to Sheet1 (b/c Sheet1 is where it needs to go to collect the data and bring it back over to the Summary/Report table on Sheet2)

 

[Andrew Poulsom]

If you are using Names it shouldn't matter that you have 2 sheets, unless the names are scoped to the worksheet. What names are you actually using?

 

[ChrisOK]

That's true -- forgot about that -- the named range refs should locate that data no mater what sheet it resides...

I've named 3 ranges:
1st -- I selected ALL the month's data ON SHEET 1 which is just JAN and FEB in my test file with range: A1:D16
2nd -- selected only JAN 2014 data ON SHEET 1 and named it what you noted: "JAN2014data")
3rd -- selected only FEB2014 data ON SHEET 1 and named it what you noted: "FEB2014data")

Obviously, the formula you provided simply refers to a range called "data" so, do I need to correct what is within that range?
I also notice that the other cell refs "ROW" and "COLUMN" don't seem to match up the way I'm thinking you've intended...
Is the ROW & COLUMN supposed to point to cells on the Sheet 1 or Sheet 2? (assume Sheet 2 since this is where the formulas reside)

Ok, here's what's in the cells of my Sheet 2 test file:
A1 - blank
A2 - title :RESOURCE"
A3 - formula =INDEX(INDIRECT($B$1&$C$1&"data"),ROW(A6),COLUMN(A2)) (COPIED DOWN)
A4 - formula =INDEX(INDIRECT($B$1&$C$1&"data"),ROW(A7),COLUMN(A3))
A5 - formula =INDEX(INDIRECT($B$1&$C$1&"data"),ROW(A8),COLUMN(A4))
A6 - formula =INDEX(INDIRECT($B$1&$C$1&"data"),ROW(A9),COLUMN(A5))
A7 - formula =INDEX(INDIRECT($B$1&$C$1&"data"),ROW(A10),COLUMN(A6))

B1 - "FEB"
B2 - title: "TECH ORDERS"
B3 - formula (SAME AS A3, BUT COPIED ACROSS TO RIGHT) =INDEX(INDIRECT($B$1&$C$1&"data"),ROW(B6),COLUMN(B2))
B4 =INDEX(INDIRECT($B$1&$C$1&"data"),ROW(B7),COLUMN(B3)) (copied down)
B5 =INDEX(INDIRECT($B$1&$C$1&"data"),ROW(B8),COLUMN(B4))
B6 =INDEX(INDIRECT($B$1&$C$1&"data"),ROW(B9),COLUMN(B5))
B7 =INDEX(INDIRECT($B$1&$C$1&"data"),ROW(B10),COLUMN(B6))

C1 "2014"
C2 - title "URGENT"
C3 =INDEX(INDIRECT($B$1&$C$1&"data"),ROW(C6),COLUMN(C2))
C4 =INDEX(INDIRECT($B$1&$C$1&"data"),ROW(C7),COLUMN(C3)) (copied down)
C5
C6
C7

D1 - blank
D2 - title "ROUTINE"
D3 =INDEX(INDIRECT($B$1&$C$1&"data"),ROW(D6),COLUMN(D2)) (copied down)
D4
D5
D6
D7

 

[ChrisOK]

ok -- started mapping back on the formula refs and got it all fixed up!
THANKS ANDREW! AWESOME SOLUTION
will post details shortly

 

[ChrisOK]

Ok, for others reading who want to be crystal clear:
When I'm on Sheet2 (my Main Report/Roll Up/ Summary tab) the only thing I have to change is cell "B1"'s month and all the data in the table below it auto-fills with data that pertains to THAT month!
It's the koolest thing! - You'll love it and again, many thanks to ANDEW POULSOM for his guidance and patience! LOL

Here's specifics to clarify how to build based on my test file sample:

1. Select the full range of data that you want used as your database of content..... name that single range "data" (to work w/ these formulas)
....In my case, my data was on Sheet1 in the area of: A1:D16
....as mentioned, when you name a range, it doesnt matter what sheet name it resides upon... your formula will locate that range..
....I was under the impression I needed to create a sep range for each month- but that was false... just do one single range =-)

2. go to the area that you want that data feeding into and build that new area with the following formula... (copy down and across)
....In my case, my Report/Summary table was on Sheet2 in the area of A1:D7
....Populate as follows:

Ok, here's what's in the cells of my Sheet 2 test file:
A1 - blank
A2 - title :RESOURCE"
A3 - formula =INDEX(INDIRECT($B$1&$C$1&"data"),ROW(A1),COLUMN(A1)) (COPIED DOWN)
A4 - formula =INDEX(INDIRECT($B$1&$C$1&"data"),ROW(A2),COLUMN(A2))
A5 - formula =INDEX(INDIRECT($B$1&$C$1&"data"),ROW(A3),COLUMN(A3))
A6 - formula =INDEX(INDIRECT($B$1&$C$1&"data"),ROW(A4),COLUMN(A4))
A7 - formula =INDEX(INDIRECT($B$1&$C$1&"data"),ROW(A5),COLUMN(A5))

B1 - "FEB"
B2 - title: "TECH ORDERS"
B3 - formula (SAME AS A3, BUT COPIED ACROSS TO RIGHT) =INDEX(INDIRECT($B$1&$C$1&"data"),ROW(B1),COLUMN(B1))
B4 =INDEX(INDIRECT($B$1&$C$1&"data"),ROW(B2),COLUMN(B2)) (copied down)
B5 =INDEX(INDIRECT($B$1&$C$1&"data"),ROW(B3),COLUMN(B3))
B6 =INDEX(INDIRECT($B$1&$C$1&"data"),ROW(B4),COLUMN(B4))
B7 =INDEX(INDIRECT($B$1&$C$1&"data"),ROW(C5),COLUMN(C5))

C1 "2014"
C2 - title "URGENT"
C3 =INDEX(INDIRECT($B$1&$C$1&"data"),ROW(C1),COLUMN(C1)) (copied down)
C4 =INDEX(INDIRECT($B$1&$C$1&"data"),ROW(C2),COLUMN(C2)) (copied down)
C5
C6
C7

D1 - blank
D2 - title "ROUTINE"
D3 =INDEX(INDIRECT($B$1&$C$1&"data"),ROW(D1),COLUMN(D1)) (copied down)
D4 =INDEX(INDIRECT($B$1&$C$1&"data"),ROW(D2),COLUMN(D2)) (copied down)
D5
D6
D7

 

[ChrisOK]

Andrew, can you expand upon the ROW(A6),COLUMN(B2) usage?
Trying to understand how it works --- WHY you chose specifically "A6" and "B2" in your initial statement....
"A6" seems so random as it falls in the middle of the data.... its not the "start" or the "end" of a range.... I don't get it.... hoping you can expand...

I've taken it all and gone from my simple test file example and trying to apply it to my real working file - which of course has totally diff refs and having issues..

You can use a formula like:

=INDEX(INDIRECT(B1&C1&"data"),ROW(A6),COLUMN(B2))

Note that in Excel 2007 and above you can't have the Names JAN2014 and FEB2014 (because they are valid cell references), so I appended data to them.