+ Reply to Thread
Results 1 to 16 of 16

Formula doesn't move with rows when using sort in a table

  1. #1
    Registered User
    Join Date
    04-25-2011
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    50

    Formula doesn't move with rows when using sort in a table

    Hi everyone,

    I have an excel issue with sorting data (with dynamic formulas) that's in a table. I've included a Google Drive link to download the file as the attachment feature here isn't showing up for me ....

    https://drive.google.com/open?id=1mn...BLGmlCArhOYtHz

    Background info:
    - Spreadsheet contains 3 sheets: Sheet A, B, and C
    - Sheet A grabs data from sheet B that matches the names in columns B and C
    - This is done using index and match (I used array formula CTR+SHIFT Enter)
    - The data is inputted into column D

    Problem:
    The problem occurs when I turn this into a table and sort by spend for example. The index match formula in column D doesn't move with sort and stays in the fixed rows. Please reference Sheet C for an illustration of the problem.
    You'll see that the spend for Google / AAG_S_Reverse_Mortgage_SKAG_Tier_3 is $336 instead of $525.

    >> How do I make it so that the match portion of the formula moves as the table is sorted? I only locked the columns in place and not the rows, so I don't understand why it isn't moving as the table is sorted...

    Looking for help from the Excel ninjas!
    Last edited by boba7523; 02-03-2020 at 04:33 PM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    55,000

    Re: Formula doesn't move with rows when using sort in a table

    Please attach the workbook.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    04-25-2011
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    50

    Re: Formula doesn't move with rows when using sort in a table

    Quote Originally Posted by AliGW View Post
    Please attach the workbook.
    Here you go, couldn't attach it https://drive.google.com/open?id=1mn...BLGmlCArhOYtHz

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    55,000

    Re: Formula doesn't move with rows when using sort in a table

    Is this a Google Sheets query?

  5. #5
    Registered User
    Join Date
    04-25-2011
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    50

    Re: Formula doesn't move with rows when using sort in a table

    Quote Originally Posted by AliGW View Post
    Is this a Google Sheets query?
    Hi Ali,

    This is a Excel 2016 file. I uploaded it to my Google Drive because the attachment button did not show a file button after I clicked on it for some reason, so I can't upload it here.

    Once you click on the Google Drive link, you'll be able to download my spreadsheet.

    Thanks!

  6. #6
    Valued Forum Contributor dosydos's Avatar
    Join Date
    12-09-2015
    Location
    Agawam, MA
    MS-Off Ver
    Microsoft Office 365
    Posts
    843

    Re: Formula doesn't move with rows when using sort in a table

    without looking at your file as i cannot open it, my guess is you have the sheet name included in the formula when referencing a cell that is on the current sheet. So lets say this table is on Sheet22. Inside this formula are there any cell references that say something like Sheet22A1? If so remove Sheet22 so it is just A1 and it should sort correctly.
    <----- If you are happy with your solution please click on the "* Add Reputation" as a way to say thank you.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    55,000

    Re: Formula doesn't move with rows when using sort in a table

    Please follow the instructions in the yellow banner at the top of the page to attach the workbook directly here.

  8. #8
    Registered User
    Join Date
    04-25-2011
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    50

    Re: Formula doesn't move with rows when using sort in a table

    Quote Originally Posted by AliGW View Post
    Please follow the instructions in the yellow banner at the top of the page to attach the workbook directly here.
    Hi Ali,

    This is what I see when I try to attach, hence why I uploaded it to my Google Drive instead.Attachment 661242

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    55,000

    Re: Formula doesn't move with rows when using sort in a table

    Your image is invalid, but if it shows that the paper clip icon doesn’t work, we know. However, the instructions at the top of the page do work.

  10. #10
    Registered User
    Join Date
    04-25-2011
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    50

    Re: Formula doesn't move with rows when using sort in a table

    Hi, attached the file. Thank you.
    Attached Files Attached Files

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    55,000

    Re: Formula doesn't move with rows when using sort in a table

    The INDEX range was incorrect. Change it to this:

    =INDEX('Sheet B'!$D$2:$D$27,MATCH('Sheet A'!$B5&'Sheet A'!$C5,'Sheet B'!$B$2:$B$27&'Sheet B'!$C$2:$C$27,0))

  12. #12
    Registered User
    Join Date
    04-25-2011
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    50

    Re: Formula doesn't move with rows when using sort in a table

    Thank you. I changed it to the formula you recommended.

    When I sort, the output is still wrong because the lookup value is not moving with the sort. I've attached a screenshot.

    sample1.jpg

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    55,000

    Re: Formula doesn't move with rows when using sort in a table

    Use the field designations:

    =INDEX('Sheet B'!$D$2:$D$27,MATCH([@Publisher]&[@Campaign],'Sheet B'!$B$2:$B$27&'Sheet B'!$C$2:$C$27,0))

    Excel 2016 (Windows) 32 bit
    B
    C
    D
    1
    Publisher Campaign
    Spend
    2
    Misc RC
    $211
    3
    Misc CA
    $258
    4
    Google AAG_S_Reverse_Mortgage_SKAG_Tier_3
    $525
    5
    Google AAG - S - HECM
    $336
    Sheet: Sheet A

  14. #14
    Registered User
    Join Date
    04-25-2011
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    50

    Re: Formula doesn't move with rows when using sort in a table

    Thanks, that solved it!

    What's the difference between [@Publisher] and simply [Publisher]? I saw that the former only highlights the cell on the same row while the latter highlights the entire column.
    Last edited by AliGW; 02-23-2020 at 02:46 AM. Reason: Please don't quote unnecessarily!

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    55,000

    Re: Formula doesn't move with rows when using sort in a table

    You have identified the difference yourself.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  16. #16
    Registered User
    Join Date
    04-25-2011
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    50

    Re: Formula doesn't move with rows when using sort in a table

    Thank you!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 0
    Last Post: 09-19-2017, 02:27 PM
  2. [SOLVED] Macros To Move Multiple Rows To Another Sheet And Macro To Move Single Rows To DAX Table
    By jcaynes in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-22-2013, 05:08 PM
  3. [SOLVED] Excel sort alphabetically doesn't move cell border
    By Marco-Kun in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 04-25-2013, 11:13 AM
  4. Excel sort alphabetically doesn't move cell border
    By Marco-Kun in forum Excel General
    Replies: 0
    Last Post: 04-18-2013, 08:21 AM
  5. Web Query - new rows formatting doesn't move
    By chk57 in forum Excel General
    Replies: 1
    Last Post: 03-31-2009, 12:06 PM
  6. Problems Programming a sort to move rows
    By Jordan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-22-2006, 08:50 AM
  7. [SOLVED] sort rows in alphabetical order and move the corresponding data
    By lianeanddave in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 11:05 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1