# Thread: Excel incorrect result after cross-sheet sorting

1. ## Excel incorrect result after cross-sheet sorting

apple orange peach
banana orange apple
peach peach banana
apple apple tomato
banana banana tomato
peach tomato peach

above data are data from my reference sheet A1 to C6, named Sheet1.

column A column B
tomato =COUNTIF('Sheet1'!A:C,Sheet2!A1)
peach =COUNTIF('Sheet1'!A:C,Sheet2!A2)
apple =COUNTIF('Sheet1'!A:C,Sheet2!A3)
banana =COUNTIF('Sheet1 !A:C,Sheet2!A4)
orange =COUNTIF('Sheet1'!A:C,Sheet2!A5)

and this is from Sheet2 which just simply get data from Sheet1 and count how many from each of them. But if I want to sort in this sheet, such as I selected from A1 to B5, and sort this 2-D array with A1 by ascending, the formula also got sorted like this

column A column B
apple =COUNTIF('Sheet1'!A:C,Sheet2!A3)
banana =COUNTIF('Sheet1'!A:C,Sheet2!A5)
orange =COUNTIF('Sheet1'!A:C,Sheet2!A1)
peach =COUNTIF('Sheet1'!A:C,Sheet2!A2)
tomato =COUNTIF('Sheet1'!A:C,Sheet2!A4)

that is incorrect, because the formula should not sort with the column A. after sorted, the B1 which is "=COUNTIF('Sheet1'!A:C,Sheet2!A3)" suppose to remain as =COUNTIF('Sheet1'!A:C,Sheet2!A1).

Any ideas? thanks

2. Stanley,

Welcome to the Lounge as a new poster!

Actually, Excel is doing exactly what it is supposed to do.

To fix your problem just sort col A values, the formulas won't change but they will reference the new produce.

HTH

3. Originally Posted by RetiredGeek
Stanley,

Welcome to the Lounge as a new poster!

Actually, Excel is doing exactly what it is supposed to do.

To fix your problem just sort col A values, the formulas won't change but they will reference the new produce.

HTH

but if I write =A1 in column C, after sorted it, C1 still remain =A1, it didn't changed to A3 though. I tried this and find out the formula will also been sorted with contents only happened if you try to reference the data from other sheet. any ideas?

apple =COUNTIF('Sheet1'!A:C,Sheet2!A3) =A1
banana =COUNTIF('Sheet1'!A:C,Sheet2!A5) =A2
orange =COUNTIF('Sheet1'!A:C,Sheet2!A1) =A3
peach =COUNTIF('Sheet1'!A:C,Sheet2!A2) =A4
tomato =COUNTIF('Sheet1'!A:C,Sheet2!A4) =A5

4. Change the formula in B1 to something like:
=COUNTIF(Sheet1!A:C,A1)

And it will sort the way you expect it to sort. By explicitly adding a sheetname (even if it is sheetname on the sheet), the copy will do more of "move" than a "copy" of the formula. With no sheetname and implicitly on the current sheet, it works as you expect it.

Steve

5. ## The Following User Says Thank You to sdckapr For This Useful Post:

stanleywurld (2014-09-13)

6. Originally Posted by sdckapr
Change the formula in B1 to something like:
=COUNTIF(Sheet1!A:C,A1)

And it will sort the way you expect it to sort. By explicitly adding a sheetname (even if it is sheetname on the sheet), the copy will do more of "move" than a "copy" of the formula. With no sheetname and implicitly on the current sheet, it works as you expect it.

Steve
Oh~ thanks!! it works, thank you very much

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•