+ Reply to Thread
Results 1 to 7 of 7

Absolute References and Sorting

  1. #1
    Registered User
    Join Date
    04-23-2019
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    4

    Absolute References and Sorting

    I maintain a spreadsheet which contains formulas summing certain cells etc and linking between workbooks. Data is added to the spreadsheet which is then sorted to the correct place in the list. I want the formulas to carry on adding the same data as it did before the new data was added. The problem I have is that the absolute references do not adjust themselves to take account of new data added this way (as it would if rows were inserted). Example attached.

    I have tried using name ranges, which has the same result, using the workbook reference but nothing seems to work. If there anyway to force the system to keep the references.

    Thanks for your help.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: Absolute References and Sorting

    It looks like you could use SUMIF:
    Please Login or Register  to view this content.
    The ranges should expand to include new data, but will continue to only total any rows with Type 2 in the Name column.
    If someone helped achieve your solution, consider clicking "* Add Reputation" on their post.

    If your question has been answered, mark the thread as [SOLVED] using the Thread Tools menu at the top of the thread.

  3. #3
    Registered User
    Join Date
    04-23-2019
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    4

    Re: Absolute References and Sorting

    Thanks. I (perhaps over) simplied the example, all the names are different so couldn't use sumif, though nice idea!

  4. #4
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: Absolute References and Sorting

    You could do something like this, just change the names to fit what you need:
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    04-23-2019
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    4

    Re: Absolute References and Sorting

    Thanks, the spreadsheet has thousands of entries so not really practical to retrofit the formulas, especially having to 'hard code' the references e.g "Dave" rather than B2356. I was really hoping there was some way of making the absolute references work correctly on sorting but I guess not.

    Thanks for your interest.

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

    Re: Absolute References and Sorting

    I (perhaps over) simplied the example, all the names are different...
    Hello SallyO and Welcome to Excel Forum.
    If all of the names are different then how is the decision made as to which ones to sum in the first place. If there is nothing distinguishing about the names that are summed, is there anything distinguishing about those that are not summed and/or those that are added?
    One thought, as modeled, would be to add a 'Sum' column that identifies which cells were originally summed (*) then use a formula such as: =SUMIFS(M3:M20,N3:N20,"*")
    It may help if you could update the file so that it more closely represents your actual data, without using sensitive information of course.
    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.

  7. #7
    Registered User
    Join Date
    04-23-2019
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    4

    Re: Absolute References and Sorting

    Hi JeteMc

    There is nothing in the names (or any other field) which can be used to automate a sumif. Calculations have to be added manually, and there are different types of calculations. Which is why it is paticulary difficult that sort knocks out abolute references. I attach a more detailed example to give you an idea. I think I am going to come way of inserting new data rather than sorting it in.

    Thanks for your interest.
    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. relative references to absolute references
    By marian.dicicco in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-30-2019, 08:09 PM
  2. How to for converting absolute references to relative references in formulas
    By edspyhill01 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-11-2014, 02:09 AM
  3. Absolute References
    By Robin2 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-04-2008, 12:19 AM
  4. Absolute references aren't absolute?
    By Sir Spike in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-19-2007, 10:21 PM
  5. [SOLVED] RE: Absolute references
    By Johan in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-16-2006, 05:10 PM
  6. RE: Absolute references
    By Tom Ogilvy in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-16-2006, 05:10 PM
  7. [SOLVED] Absolute references
    By Johan in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-16-2006, 05:10 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