+ Reply to Thread
Results 1 to 4 of 4

Long IF Statement changing ref to one cell after sorting

  1. 09-08-2021, 08:04 AM #1
    jenmc84
    jenmc84 is offline
    Registered User
    Join Date
    10-12-2011
    Location
    England
    MS-Off Ver
    Excel 365
    Posts
    36

    Long IF Statement changing ref to one cell after sorting

    I have a long IF formula which is looking at a cell to see a weight bracket. It's using 'if H2 is less than this but greater than that then put this value in the cell'.

    Works absolutely fine until we sort H2 in any way. The second ref in the AND statement changes to H6. i've not used dollars as I want the formula in cell I# to always be looking at the adjacent cell in column H#, in this case it started as H2 but after the sort some of the cells updated to H6 which is wrong, should stay as H2 for that row, then H3 etc after.

    In the attached example if you sort column H smallest to largest you will see the issue.

    PRE-SORT

    =IF(AND(H2>='Postage Matrix'!$A$2,'A - Price File'!H2<='Postage Matrix'!$B$2),'Postage Matrix'!$C$2,IF(AND(H2>='Postage Matrix'!$A$3,'A - Price File'!H2<='Postage Matrix'!$B$3),'Postage Matrix'!$C$3,IF(AND(H2>='Postage Matrix'!$A$4,'A - Price File'!H2<='Postage Matrix'!$B$4),'Postage Matrix'!$C$4,IF(AND(H2>='Postage Matrix'!$A$5,'A - Price File'!H2<='Postage Matrix'!$B$5),'Postage Matrix'!$C$5,IF(AND(H2>='Postage Matrix'!$A$5,'A - Price File'!H2<='Postage Matrix'!$B$5),'Postage Matrix'!$C$5,IF(AND(H2>='Postage Matrix'!$A$6,'A - Price File'!H2<='Postage Matrix'!$B$6),'Postage Matrix'!$C$6,IF(AND(H2>='Postage Matrix'!$A$7,'A - Price File'!H2<='Postage Matrix'!$B$7),'Postage Matrix'!$C$7,IF(AND(H2>='Postage Matrix'!$A$8,'A - Price File'!H2<='Postage Matrix'!$B$8),'Postage Matrix'!$C$8,IF(AND(H2>='Postage Matrix'!$A$9,'A - Price File'!H2<='Postage Matrix'!$B$9),'Postage Matrix'!$C$9,IF(AND(H2>='Postage Matrix'!$A$10,'A - Price File'!H2<='Postage Matrix'!$B$10),'Postage Matrix'!$C$10,IF(AND(H2>='Postage Matrix'!$A$11,'A - Price File'!H2<='Postage Matrix'!$B$11),'Postage Matrix'!$C$11,IF(AND(H2>='Postage Matrix'!$A$12,'A - Price File'!H2<='Postage Matrix'!$B$12),'Postage Matrix'!$C$12,IF(AND(H2>='Postage Matrix'!$A$13,'A - Price File'!H2<='Postage Matrix'!$B$13),'Postage Matrix'!$C$13,IF(AND(H2>='Postage Matrix'!$A$14,'A - Price File'!H2<='Postage Matrix'!$B$14),'Postage Matrix'!$C$14,IF(AND(H2>='Postage Matrix'!$A$15,'A - Price File'!H2<='Postage Matrix'!$B$15),'Postage Matrix'!$C$15,IF(AND(H2>='Postage Matrix'!$A$16,'A - Price File'!H2<='Postage Matrix'!$B$16),'Postage Matrix'!$C$16,"CHECK"))))))))))))))))

    POST-SORT (H2 ref changes to H6)

    =IF(AND(H2>='Postage Matrix'!$A$2,'A - Price File'!H6<='Postage Matrix'!$B$2),'Postage Matrix'!$C$2,IF(AND(H2>='Postage Matrix'!$A$3,'A - Price File'!H6<='Postage Matrix'!$B$3),'Postage Matrix'!$C$3,IF(AND(H2>='Postage Matrix'!$A$4,'A - Price File'!H6<='Postage Matrix'!$B$4),'Postage Matrix'!$C$4,IF(AND(H2>='Postage Matrix'!$A$5,'A - Price File'!H6<='Postage Matrix'!$B$5),'Postage Matrix'!$C$5,IF(AND(H2>='Postage Matrix'!$A$5,'A - Price File'!H6<='Postage Matrix'!$B$5),'Postage Matrix'!$C$5,IF(AND(H2>='Postage Matrix'!$A$6,'A - Price File'!H6<='Postage Matrix'!$B$6),'Postage Matrix'!$C$6,IF(AND(H2>='Postage Matrix'!$A$7,'A - Price File'!H6<='Postage Matrix'!$B$7),'Postage Matrix'!$C$7,IF(AND(H2>='Postage Matrix'!$A$8,'A - Price File'!H6<='Postage Matrix'!$B$8),'Postage Matrix'!$C$8,IF(AND(H2>='Postage Matrix'!$A$9,'A - Price File'!H6<='Postage Matrix'!$B$9),'Postage Matrix'!$C$9,IF(AND(H2>='Postage Matrix'!$A$10,'A - Price File'!H6<='Postage Matrix'!$B$10),'Postage Matrix'!$C$10,IF(AND(H2>='Postage Matrix'!$A$11,'A - Price File'!H6<='Postage Matrix'!$B$11),'Postage Matrix'!$C$11,IF(AND(H2>='Postage Matrix'!$A$12,'A - Price File'!H6<='Postage Matrix'!$B$12),'Postage Matrix'!$C$12,IF(AND(H2>='Postage Matrix'!$A$13,'A - Price File'!H6<='Postage Matrix'!$B$13),'Postage Matrix'!$C$13,IF(AND(H2>='Postage Matrix'!$A$14,'A - Price File'!H6<='Postage Matrix'!$B$14),'Postage Matrix'!$C$14,IF(AND(H2>='Postage Matrix'!$A$15,'A - Price File'!H6<='Postage Matrix'!$B$15),'Postage Matrix'!$C$15,IF(AND(H2>='Postage Matrix'!$A$16,'A - Price File'!H6<='Postage Matrix'!$B$16),'Postage Matrix'!$C$16,"CHECK"))))))))))))))))
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 09-08-2021, 08:13 AM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,647

    Re: Long IF Statement changing ref to one cell after sorting

    Change it to this:

    =IF(AND(H2>='Postage Matrix'!$A$2,H2<='Postage Matrix'!$B$2),'Postage Matrix'!$C$2,IF(AND(H2>='Postage Matrix'!$A$3,H2<='Postage Matrix'!$B$3),'Postage Matrix'!$C$3,IF(AND(H2>='Postage Matrix'!$A$4,H2<='Postage Matrix'!$B$4),'Postage Matrix'!$C$4,IF(AND(H2>='Postage Matrix'!$A$5,H2<='Postage Matrix'!$B$5),'Postage Matrix'!$C$5,IF(AND(H2>='Postage Matrix'!$A$5,H2<='Postage Matrix'!$B$5),'Postage Matrix'!$C$5,IF(AND(H2>='Postage Matrix'!$A$6,H2<='Postage Matrix'!$B$6),'Postage Matrix'!$C$6,IF(AND(H2>='Postage Matrix'!$A$7,H2<='Postage Matrix'!$B$7),'Postage Matrix'!$C$7,IF(AND(H2>='Postage Matrix'!$A$8,H2<='Postage Matrix'!$B$8),'Postage Matrix'!$C$8,IF(AND(H2>='Postage Matrix'!$A$9,H2<='Postage Matrix'!$B$9),'Postage Matrix'!$C$9,IF(AND(H2>='Postage Matrix'!$A$10,H2<='Postage Matrix'!$B$10),'Postage Matrix'!$C$10,IF(AND(H2>='Postage Matrix'!$A$11,H2<='Postage Matrix'!$B$11),'Postage Matrix'!$C$11,IF(AND(H2>='Postage Matrix'!$A$12,H2<='Postage Matrix'!$B$12),'Postage Matrix'!$C$12,IF(AND(H2>='Postage Matrix'!$A$13,H2<='Postage Matrix'!$B$13),'Postage Matrix'!$C$13,IF(AND(H2>='Postage Matrix'!$A$14,H2<='Postage Matrix'!$B$14),'Postage Matrix'!$C$14,IF(AND(H2>='Postage Matrix'!$A$15,H2<='Postage Matrix'!$B$15),'Postage Matrix'!$C$15,IF(AND(H2>='Postage Matrix'!$A$16,H2<='Postage Matrix'!$B$16),'Postage Matrix'!$C$16,"CHECK"))))))))))))))))

    removing any sheet references to the current sheet ('A - Price File'!).

    Are you still using Excel 2003? If not, please update your profile. Thanks.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 09-08-2021, 08:27 AM #3
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,169

    Re: Long IF Statement changing ref to one cell after sorting

    In H2

    =VLOOKUP(H2,'Postage Matrix'!$A$2:$C$17,3,1)

    copy down
    Reply With QuoteRegister To Reply
  4. 09-08-2021, 08:32 AM #4
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,647

    Re: Long IF Statement changing ref to one cell after sorting

    @John

    LOL! Yes, that's by far the best (and most obvious) solution.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Sorting data and changing cell contents
    By The Mathguy in forum Excel General
    Replies: 5
    Last Post: 10-18-2013, 09:44 PM
  2. [SOLVED] Changing cell color from an if statement
    By HHR in forum Excel General
    Replies: 18
    Last Post: 09-27-2012, 04:44 PM
  3. [SOLVED] Changing the colour of a cell within an IF statement
    By wetfish in forum Excel General
    Replies: 10
    Last Post: 08-08-2012, 04:17 PM
  4. Cell Phone Optimization-Long If Statement
    By alias09 in forum Excel General
    Replies: 7
    Last Post: 11-21-2009, 05:07 PM
  5. Changing cell color thru IF statement
    By Valtir in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-03-2009, 12:23 AM
  6. Changing cell formatting with an IF statement
    By jmoffett in forum Excel General
    Replies: 5
    Last Post: 11-18-2008, 02:56 PM
  7. Changing cell color with an IF statement
    By duffer_devon in forum Excel General
    Replies: 3
    Last Post: 04-27-2008, 11:59 PM
  8. Changing Cell colour using IF Statement?
    By robbiebrown34 in forum Excel General
    Replies: 3
    Last Post: 07-17-2007, 07:32 AM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1