Results 1 to 10 of 10

Thread: If formula reference changes when column order is changed, but shouldn't

  1. #1
    Board Regular
    Join Date
    Nov 2016
    Posts
    354
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default If formula reference changes when column order is changed, but shouldn't

    Hi

    I've got an IF statement that

    i) checks if the value of a cell in another sheet is blank - if it is, then it returns nothing

    ii) returns the value of that cell if it is not blank.

    =IF('Tab1'!$S5="","",'Tab1'!$S5)

    However, the formula keeps changing every time a macro, which re-arranges columns in that sheet is run.

    It keeps changing to

    =IF('Tab1'!$R5="","",'Tab1'!$R5)

    Is there a way of amending the IF statement, so it always looks at column S, irrespective of whether the columns have been moved around?

    Can it be done using the indirect function?

    Thanks in advance.

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    49,667
    Post Thanks / Like
    Mentioned
    51 Post(s)
    Tagged
    11 Thread(s)

    Default Re: If formula reference changes when column order is changed, but shouldn't

    You can use the INDIRECT function, because then the range reference is treated like a string, and will not be adjusted by moving columns, i.e.
    Code:
    =IF(INDIRECT("'Tab1'!$S5")="","",INDIRECT("'Tab1'!$S5"))
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    Board Regular
    Join Date
    Nov 2016
    Posts
    354
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If formula reference changes when column order is changed, but shouldn't

    Cool - thanks Joe!

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    49,667
    Post Thanks / Like
    Mentioned
    51 Post(s)
    Tagged
    11 Thread(s)

    Default Re: If formula reference changes when column order is changed, but shouldn't

    You are welcome.

    The INDIRECT function is very useful in building range references on-the-fly (i.e. you don't want to hard-code the range reference, but want it to pull the address from another cell), and also in this instance too, where you don't want it to change, regardless of how cells are moved around.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    Board Regular
    Join Date
    Nov 2016
    Posts
    354
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If formula reference changes when column order is changed, but shouldn't

    Ok, thanks Joe.

    Do you know why the formula doesn't update the row number, when you drag it down?

  6. #6
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    49,667
    Post Thanks / Like
    Mentioned
    51 Post(s)
    Tagged
    11 Thread(s)

    Default Re: If formula reference changes when column order is changed, but shouldn't

    For the exact same reason, we have the whole reference set to be a String. So since Excel sees it as a String and not a Range Reference, it won't increment it.
    If you need it to increase the row as we copy the formula down, there are ways to do that, if we know the relationship between what row number the formula is being placed in compared to what row number the formula is pulling.

    So what cell address, exactly, are you placing the original formula that references cell S5 in?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  7. #7
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    403
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If formula reference changes when column order is changed, but shouldn't

    One option might be:

    Code:
    =IF(INDIRECT("'Tab1'!$S"&ROW())="","",INDIRECT("'Tab1'!$S"&ROW()))
    But, as Joe4 points out, you need to be sure about the row reference.

  8. #8
    Board Regular
    Join Date
    Nov 2016
    Posts
    354
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If formula reference changes when column order is changed, but shouldn't

    Ok.

    B5 in Sheet5 is where the original formula is placed.

    Please let me know if you need any more info?
    Last edited by Mr2017; Jun 25th, 2019 at 10:39 AM.

  9. #9
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    49,667
    Post Thanks / Like
    Mentioned
    51 Post(s)
    Tagged
    11 Thread(s)

    Default Re: If formula reference changes when column order is changed, but shouldn't

    One option might be:

    Code:
    =IF(INDIRECT("'Tab1'!$S"&ROW())="","",INDIRECT("'Tab1'!$S"&ROW()))

    But, as Joe4 points out, you need to be sure about the row reference.
    Yes, that is exactly where I was going with that. That works if the first formula is going on row 5.
    If not, you will need to add or subtract the difference from ROW() to get the correct row reference.


    EDIT: Since you are putting the first formula in row 5, the modification Weaver posted should work, exactly as written.
    Last edited by Joe4; Jun 25th, 2019 at 10:40 AM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  10. #10
    Board Regular
    Join Date
    Nov 2016
    Posts
    354
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If formula reference changes when column order is changed, but shouldn't

    Ok, thank you both - that worked, as intended.
    Last edited by Mr2017; Jun 25th, 2019 at 10:53 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •