Need help with Cell linkages & Sorting

yyxie

New Member
Joined
Apr 2, 2019
Messages
1
Need help on this issue I have with excel:
Sheet 1: master copy with columns of data, A, B, C
Sheet 2: summary of minimum value linked from sheet 1, but of only selected cells with no fixed order; eg MIN('sheet1' B2, 'sheet 1' B4, 'sheet 1' B5)

However, when I Sort my sheet 1 data, say column B from smallest to largest, or column C in alphabetical order, my sheet 2 summary value changes because the numbers in the cell in sheet 1 changes.
Filtering does not have this issue because irrelevant rows are simply hidden, but Sort is different.

So to ensure my sheet 2 summary values are accurate, I have to forever revert my sheet 1 to the original condition?

Any way to solve this?
Really appreciate any advise you guys may have! Thanks in advance!
 

DSCfromCFA

Board Regular
Joined
Feb 27, 2018
Messages
122
I have tried a couple of things, and none of them solved the problem. I think you are going to need to use a helper column to identify the cells in Sheet 1 and use Index/Match to generate the summary data which will track the source cells as you sort. (VLookup probably won't work as the data needs to be sorted in ascending order to work, and you suggest that this may not be the case as you sort on various ways).
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,672
Office Version
365
Platform
Windows
Do you have a fixed unique value in any of the columns? Use Index and Match (or Vlookup if in column A)
=MIN(INDEX(Sheet1!B:B,MATCH("name01",Sheet1!A:A,0)),INDEX(Sheet1!B:B,MATCH("name03",Sheet1!A:A,0)),INDEX(Sheet1!B:B,MATCH("name04",Sheet1!A:A,0)))

returns 14

Excel 2016 (Windows) 32 bit
A
B
1
NameData02
2
name01
69​
3
name02
82​
4
name03
56​
5
name04
14
6
name05
58​
7
name06
36​
8
name07
48​
9
name08
29​
10
name09
10​
11
name10
85​
12
name11
42​
13
name12
51​
14
name13
85​
15
name14
93​
16
name15
53​
Sheet: Sheet1

After sorting data updated and sorted - returns 56

Excel 2016 (Windows) 32 bit
A
B
1
NameData02
2
name15
57​
3
name14
37​
4
name13
76​
5
name12
60​
6
name11
75​
7
name10
46​
8
name09
58​
9
name08
25​
10
name07
76​
11
name06
11​
12
name05
39​
13
name04
56
14
name03
75​
15
name02
18​
16
name01
71​
Sheet: Sheet1


After sorting data updated and sorted - returns 32

Excel 2016 (Windows) 32 bit
A
B
1
NameData02
2
name13
35​
3
name07
29​
4
name11
77​
5
name03
81​
6
name01
32
7
name12
40​
8
name09
93​
9
name15
42​
10
name04
56​
11
name10
44​
12
name05
37​
13
name14
84​
14
name08
70​
15
name02
75​
16
name06
61​
Sheet: Sheet1
 

Forum statistics

Threads
1,082,126
Messages
5,363,315
Members
400,725
Latest member
excelingtolearn

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top