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

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
644
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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"))
 
Upvote 0
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.
 
Upvote 0
Ok, thanks Joe.

Do you know why the formula doesn't update the row number, when you drag it down?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
Ok.

B5 in Sheet5 is where the original formula is placed.

Please let me know if you need any more info?
 
Last edited:
Upvote 0
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:
Upvote 0
Ok, thank you both - that worked, as intended.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,237
Messages
6,170,930
Members
452,367
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top