+ Reply to Thread
Results 1 to 6 of 6

Sort by column in one sheet and maintain row formula references in another sheet

  1. 09-24-2021, 10:25 AM #1
    ChronicMixTapes
    ChronicMixTapes is offline
    Registered User
    Join Date
    09-24-2021
    Location
    USA
    MS-Off Ver
    MS365
    Posts
    2

    Exclamation Sort by column in one sheet and maintain row formula references in another sheet

    Hi, everyone!

    I have a workbook with multiple sheets (13 in total, every month plus a YTD). The YTD sheet compiles metrics for multiple associates and references rows from the preceding 12 sheets.

    My goal is to be able to go back to any month and sort any given column without changing the values that are displayed in the YTD sheet. If every month is sorted alphabetically by associate name then the formulas in the YTD sheet are correct as this is how the YTD sheet is also sorted. If you go back and sort by a column in any month, the values change on the YTD sheet since the cell references change (thereby producing incorrect data on the YTD sheet).

    Please see the attached sample (condensed) workbook for reference and thanks in advance for any help here.

    -CMT
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 09-24-2021, 10:44 AM #2
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,943

    Re: Sort by column in one sheet and maintain row formula references in another sheet

    You probably need to use an INDEX/MATCH/MATCH structure like this:

    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    You can copy this across and down. Adjust for the Time average column (see the example). And the headings must match (Avg. Work Volume/Hour)
    Attached Files Attached Files
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  3. 09-24-2021, 10:45 AM #3
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2507 (Windows 11 Home 24H2 64-bit)
    Posts
    91,792

    Re: Sort by column in one sheet and maintain row formula references in another sheet

    You need to use lookups to create the summary sheet in order for the rows not to get muddled.

    Look at a combination of the following functions:

    UNIQUE
    VLOOKUP/HLOOKUP/XLOOKUP
    INDEX MATCH MATCH
    SUMIF(S)
    SUMPRODUCT

    Shout if you need further guidance.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    NB:     as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  4. 09-24-2021, 11:06 AM #4
    ChronicMixTapes
    ChronicMixTapes is offline
    Registered User
    Join Date
    09-24-2021
    Location
    USA
    MS-Off Ver
    MS365
    Posts
    2

    Re: Sort by column in one sheet and maintain row formula references in another sheet

    Thank you both so much.
    Reply With QuoteRegister To Reply
  5. 09-24-2021, 11:07 AM #5
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2507 (Windows 11 Home 24H2 64-bit)
    Posts
    91,792

    Re: Sort by column in one sheet and maintain row formula references in another sheet

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

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
    Reply With QuoteRegister To Reply
  6. 09-24-2021, 11:08 AM #6
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,943

    Re: Sort by column in one sheet and maintain row formula references in another sheet

    You're welcome. Thanks for the rep.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] maintain cell references from Sheet1 in Sheets2-6, sort by different columns in Sheets2-6
    By Nardar in forum Excel - New Users/Basics
    Replies: 6
    Last Post: 05-22-2021, 12:23 PM
  2. [SOLVED] Carry information from Sheet 1 to Sheet 2 maintain data integrity when adding rows
    By rhett7660 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-04-2016, 02:53 PM
  3. Maintain Formula References when replacing tabs
    By maddogp in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-19-2015, 03:17 PM
  4. Sort By Column then Copy into new Sheet and name that sheet with the value
    By fdiddy in forum Excel General
    Replies: 1
    Last Post: 07-06-2014, 10:25 PM
  5. Maintain data on one sheet and sort on another
    By fiat124cc in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-17-2013, 07:05 PM
  6. Maintain formula if sheet removed
    By rybussell in forum Excel General
    Replies: 1
    Last Post: 02-27-2013, 12:47 PM
  7. Drag Formula but Maintain References
    By kaiouji in forum Excel General
    Replies: 2
    Last Post: 01-02-2011, 06:19 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1