Need help with Cell linkages & Sorting

[yyxie]

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]

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]

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​	Name	Data02
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​	Name	Data02
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​	Name	Data02
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