• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sorting messes up relative address in formula

A

AlasdairM

New Member
Hello.


I've got this formula in Row 2:


=SUMIF(Ledger!$E$4:$E$1333,'New Main'!A2,Ledger!$AP$4:$AP$1333)


I always build up and check my row formulae first, then simply copy them down to the last row on the sheet. At some point I sorted the spreadsheet, and somehow although many rows had the correct formula in then, some had an incorrect value in the central variable (i.e. the 'New Main'!A2 bit above).


This should of course change to reflect the row the formula is on - so on row 865, it should read 'New Main'!A865.... and so on.


So my question is why, after sorting do some rows show the correct value, and some not?


This has happened a few times to me and I can't understand it!
 
Hi AlasdairM,


Welcome to the Forums! So far as i have tried, there is nothing wrong going with the formula and it is giving correct result even after sorting data. It will be helpful if you upload a sample file.


Please read three green sticky posts on forums home page, that will help you alot!


Regards,

Faseeh
 
Since the formula is looking at another sheet, it will change location based on the where the cell in New Main moves to, not necessarily where the cell with the formula gets moved to. Also, if New Main is the name of the sheet where the formula is at, check this out:

http://spreadsheetpage.com/index.php/oddity/sorting_oddity_bug/


The issue described there might have something to do with this as well.
 
Hi. Thanks for the replies.


The formula is on the 'New Main' sheet.


That link looks like it, Luke. I created the formula using the mouse (so clicked over onto the 'Ledger' file for the first variable, then back to the 'New Main' sheet to pick up A2. I wonder if that's what caused it.... I'll avoid using a sheet ref on the sheet to which it refers in future and keep an eye on it!
 
The odd thing is that I usually remove the sheet name (if the formula is on the same sheet) simply to reduce the No. of characters in the formula!
 
It is certainly an oddity. =P

Hope the issue gets resolved...I can understand how frustrating it is when you build something correctly, and then XL goes and messes it up!
 
You must log in or register to reply here.
Back
Top