Excel Table sort using cell references...

[Jimmypop]

Good day all

I have a Table1. Two Columns. The one columns has site names and the other columns has values inside them. These values are populated from a cell refreence on the same sheet e.g. =AI2...

Now my issue is when I sort the table values column from largest to smallest it only sorts the first columns (site names) and does not sort the values column (which come from the cell references)... Appreciate any assistance in this regard...

 

[Fluff]

Try locking the rows like =AI$2

 

[AOB]

Have you added a second sort level?

Sort by : Column 1, Then By : Column 2 ?

 

[Jimmypop]

Try locking the rows like =AI$2

Hi Fluff

Then my table only references the first row... so basically goes from this:

Near Misses for month end report.xlsx
	A	B
44	Site	Total NM Reported
45	Site1	1
46	Site2	12
47	Site3	1
48	Site4	2
49	Site5	7
50	Site6
51	Site7	14
52	Site8	2
53	Site9
54	Site10	11
55	Site11	1
56	Site12	13
57	Site13
58	Site14	26
59	Site15	1
60	Site16
61	Site17	1
62	Site18	4
63	Site19	23
64	Site20	1
65	Site21
66	Site22	19
67	Site23	15
68	Site24
69	Site25
70	Site26
71	Site27	1
72	Site28
73	Site29	4
74	Site30
75	Site31
76	Site32
77	Site33
78	Site34
79	Site35
80	Site36
81	Site37	5
82	Site38
83	Site39
84	Site40	717
Sheet1

Cell Formulas
Range	Formula
B45:B84	B45	=AI2

to this

Near Misses for month end report.xlsx
	A	B
44	Site	Total NM Reported
45	Site1	1
46	Site2	1
47	Site3	1
48	Site4	1
49	Site5	1
50	Site6	1
51	Site7	1
52	Site8	1
53	Site9	1
54	Site10	1
55	Site11	1
56	Site12	1
57	Site13	1
58	Site14	1
59	Site15	1
60	Site16	1
61	Site17	1
62	Site18	1
63	Site19	1
64	Site20	1
65	Site21	1
66	Site22	1
67	Site23	1
68	Site24	1
69	Site25	1
70	Site26	1
71	Site27	1
72	Site28	1
73	Site29	1
74	Site30	1
75	Site31	1
76	Site32	1
77	Site33	1
78	Site34	1
79	Site35	1
80	Site36	1
81	Site37	1
82	Site38	1
83	Site39	1
84	Site40	1
Sheet1

Cell Formulas
Range	Formula
B45:B84	B45	=AI$2

And need it sorted like: (which I can do but only if I convert the table back to a range and then sort - I would however like to keep the table)

Near Misses for month end report.xlsx
	A	B
44	Site	Total NM Reported
45	Site40	717
46	Site14	26
47	Site19	23
48	Site22	19
49	Site23	15
50	Site7	14
51	Site12	13
52	Site2	12
53	Site10	11
54	Site5	7
55	Site37	5
56	Site18	4
57	Site29	4
58	Site4	2
59	Site8	2
60	Site1	1
61	Site3	1
62	Site11	1
63	Site15	1
64	Site17	1
65	Site20	1
66	Site27	1
67	Site6
68	Site9
69	Site13
70	Site16
71	Site21
72	Site24
73	Site25
74	Site26
75	Site28
76	Site30
77	Site31
78	Site32
79	Site33
80	Site34
81	Site35
82	Site36
83	Site38
84	Site39
Sheet1

 

[Jimmypop]

Have you added a second sort level?

Sort by : Column 1, Then By : Column 2 ?

Hi AOB...

No there is no secondary sort level....

 

[AOB]

What is the relationship between the cell reference (AI2) and the name of the site? Is it a lookup?

 

[Jimmypop]

What is the relationship between the cell reference (AI2) and the name of the site? Is it a lookup?

Just a straight formula... Formula in AI2 is =SUM(B2:AH2)

 

[Fluff]

You need to lock each cell individually, not lock the 1st one & drag down.
Select the range & use Replace (Ctrl H) to replace AI with AI$

 

[RoryA]

This doesn't seem like good design to me, but you can include the sheet name in your formula link and it should then sort as you want.

 

[Jimmypop]

You need to lock each cell individually, not lock the 1st one & drag down.
Select the range & use Replace (Ctrl H) to replace AI with AI$

Thanks Fluff... this works now...