Sorting with refs to other sheets

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • I have attached a sheet with a basic example of my prob

    I have multiple sheets with formula references that refer to other sheets.

    My data is constantly growing so I need to add people "teresa green" in example, who owes me £50 according to 'accounts' sheet

    but I want my people in alphabetical order, if you try and sort the names into alphabetical order then it messus up the data and now shows Teresa Green owing £20 which is wrong.

    I really need help finding a solution to this. I cant just insert a row into the sheets because this will mess up my "thick black line every two row" formatting, particularly as I will be adding a lot of people. This formatting is very important to my actual workbook (if not to this one)

    can anyone help?

    I will be very grateful

  • Re: Sorting with refs to other sheets

    Quote from Barrie Davidson

    Is there a reason you can't combine the worksheets and just have one?

    Hi thanks for reply, yes I can't combine the worksheets because I have a LOT of information in each sheet, and not practical to combine

  • Re: Sorting with refs to other sheets

    If you use absolute cell reference (i.e., =Sheet1!$A$1 instead of =Sheet1!A1), you'll be able to sort and not lose the link.

    Barrie Davidson
    My Excel Web Page
    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!