Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Sep 2014
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Talking 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. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    New Lounger
    Join Date
    Sep 2014
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by RetiredGeek View Post
    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
    Thanks for reply

    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. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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. #5
    New Lounger
    Join Date
    Sep 2014
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by sdckapr View Post
    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
  •