Excel - Making absolute cell references 1

[sgreenwood]

I have a friend with a worksheet that has several columns containing formulas. They want to "transpose" the columns to rows, but of course the formulas don't work after doing a paste special-transpose, so they want to create absolute cell references in the formulas.

There are A LOT of cells involved, so they don't want to have to edit each cell and press F4 to create the absolutes. Is there any way to do this to an entire range of cells??

I tried creating a macro that would work, but the macro recorder doesn't pick up the action of pressing F2 then F4, and I don't know how to enter those keystrokes in VBA manually.

Can anyone possibly help with this??

Any help would be greatly appreciated.

Thanks in advance,
Steve

 

[PeterMoran]

Hi Steve,

You need ASAP-Utilities a free Excel addin you can get from

http://www.asap-utilities.com

which will do what you want along with a zillion other things which are not easy to do in Excel. It's great and essential!

Good Luck!

Peter Moran

 

[KenWright]

1) Select the range of data
2) Do Edit / Replace / Replace = with %%
3) Copy selected Range and paste special / transpose where needed
4) Do Edit / Replace / Replace %% with =

Done

Regards
Ken................


----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[sgreenwood]

Ken,

I like your way of thinking! I would have never thought of doing it that way...but it works perfectly!

Thanks so much.

A star for you!!

 

[KenWright]

You're welcome and thank you

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------