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

[Mr2017]

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.

 

[Joe4]

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.

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

 

[Mr2017]

Cool - thanks Joe!

 

[Joe4]

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]

Ok, thanks Joe.

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

 

[Joe4]

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]

One option might be:

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

But, as Joe4 points out, you need to be sure about the row reference.

 

[Mr2017]

Ok.

B5 in Sheet5 is where the original formula is placed.

Please let me know if you need any more info?

 

[Joe4]

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.

 

[Mr2017]

Ok, thank you both - that worked, as intended.