Sorting help needed

[NS4Excel]

I need help with the attached file. If you sort worksheet Docs first by col A and then by col D, you'll see the formulas do not reference the proper cells. How do I get the formulas to still work properly when the rows are sorted?

[moinsk21]

Hi,

First of all, i am not sure why you have added somany blank columns into it. secondly why you have added blank rows after header.

I have sorted it, i didnt find any trouble in the formula. might be you are selecting the header for the sorting.

if you want to sort such kind of data which has blank rows after header, then simply select the data and then sort it.

i have sorted the attached sheet, let me know if you need anything else.

Thanks!
Moinuddin Shaikh

If you like my Response:
1. Show appreciation to those who have helped you by clicking below their posts.
2. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.

Appreciate someone towards their career path is better than saying Thanks!
-Moin

[FDibbins]

Which formulas are nor working for you? (and in G, why are some cells formulas and others text?)

[NS4Excel]

The blank columns are because there is data that does not have anything to do with the sorting issue. I deleted the data for the purspose of sending this file.

When sorting the data, only the data is selected - no headers.

The file that was returned shows that the data was sorted and also shows where the formulas are not working.
The formula in Q32 reason as follows: =IF(OR(A32="",M32=""),"",IF(K32=List!$D$3,SUMPRODUCT((Code=Docs!M33)*(List!$H$1:$O$1=Docs!T33)*(List!$H$3:$O$16)),"")).

The formula in the non sorted file for that same cell reads: =IF(OR(A32="",M32=""),"",IF(K32=List!$D$3,SUMPRODUCT((Code=Docs!M32)*(List!$H$1:$O$1=Docs!T32)*(List!$H$3:$O$16)),""))

The formula in the sorted file references M33 and T33 within the sumproduct section. It should have stayed referencing M32 and T32. The easy way to see the sorted file is not working is because this cell is red in the sorted file.

The reason Col G has some cells with a formula and some without is because those without show manually entered text when the formula could not find a match.

I hope that answers all the questions to direct someone to finding a solution for me. Thank you all for looking at this.

[NS4Excel]

Was anyone able to figure out why sorting messes up my formulas? I really could use a solution to this. Thanks.

[AliGW]

Are you selecting just columns A and D when you sort, or the whole table?

[NS4Excel]

I am selecting just the data, no headers, not entire rows or columns, just the data in cells A5-X320 or whichever row the last data entered is.