+ Reply to Thread
Results 1 to 4 of 4

SORTBY not populating with data stored in circular references

  1. #1
    Registered User
    Join Date
    08-07-2023
    Location
    Germany
    MS-Off Ver
    365
    Posts
    3

    SORTBY not populating with data stored in circular references

    I have a schedule with people assigned to operate machines. The user can input a sequence of people using a particular machine and see a new schedule based on those changes.



    The sheet imports A2:C7 as values (the current schedule)



    I2:K2 are circular references: if an input cell is filled & Switch=1 then record the input, otherwise the cell = current value (itself).



    E2:G7 is the new schedule: take changes from I2:K2 if present, otherwise take A2:C7 (original schedule).



    For the user to visualise their changes as they are applied, cell T2 contains a SORTBY function, taking E2:G7 (new schedule) sorted ascending by machine and sequence.



    The tool allows the user to iterate changes to the schedule until a desired sequence of people per machine is reached.



    The problem is that the SORTBY function doesn't update to reflect changes as they are made or returns "0" when schedule changes have been input and the formula is retyped into the cell.



    Can we force SORTBY to update when new data is entered into a cell? Can we force SORTBY to recognize data stored via circular reference? Is there an alternative to SORTBY to get the new schedule data and sort ascending so the user can understand their changes?
    Attached Files Attached Files
    Last edited by ArlenS; 08-08-2023 at 04:54 AM.

  2. #2
    Forum Contributor
    Join Date
    11-20-2007
    Location
    Felixstowe, England
    MS-Off Ver
    Excel 2013, 2019
    Posts
    217

    Re: SORTBY not populating with data stored in circular references

    Instead of attempting to force the SORTBY to work, why can't you redesign your sheet to NOT use circular references?

  3. #3
    Registered User
    Join Date
    08-07-2023
    Location
    Germany
    MS-Off Ver
    365
    Posts
    3

    Re: SORTBY not populating with data stored in circular references

    Can you think of a solution which doesn't include circular references to record changes?

  4. #4
    Registered User
    Join Date
    08-07-2023
    Location
    Germany
    MS-Off Ver
    365
    Posts
    3

    Re: SORTBY not populating with data stored in circular references

    Here is a copy of the solved sheet. If the memory cells have null values SORTBY doesn't seem to operate. The solution appears to be to have a your memory array fully print, including items which haven't changed (I believe this was the issue).
    Attached Files Attached Files

+ 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. [SOLVED] Circular references
    By Osprey2 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-14-2022, 09:49 AM
  2. UDF Help: Circular References
    By grturner in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-25-2016, 04:39 AM
  3. Circular References
    By sazza7 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-27-2015, 01:07 PM
  4. [SOLVED] Populating Data from another spreadsheet without references
    By edanielqsf in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-23-2014, 06:14 PM
  5. Replies: 2
    Last Post: 02-23-2014, 06:06 PM
  6. Non circular references
    By jammerculture in forum Excel General
    Replies: 8
    Last Post: 05-29-2010, 09:43 PM
  7. Circular references
    By R.Hocking in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-02-2006, 02:55 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