+ Reply to Thread
Results 1 to 6 of 6

Ensuring Cell References After Sort

  1. #1
    Registered User
    Join Date
    05-16-2018
    Location
    England
    MS-Off Ver
    Office 365
    Posts
    9

    Ensuring Cell References After Sort

    This may be a very simple solution that's I'm missing, so apologies if it is. I'm trying to create a planner which has several project start dates and several tasks for each project. I want the follow up tasks to read the first date, apply a specified working number of days then spit out a resulting day. The problem I'm having is that when I sort the dates, the follow up tasks do not maintain the original references no matter the order I place dollar's or playing around with INDIRECT which to be honest I don't fully understand.

    To summarise, I'm just looking for a way to sort the dates column by smallest to largest and for a project's Task's 2, 3 & 4 to read from the relative Task 1, regardless of the order.

    I've attached a sheet for clarity

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,830

    Re: Ensuring Cell References After Sort

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    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.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    05-16-2018
    Location
    England
    MS-Off Ver
    Office 365
    Posts
    9

    Re: Ensuring Cell References After Sort

    Apologies, forgot the attachment
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    01-09-2016
    Location
    Perth Western Australia
    MS-Off Ver
    Office 365
    Posts
    257

    Re: Ensuring Cell References After Sort

    Hi

    As row 2 is an absolute date then you can only sort below that row otherwise all formula other than the original Row 2 are destroyed.

    Does that make sense

    Cheers

  5. #5
    Registered User
    Join Date
    05-16-2018
    Location
    England
    MS-Off Ver
    Office 365
    Posts
    9

    Re: Ensuring Cell References After Sort

    There must be some way around this?

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,563

    Re: Ensuring Cell References After Sort

    As opposed to sorting the original table this proposal produces a new, sorted, table.
    Column L is populated using: =IFERROR(AGGREGATE(15,6,D$2:D$13,ROW(1:1)),"")
    The other columns are populated using: =IFERROR(INDEX(A$2:A$13,MATCH($L2,$D$2:$D$13,0)),"")
    Note that this assumes each start date is unique as in the example.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. Using Cell References As PivotItem names to Custom Sort PivotTable
    By brndnr in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-26-2015, 01:49 AM
  2. [SOLVED] VBA to convert cell to text and ensuring 7 digit number all along in entire column.
    By shiva_reshs in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 09-19-2013, 09:35 AM
  3. Replies: 7
    Last Post: 06-12-2013, 06:56 PM
  4. Replies: 0
    Last Post: 06-10-2013, 12:26 PM
  5. Replies: 0
    Last Post: 02-11-2013, 08:12 AM
  6. Ensuring cell is filled with one of many keywords
    By lightsandsirens in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-04-2009, 10:58 PM
  7. [SOLVED] When I sort how do I keep cell references correct?
    By GLS in forum Excel General
    Replies: 3
    Last Post: 12-03-2005, 12:59 PM

Tags for this Thread

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