Relative, absolute & mixed references in many cells

[Guest]

Hi All,
I've read several threads about switching between relative, absolute, and
mixed references across several cells however these solutions seem to result
in formula with all relative or all absolute or all mixed.
I need to change the formula in lots of cells with a mix of types of
reference. e.g. I need to change "$E$4*AD$2" to "$E4*$AD$2" & would prefer
not to have to go though each of the cells with F4!
I would be very grateful for your help.

 

[Mallycat]

I need to change "$E$4*AD$2" to "$E4*$AD$2".

It's not really clear exactly what you want to do. What are the
variations you can have?

Ie $E$4*AD$2 to $E4*$AD$2 is this always the case (that you want to
remove the $ from the first row in the formula and add $ to the second
column in the formula). Or do you want to do different things with
different formulae in different cells?

Matt

 

[Guest]

Hi Matt,
Yes I do want to always "remove the $ from the first row in the formula and
add $ to the second column in the formula". (Basically I needed to use one
version to make it possible to "drag" the formula across a row but I now need
to copy & paste into different rows so I need to change the relative/absolute
references.)
Cheers

 

[VBA Noob]

Hi FTM,

You could try

=SUBSTITUTE(SUBSTITUTE("="&"$E$4*AD$2","$E$","$E"),"AD$","$AD$")

then paste special vaule and copy formula down

VBA Noob

 

[Guest]

Hi VBA Noob,
Thank you but the values I used were just an example, the cells all have
different cell references in them; I want to remove the $ from the first row
in the formula and add $ to the second column in the formula.
Cheers
FTM

 

[Abanana]

Could anyone please help

I would like help with the following

Rows

A1 LCDU to be turned into 7.5

B1 AL = zero

C1 SD = 7.5

D1 LDENP = 12

B2 AL = zero

C2 EENP = 7.5


and when you calculate column

ENP value =1 SD value = 0

Please help

 

[Mallycat]

OK, here is a macro that may do what you want.
http://members.optusnet.com.au/~allington65/Files/changeref.zip

A couple of caveats though.

1. I assumed that you will have a row of formula. The macro will copy
the row above into current selected range, and make the changes you
want. If you are actually trying to copy a column of formulae, it
wont work in its current format. Not too hard to change though.

2. When the formulae are copied into the new row, the relative
reference A4 in your example doesn't change to A5. This may or may not
be a problem - I'm not sure. It is a non-trivial task to change this,
so I thought I would wait and see before coding for it.

Let me know if it works.

Matt

 

[Guest]

Hi Matt,
It worked like a dream. THANK YOU!
I really apreaciate this, it was very kind of you & has saved me countless
hours of frustration.
Thanks again
Fiona

 

[Mallycat]

It worked like a dream.

Great. Did you get the second version of the first? From your quote,
it looks like you may have got the original one. I think the one that
is linked there now changeref2.zip is probably more what you are after
rather than changeref.zip

Matt

 

[Guest]

Hi Matt,
It was the first version that I used & it has done what I needed it to do.
I'm not sure about changeref2 as the link still takes me to the original one.
Am I missing something?
Ta
Fiona

 

[Mallycat]

My link on post #7 has been edited by me to point to changeref2.zip You
can manually change it too if you like. It seems that you can see a
different version than me. I don't know why.

The problem with the first version is that it may muck up the
relativitiy of cell references.

Matt

 

[Guest]

Ah!
I've tried to edit the link to changeref2.zip but when I click I get the
message:
"unable to open.... item you requested could not be found"