Excel table converted to range but now does not sort properly

[tcalleia]

I have an excel table with many formulas that use structured references. When I convert to a range (way easier to work in for my purpose) the structured go away but then I have a problem sorting. They still reference the work sheet that I am in but do change to a cell reference like $AD7.

This is the start of the formula when it is a table with structured references: IF([@[ABC]]=0,"-",IF([@XYZ]="XXX","Manual Review",...
This is the start of the formula when I convert to a range: =IF('Sheet1'!$AD7=0,"-",IF('Sheet1'!$F7="XXX","Manual Review",

It looks OK except for including the sheet name even when it is in the same sheet (not a lookup to another sheet).

But... If I sort the data the rows keep the original row value- not the new row. In this example, after my sort the data is now in row 1,514 but it is still referencing row 7, even though there is no $ on the 7 in the formula.
IF('Sheet1'!$AD7=0,"-",IF('Sheet1'!$F7="XXX","Manual Review",

Has anyone else seen this problem? Is there any way to fix this without having to rewrite all of my formulas to be non-structured references?
Thanks in advance for any assistance

 

[GraH]

I hope I understand correctly.
Try not to include the columns holding a formula in the sort.

Book1
	C	D	E	F
2	Something	With a value i want to sort	a formula
3	a	5	a	=IF(D3<10;C3;"blabla")
4	b	10	blabla	=IF(D4<10;C4;"blabla")
5	c	4	c	=IF(D5<10;C5;"blabla")
6	d	12	blabla	=IF(D6<10;C6;"blabla")
7	e	18	blabla	=IF(D7<10;C7;"blabla")
8	f	20	blabla	=IF(D8<10;C8;"blabla")
9	g	1	g	=IF(D9<10;C9;"blabla")
10	h	2	h	=IF(D10<10;C10;"blabla")
11	i	4	i	=IF(D11<10;C11;"blabla")
12	j	14	blabla	=IF(D12<10;C12;"blabla")
Sheet1

Cell Formulas
Range	Formula
E3:E12	E3	=IF(D3<10,C3,"blabla")
F3:F12	F3	=FORMULATEXT(E3)

Formulas stay "in place".

Book1
	C	D	E	F
2	Something	With a value i want to sort	a formula
3	g	1	g	=IF(D3<10;C3;"blabla")
4	h	2	h	=IF(D4<10;C4;"blabla")
5	c	4	c	=IF(D5<10;C5;"blabla")
6	i	4	i	=IF(D6<10;C6;"blabla")
7	a	5	a	=IF(D7<10;C7;"blabla")
8	b	10	blabla	=IF(D8<10;C8;"blabla")
9	d	12	blabla	=IF(D9<10;C9;"blabla")
10	j	14	blabla	=IF(D10<10;C10;"blabla")
11	e	18	blabla	=IF(D11<10;C11;"blabla")
12	f	20	blabla	=IF(D12<10;C12;"blabla")
Sheet1

Cell Formulas
Range	Formula
E3:E12	E3	=IF(D3<10,C3,"blabla")
F3:F12	F3	=FORMULATEXT(E3)

 

[tcalleia]

GraH- Thank you for your time trying to solve my problem
Unfortunately for my situation I have many formula columns all among several non formula columns so this solution will not work for me. I did find my own work around though which seems to maybe the only one that is close to what I need. For my example I do a find and replace on the sheet name and replace it with nothing. This hangs up the report a bit because of the volume it is finding and replacing but it solves the sort problem. Once I get the active sheet name out of the formulas all sort works as expected.
I will mark as solved since we now have 2 round about solutions.

Thanks much Grah!