Relative, absolute & mixed references in many cells

[FTM]

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

[FTM]

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

"Mallycat" wrote:

>
> FTM Wrote:
> > 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
>
>
> --
> Mallycat
> ------------------------------------------------------------------------
> Mallycat's Profile: http://www.excelforum.com/member.php...o&userid=35514
> View this thread: http://www.excelforum.com/showthread...hreadid=552968
>
>

[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

[FTM]

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

"VBA Noob" wrote:

>
> 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
>
>
> --
> VBA Noob
> ------------------------------------------------------------------------
> VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
> View this thread: http://www.excelforum.com/showthread...hreadid=552968
>
>

[Mallycat]

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

EDIT: I just realised that you probably want to change the formulae in their original location. I have changed the macro to do this. Let me know if it works as you had hoped.

Matt

[FTM]

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" wrote:

>
> OK, here is a macro that may do what you want.
> http://members.optusnet.com.au/~alli.../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
>
>
> --
> Mallycat
> ------------------------------------------------------------------------
> Mallycat's Profile: http://www.excelforum.com/member.php...o&userid=35514
> View this thread: http://www.excelforum.com/showthread...hreadid=552968
>
>

[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

[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