Sorting with refs to other sheets

  • 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!