keeping absolute reference during cut and insert 2

[altaratz]

i've got a macro that imports data to some fields, and then rearranges 2 of the columns. Next to the data fields, I'm using a concatenate function to combine 2 of the fields, however, when the macro cuts and inserts the column, it changes the reference cell for the concatenate function.

So - the cell is "concatenate(a1,b1)" - but after I cut column d and re-insert it between columns A and B, the cell becomes "concatenate(a1,c1)".

It even does this if I make the cells absoute references (i.e. concatenate($a$1,$b$1)")

Any simple way to keep the cell reference absolute?

Thanks

Ethan Altaratz

 

[TonyJollans]

Hi altaratz,

If you don't want your addresses changed, you must make them something other than addresses - using INDIRECT is the easiest way ..

[blue][tt]=CONCATENATE(INDIRECT("$A$1"),INDIRECT("$B$1"))[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[dcompto]

Thanks, Tony. Another answer for which I've been searching. Have a star!

 

[altaratz]

thanks so much - i've never heard of that one!