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

Mr2017

Active Member
Joined
Nov 28, 2016
Messages
463
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.
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,471
Office Version
365
Platform
Windows
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"))
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,471
Office Version
365
Platform
Windows
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.
 

Mr2017

Active Member
Joined
Nov 28, 2016
Messages
463
Ok, thanks Joe.

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,471
Office Version
365
Platform
Windows
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?
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,046
Office Version
365, 2010
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.
 

Mr2017

Active Member
Joined
Nov 28, 2016
Messages
463
Ok.

B5 in Sheet5 is where the original formula is placed.

Please let me know if you need any more info?
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,471
Office Version
365
Platform
Windows
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:

Mr2017

Active Member
Joined
Nov 28, 2016
Messages
463
Ok, thank you both - that worked, as intended.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,900
Messages
5,471,380
Members
406,759
Latest member
jackflint

This Week's Hot Topics

Top