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

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,457
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,457
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,457
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,042
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,457
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,579
Messages
5,469,504
Members
406,656
Latest member
Kriscrawford76

This Week's Hot Topics

Top