Named Ranges do not Sort

[Jtucker10278]

So Oh Holy ____!

I wish i would have known this 75 hours ago when i started making this beast of workbook


I have a list of 75 names in Column A

and for each name there is a named range for that row[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Wk1[/TD]
[TD]Wk2[/TD]
[TD]Wk3[/TD]
[TD]Wk4[/TD]
[TD]Wk5[/TD]
[TD]Wk6
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]John[/TD]
[TD]10[/TD]
[TD]12[/TD]
[TD]9[/TD]
[TD]13[/TD]
[TD]15[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]James[/TD]
[TD]6[/TD]
[TD]5[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]4[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]David[/TD]
[TD]9[/TD]
[TD]8[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]9[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Lamont[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]6[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Dustin[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]8[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Dan[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]6[/TD]
[TD]4[/TD]
[TD]8[/TD]
[TD]7[/TD]
[/TR]
</tbody>[/TABLE]

B2:G2 = John (named range)
B3:G3 = James (named range)

ect ect ect

Now lets say I want/need to sort the names (and their respective date) alphabetically.

The names Sort, The data sorts, but the named ranges do not sort.

Any thoughts?

Thanks

 

[StephenCrump]

I think you're painting yourself into a corner having a different range name for each person.

I'd have a single data range name: MyData:=B2:G7

Then you can do things like this:

C10: =IFERROR(MIN(INDEX(MyData,MATCH(B10,A2:A7,),)),"n/a")
D10: =IFERROR(MAX(INDEX(MyData,MATCH(B10,A2:A7,),)),"n/a")

Excel 2010
	A	B	C	D	E	F	G
1		Wk1	Wk2	Wk3	Wk4	Wk5	Wk6
2	John	10	12	9	13	15	7
3	James	6	5	7	8	4	2
4	David	9	8	6	7	9	8
5	Lamont	3	2	6	2	3	5
6	Dustin	1	5	2	4	8	5
7	Dan	1	2	6	4	8	7
8
9			Min Sale	Max Sale
10		David	6	9
Sheet1

 

[Jtucker10278]

So my original post was a bit over simplified (see below)

in addition to my names being named ranges i also have my weeks set as name ranges so i can do intersection sums via the indirect function

=IFERROR(SUM(INDIRECT(C$3) INDIRECT($B10)),"") with this formula i can copy and paste it into all 75 names and 52 weeks and im all set

I did figure out that i can manualy adjust the rows and the named ranges will follow suit. all i really need it for is to add new names in order down the road

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
3		Week_1						Week_2						Week_3
4		Mon	Tue	Wed	Thu	Fri	Sat	Mon	Tue	Wed	Thu	Fri	Sat	Mon	Tue	Wed	Thu	Fri	Sat
5		1	2	3	4	5	6	8	9	10	11	12	13	15	16	17	18	19	20
6
7	HOURS	8	8	8	8	8		8	8	8	8	8		8	8	8	8	8	6
8	ASM_COUNT	11	11	10	8	10	0	10	8	10	10	10	1	10	9	10	9	10	5
9	TOTAL	44	58	43	37	55	0	56	34	65	56	67	4	70	57	56	48	63	33
10
11	OTHER	1	1											1
12	A_Sanchez	4	3	1	1	6		2	4	5	7	7		3	8	7	1	4	11
13	A_Holmes	11	5	1	2	1		12		8	11	3			1	9	9	11
14	B_Perez	2	11	7		2		4	3	4	3	6		9	1	9	7	11	8
15	C_Reynolds	3	1	4		7		5	8	6	3	6		11	5	1	4	12
16	M_Lopez	1	3	6	8	3		1	3	1	7	12		6		4		3	4
17	M_Reynolds	6	11	4	5	6		4	1	11	2	9		11	4	5	9	2
18	M_Tran	3	7	3	6	12		4		4	11	11		12	12	4	4	6	6
19	N_Fonseca	12	9	7	1	5		9	5	11	1	2		8	12	3	7	6
20	S_Hernandez	0	6	6	11	12		11	9	12	9	8	4	5	9	9	6	7
21	T_Stohler	1	1	4	3	1		4	1	3	2	3		4	5	5	1	1	4
Input Table

as a side note I really wish i would have taken the time to figure out that HTML Maker a long time ago, that makes life super easy[h=2][/h]

 

[StephenCrump]

You can use an Index/Match approach to look up both row and column. Here's one way you could do this without using multiple range names (and the volatile Indirect function).

MyData: C12:O15
Names: B12:B15
B20: =SUMPRODUCT(INDEX(MyData,MATCH(B18,Names,),)*(COLUMN(MyData)>=MATCH(B19,3:3,))*(COLUMN(MyData)<6+MATCH(B19,3:3,)))

Or you could just use a pivot table.

Excel 2010
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
3		Week_1						Week_2						Week_3
4		Mon	Tue	Wed	Thu	Fri	Sat	Mon	Tue	Wed	Thu	Fri	Sat	Mon
5		1	2	3	4	5	6	8	9	10	11	12	13	15
6
7	HOURS	8	8	8	8	8		8	8	8	8	8		8
8	ASM_COUNT	11	11	10	8	10	0	10	8	10	10	10	1	10
9	TOTAL	44	58	43	37	55	0	56	34	65	56	67	4	70
10
11	OTHER	1	1											1
12	A_Sanchez	4	3	1	1	6		2	4	5	7	7		3
13	A_Holmes	11	5	1	2	1		12		8	11	3
14	B_Perez	2	11	7		2		4	3	4	3	6		9
15	C_Reynolds	3	1	4		7		5	8	6	3	6		11
16
17
18	A_Sanchez
19	Week_2
20	25
Sheet1

 

[Jtucker10278]

So I have several comments/questions

1. Why does everyone seem to frown on INDIRECT function? What are its issues? it has served me well so far, but that word volatile makes me think at some point Ill regret having used it.

2. Repeat paste formulas. My input sheet is set up with (col) 52 "Week_#" NamedRange, and (row) 75 "names" NamedRange.
Then In four other Results tables
Totals By Week =IFERROR(SUM(INDIRECT(C$3) INDIRECT($B10)),"")
Daily Avg By Week =IFERROR(AVERAGE(INDIRECT(C$3) INDIRECT($B10)),"")
Hourly Avg By Week =IFERROR(Wk_Totals!C10/SUMPRODUCT((INDIRECT(Wk_Hour_Avg!C$3) Hours)*((INDIRECT(Wk_Hour_Avg!C$3) INDIRECT(Wk_Hour_Avg!$B10))<>"")),"")
% of total by Week =IFERROR(SUM(Wk_Totals!C10/Wk_Totals!C$7),"")

The benefit to all of these formulas are that i can paste them in all 3900 cells (52col by 75row) and they work

The most important part of using the INDIRECT function is that I understand it and know how to use it. Which brings me to my last question

3. Sumproduct. I understand sumproduct's intended use of "Summing Products" but the hidden mystery use of magically looking things up and cross referencing I am lost. further more I can find anything that explains how its secondary use works. (at least not in a way i understand) And I am by no means a novice with excel (I'm not a wizard) but I would love to figure it out if you know of any reference material that could help me

 

[StephenCrump]

1. INDIRECT is a volatile function, which means that it recalculates every time Excel recalculates. So you may find that with 3,900 cells updating every time Excel recalculates, your workbook is a bit sluggish. If it's performing OK, I wouldn't stress too much about using volatile functions. More info: http://www.decisionmodels.com/calcsecretsi.htm

2. The nice thing about Excel is that you can do things many different ways. There's nothing wrong with your approach, although you have identified a couple of drawbacks, e.g. having to maintains multiple range names, and the sort issue identified in the original post. A pivot table would be a possible alternative. My formula approach, with one range name is another. There may be smarter alternatives.

3. Check out these two articles on the Sumproduct and Index functions:

http://www.excelhero.com/blog/2010/01/the-venerable-sumproduct.html
http://www.excelhero.com/blog/2011/03/the-imposing-index.html

In terms of Excel resources generally, forum member, hiker95, has assembled this list (see Post#2)

https://www.mrexcel.com/forum/lounge-v-2-0/1031751-two-novice-questions.html