Results 1 to 16 of 16

Keep the references after sorting

Threaded View

  1. #1
    Registered User
    Join Date
    07-16-2014
    Location
    UK
    MS-Off Ver
    2016
    Posts
    90

    Lightbulb Keep the references after sorting

    Take a look at the spreadsheet.

    The references will change after sorting and break the calculations.
    Absolute referencing is not an option. The table has to be copied periodically to another sheet and placed it at a different row (the columns are identical). Absolute referencing will break the calculations after copying.

    I would like to keep the references unchanged. One idea is to use formulas to maintain references.
    For example, S75 contains:
    =T70
    Change it into:
    =(the address of the Count data cell)

    What formulas could I use to achieve this?

    If no formula could do, I need to resort to macro. Use absolute referencing ($T$70) first. Then run a macro which change all selected cells into relative referencing (T70) every time it needs to be copied. Only the reference of T70 needs to be changed, not any others. I don't know how to code. Could anyone help? Thanks a lot.

    Other links:
    https://www.mrexcel.com/forum/excel-...r-sorting.html
    Attached Files Attached Files
    Last edited by mastertonn; 08-27-2018 at 04:45 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. References goes wrong after sorting
    By sfs in forum Excel General
    Replies: 17
    Last Post: 10-02-2018, 04:36 PM
  2. [SOLVED] First Thread - Sorting and cell references
    By TomW23 in forum Excel General
    Replies: 9
    Last Post: 01-31-2018, 05:57 PM
  3. Sorting with Cell References
    By tangerinezebra in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-04-2016, 11:40 AM
  4. Dynamic Sorting - error with references
    By Grimace in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-18-2011, 10:30 PM
  5. Sorting Changes Cell References
    By Camp2 in forum Excel General
    Replies: 2
    Last Post: 06-20-2008, 03:41 PM
  6. Sorting of References for Formulas
    By fak119 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-27-2006, 12:40 PM
  7. Sorting lists with references
    By jonfu in forum Excel General
    Replies: 1
    Last Post: 10-18-2005, 06:05 PM

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