Sort Formulas referencing another sheet

[SFCChase]

I have a large document with multiple tabs at the bottom. My primary page (Master Data Flow Sheet) contains cells that have both open cells where data is typed in and embedded formulas that refer to the 2nd tab (Calculations). The Calculations tab refers to information on the Master Data Flow Sheet to do some data manipulation.

The formula's all seem to be doing what they're supposed to, but when I try to sort data on the Master Data Flow Sheet the calculated cells end up getting messed up. The free-text data moves as it should with sorting, but the formula's always end up staying in the same place, but referring to the new cell the original data was typed in (if that makes sense).

TAB 1: MASTER DATA FLOW SHEET

STATUS	TYPE	ID	Last Name	First Name	Middle Initial	Form Name
Active	Academic	1112222	Brown	Bob	B	=CALCULATIONS!A2
Relief	Academic	1221221	Michaels	Mike	M	=CALCULATIONS!A3
Recycle	Academic	12341234	Jones	Joe	J	=CALCULATIONS!A4
Active	Academic	43214321	Williams	Will	W	=CALCULATIONS!A5

The Form Name would go to the CALCULATIONS tab and see

Form Name	ETC	ETC	ETC	ETC	ETC	ETC
=IF(OR('Master Data Flow Sheet'!D2<>"",'Master Data Flow Sheet'!E2<>"",'Master Data Flow Sheet'!F2<>""),'Master Data Flow Sheet'!D2&", "&('Master Data Flow Sheet'!E2)&" "&('Master Data Flow Sheet'!F2),"")
=IF(OR('Master Data Flow Sheet'!D3<>"",'Master Data Flow Sheet'!E3<>"",'Master Data Flow Sheet'!F3<>""),'Master Data Flow Sheet'!D3&", "&('Master Data Flow Sheet'!E3)&" "&('Master Data Flow Sheet'!F3),"")

The ETC columns are other formulas that calculate off other data on Master Data Flow Sheet. If I can solve this first one I'll apply it to the others.

The Form Names that should be resulted are:
Brown, Bob B
Michaels, Mike M
Jones, Joe J
Williams, Will W

When I sort Column A (Master Data Flow Sheet) alphabetically all the input information sorts correctly, however the Form names stay in the same order and the formula reference cells change.

is there any way to get Sorting to work while still referencing my formula work on the CALCULATIONS tab???

 

[MCLIFTO8]

Sorting with the sort button can mess up your formulas, sorting with the filter button shouldn't. Try that.