Keep a Lookup "static"

[gtd526]

Hello,
Want to keep the listing 'static' or not rearranging the list, its still correct, but rearranged.
When I save the workbook, it rearranges the list.

NBA.xlsm
	B	C	D
2	ATL	POR	IND
Injuries

Cell Formulas
Range	Formula
B2:D2	B2	=LOOKUP(2, 1/((COUNTIF($B$2:B2,Favs!$A$4:$A$29)=0)*(Favs!$A$4:$A$29<>"")),Favs!$A$4:$A$29)

 

[jasonb75]

If the source of the formula changes then the results will change, that is the basic principle of how they work.

There is not enough information contained in your post to indicate what is being rearranged and what you actually want.

 

[gtd526]

If the source of the formula changes then the results will change, that is the basic principle of how they work.

There is not enough information contained in your post to indicate what is being rearranged and what you actually want.

I don't edit data in the formula (or edit anything). I notice it only switches back and forth between B:C, D:E, F:G, etc. when I hit the save button. Maybe its the Lookup formula I'm using. Its not a major issue at this time. I call it 'preventative maintenance".

NBA.xlsm
	A	B	C	D	E	F	G	H	I
1	Team:	POR	ATL	IND	PHX	PHI	MEM	DET	MIA
2	Injuries:	3	3	5	2	6	5	1	11
3	Who:	Zach Collins	Bogdan Bogdanović	Goga Bitadze	Cameron Payne	Seth Curry	Jaren Jackson	Killian Hayes	Bam Adebayo
4		CJ Elleby	Kris Dunn	Brian Bowen	Jalen Smith	Joel Embiid	Ja Morant		Avery Bradley
5		Jusuf Nurkić	Danilo Gallinari	Jeremy Lamb		Furkan Korkmaz	Jontay Porter		Jimmy Butler
6				Jalen Lecque		Vincent Poirier	Killian Tillie		Goran Dragić
7				T.J. Warren		Mike Scott	Justise Winslow		Maurice Harkless
8						Ben Simmons			Udonis Haslem
9									Tyler Herro
10									Meyers Leonard
11									Kendrick Nunn
12									KZ Okpala
13									Gabe Vincent
14
15
16		ATL	POR	PHX	IND	MEM	PHI	MIA	DET
17		POR	ATL	IND	PHX	PHI	MEM	DET	MIA
Injuries

Cell Formulas
Range	Formula
B1:I1	B1	=LOOKUP(2, 1/((COUNTIF($B$1:B1,Favs!$A$4:$A$29)=0)*(Favs!$A$4:$A$29<>"")),Favs!$A$4:$A$29)
B2:I2	B2	=COUNTIF('Injury List'!$A:$A,B1)
B3:I3	B3	=IFERROR(INDEX('Injury List'!$B$3:$B$200,SMALL(IF(B$1='Injury List'!$A$3:$A$200,ROW('Injury List'!$A$3:$A$200)-ROW('Injury List'!$A$3)+1),1)),"")
B4:I4	B4	=IFERROR(INDEX('Injury List'!$B$3:$B$200,SMALL(IF(B$1='Injury List'!$A$3:$A$200,ROW('Injury List'!$A$3:$A$200)-ROW('Injury List'!$A$3)+1),2)),"")
B5:I5	B5	=IFERROR(INDEX('Injury List'!$B$3:$B$200,SMALL(IF(B$1='Injury List'!$A$3:$A$200,ROW('Injury List'!$A$3:$A$200)-ROW('Injury List'!$A$3)+1),3)),"")
B6:I6	B6	=IFERROR(INDEX('Injury List'!$B$3:$B$200,SMALL(IF(B$1='Injury List'!$A$3:$A$200,ROW('Injury List'!$A$3:$A$200)-ROW('Injury List'!$A$3)+1),4)),"")
B7:I7	B7	=IFERROR(INDEX('Injury List'!$B$3:$B$200,SMALL(IF(B$1='Injury List'!$A$3:$A$200,ROW('Injury List'!$A$3:$A$200)-ROW('Injury List'!$A$3)+1),5)),"")
B8:I8	B8	=IFERROR(INDEX('Injury List'!$B$3:$B$200,SMALL(IF(B$1='Injury List'!$A$3:$A$200,ROW('Injury List'!$A$3:$A$200)-ROW('Injury List'!$A$3)+1),6)),"")
B9:I9	B9	=IFERROR(INDEX('Injury List'!$B$3:$B$200,SMALL(IF(B$1='Injury List'!$A$3:$A$200,ROW('Injury List'!$A$3:$A$200)-ROW('Injury List'!$A$3)+1),7)),"")
B10:I10	B10	=IFERROR(INDEX('Injury List'!$B$3:$B$200,SMALL(IF(B$1='Injury List'!$A$3:$A$200,ROW('Injury List'!$A$3:$A$200)-ROW('Injury List'!$A$3)+1),8)),"")
B11:I11	B11	=IFERROR(INDEX('Injury List'!$B$3:$B$200,SMALL(IF(B$1='Injury List'!$A$3:$A$200,ROW('Injury List'!$A$3:$A$200)-ROW('Injury List'!$A$3)+1),9)),"")
B12:I12	B12	=IFERROR(INDEX('Injury List'!$B$3:$B$200,SMALL(IF(B$1='Injury List'!$A$3:$A$200,ROW('Injury List'!$A$3:$A$200)-ROW('Injury List'!$A$3)+1),10)),"")
B13:I13	B13	=IFERROR(INDEX('Injury List'!$B$3:$B$200,SMALL(IF(B$1='Injury List'!$A$3:$A$200,ROW('Injury List'!$A$3:$A$200)-ROW('Injury List'!$A$3)+1),11)),"")
B14:I14	B14	=IFERROR(INDEX('Injury List'!$B$3:$B$200,SMALL(IF(B$1='Injury List'!$A$3:$A$200,ROW('Injury List'!$A$3:$A$200)-ROW('Injury List'!$A$3)+1),12)),"")
B15:I15	B15	=IFERROR(INDEX('Injury List'!$B$3:$B$200,SMALL(IF(B$1='Injury List'!$A$3:$A$200,ROW('Injury List'!$A$3:$A$200)-ROW('Injury List'!$A$3)+1),13)),"")
B17:I17	B17	=B1
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[jasonb75]

There is nothing in any of those formulas that would change when you save.

If any of the cells that the formulas refer to contain volatile functions such as RAND() TODAY() NOW() RANDBETWEEN() (maybe others) then those would change when you save which would mean that these formulas will then be recalculated to reflect the changes of the volatile functions in the other cells.

Likewise if you have any vba in your sheet that is being triggered when you save, live data from a web page that is being refreshed, or anything similar then that could be making changes which have a knock on effect.

 

[gtd526]

There is nothing in any of those formulas that would change when you save.

If any of the cells that the formulas refer to contain volatile functions such as RAND() TODAY() NOW() RANDBETWEEN() (maybe others) then those would change when you save which would mean that these formulas will then be recalculated to reflect the changes of the volatile functions in the other cells.

Likewise if you have any vba in your sheet that is being triggered when you save, live data from a web page that is being refreshed, or anything similar then that could be making changes which have a knock on effect.

Ya, the data for the Names is being received 'from Web' in excel, on another worksheet. I use index,match to receive the data used in the Lookup formula. Everything rolls downhill, if the data involved in the Lookup formula is via 'from Web', which is being updated automatically, it affects the Lookup formula, which then refreshes.
thanks for the info and reply.

 

[jasonb75]

In that case, the only way to stop the formula updating would be to stop the query updating (if possible).

Alternatively you could try setting calculation to manual, but that will affect all formulas.