Replication of Excel formulas [automatic cell update feature]

The automatic update of cell references while auto-filling an Excel
formula is undoubtedly an excellent feature, that I have used many
times over. However, I now have a need to disable it. Can someone
suggest a good way to do that?

Let me explain with an example:

Here's my formula for row-2:
=IF(C2="INR", VLOOKUP(A2,'Forex History'!A2:'Forex History'!B14, 2,
TRUE)*D2, VLOOKUP(A2,'Forex History'!A15:'Forex History'!B21, 2,
TRUE)*D2)

For row-3, I would like the formula to be:
=IF(C3="INR", VLOOKUP(A3,'Forex History'!A2:'Forex History'!B14, 2,
TRUE)*D3, VLOOKUP(A3,'Forex History'!A15:'Forex History'!B21, 2,
TRUE)*D3)

Note that while I am updating C2 to C3, A2 to A3 and D2 to D3, I am **
NOT ** updating 'Forex History'!A2:'Forex History'!B14 to 'Forex
History'!A3:'Forex History'!B15.

I am also ** NOT ** updating 'Forex History'!A15:'Forex History'!B21
to 'Forex History'!A16:'Forex History'!B22


I am sure there must be some way to selective disable the automatic
cell adjustment feature when specifying the formula (something akin to
using an escape sequence in the Unix world).

Thanks in advance,
Bhat

The differences that you are talking about are called Relative and Absolute
referencing. If you write C2 then the C as well as the 2 will update as the
formula is copied from row to row and from column to column. If you don't
want the C to change, write $C2. Then only the 2 will change when a row is
changed. You can write C$2 and only the C will update. If you write $C$2,
then neither one will update. HTH Otto
"Generic Usenet Account" <usenet@sta.samsung.com> wrote in message
news:1142988911.592794.118640@t31g2000cwb.googlegroups.com...
> The automatic update of cell references while auto-filling an Excel
> formula is undoubtedly an excellent feature, that I have used many
> times over. However, I now have a need to disable it. Can someone
> suggest a good way to do that?
>
> Let me explain with an example:
>
> Here's my formula for row-2:
> =IF(C2="INR", VLOOKUP(A2,'Forex History'!A2:'Forex History'!B14, 2,
> TRUE)*D2, VLOOKUP(A2,'Forex History'!A15:'Forex History'!B21, 2,
> TRUE)*D2)
>
> For row-3, I would like the formula to be:
> =IF(C3="INR", VLOOKUP(A3,'Forex History'!A2:'Forex History'!B14, 2,
> TRUE)*D3, VLOOKUP(A3,'Forex History'!A15:'Forex History'!B21, 2,
> TRUE)*D3)
>
> Note that while I am updating C2 to C3, A2 to A3 and D2 to D3, I am **
> NOT ** updating 'Forex History'!A2:'Forex History'!B14 to 'Forex
> History'!A3:'Forex History'!B15.
>
> I am also ** NOT ** updating 'Forex History'!A15:'Forex History'!B21
> to 'Forex History'!A16:'Forex History'!B22
>
>
> I am sure there must be some way to selective disable the automatic
> cell adjustment feature when specifying the formula (something akin to
> using an escape sequence in the Unix world).
>
> Thanks in advance,
> Bhat
>

Try this:

=IF(C2="INR", VLOOKUP(A2,'Forex History'!$A$2:$B$14, 2,TRUE)*D2,
VLOOKUP(A2,'Forex History'!$A$15:$B$21, 2,TRUE)*D2)

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Generic Usenet Account" <usenet@sta.samsung.com> wrote in message
news:1142988911.592794.118640@t31g2000cwb.googlegroups.com...
> The automatic update of cell references while auto-filling an Excel
> formula is undoubtedly an excellent feature, that I have used many
> times over. However, I now have a need to disable it. Can someone
> suggest a good way to do that?
>
> Let me explain with an example:
>
> Here's my formula for row-2:
> =IF(C2="INR", VLOOKUP(A2,'Forex History'!A2:'Forex History'!B14, 2,
> TRUE)*D2, VLOOKUP(A2,'Forex History'!A15:'Forex History'!B21, 2,
> TRUE)*D2)
>
> For row-3, I would like the formula to be:
> =IF(C3="INR", VLOOKUP(A3,'Forex History'!A2:'Forex History'!B14, 2,
> TRUE)*D3, VLOOKUP(A3,'Forex History'!A15:'Forex History'!B21, 2,
> TRUE)*D3)
>
> Note that while I am updating C2 to C3, A2 to A3 and D2 to D3, I am **
> NOT ** updating 'Forex History'!A2:'Forex History'!B14 to 'Forex
> History'!A3:'Forex History'!B15.
>
> I am also ** NOT ** updating 'Forex History'!A15:'Forex History'!B21
> to 'Forex History'!A16:'Forex History'!B22
>
>
> I am sure there must be some way to selective disable the automatic
> cell adjustment feature when specifying the formula (something akin to
> using an escape sequence in the Unix world).
>
> Thanks in advance,
> Bhat
>

this is from my excel help

Stop automatic corrections
1.. On the Tools menu, click AutoCorrect Options.
2.. To prevent all automatic corrections, on the AutoCorrect tab, clear
the Replace text as you type check box.
To prevent specific corrections, clear the corresponding check box for the
option.




"Generic Usenet Account" <usenet@sta.samsung.com> wrote in message
news:1142988911.592794.118640@t31g2000cwb.googlegroups.com...
> The automatic update of cell references while auto-filling an Excel
> formula is undoubtedly an excellent feature, that I have used many
> times over. However, I now have a need to disable it. Can someone
> suggest a good way to do that?
>
> Let me explain with an example:
>
> Here's my formula for row-2:
> =IF(C2="INR", VLOOKUP(A2,'Forex History'!A2:'Forex History'!B14, 2,
> TRUE)*D2, VLOOKUP(A2,'Forex History'!A15:'Forex History'!B21, 2,
> TRUE)*D2)
>
> For row-3, I would like the formula to be:
> =IF(C3="INR", VLOOKUP(A3,'Forex History'!A2:'Forex History'!B14, 2,
> TRUE)*D3, VLOOKUP(A3,'Forex History'!A15:'Forex History'!B21, 2,
> TRUE)*D3)
>
> Note that while I am updating C2 to C3, A2 to A3 and D2 to D3, I am **
> NOT ** updating 'Forex History'!A2:'Forex History'!B14 to 'Forex
> History'!A3:'Forex History'!B15.
>
> I am also ** NOT ** updating 'Forex History'!A15:'Forex History'!B21
> to 'Forex History'!A16:'Forex History'!B22
>
>
> I am sure there must be some way to selective disable the automatic
> cell adjustment feature when specifying the formula (something akin to
> using an escape sequence in the Unix world).
>
> Thanks in advance,
> Bhat
>

this is from my excel help

Stop automatic corrections
1.. On the Tools menu, click AutoCorrect Options.
2.. To prevent all automatic corrections, on the AutoCorrect tab, clear
the Replace text as you type check box.
To prevent specific corrections, clear the corresponding check box for the
option.




"Generic Usenet Account" <usenet@sta.samsung.com> wrote in message
news:1142988911.592794.118640@t31g2000cwb.googlegroups.com...
> The automatic update of cell references while auto-filling an Excel
> formula is undoubtedly an excellent feature, that I have used many
> times over. However, I now have a need to disable it. Can someone
> suggest a good way to do that?
>
> Let me explain with an example:
>
> Here's my formula for row-2:
> =IF(C2="INR", VLOOKUP(A2,'Forex History'!A2:'Forex History'!B14, 2,
> TRUE)*D2, VLOOKUP(A2,'Forex History'!A15:'Forex History'!B21, 2,
> TRUE)*D2)
>
> For row-3, I would like the formula to be:
> =IF(C3="INR", VLOOKUP(A3,'Forex History'!A2:'Forex History'!B14, 2,
> TRUE)*D3, VLOOKUP(A3,'Forex History'!A15:'Forex History'!B21, 2,
> TRUE)*D3)
>
> Note that while I am updating C2 to C3, A2 to A3 and D2 to D3, I am **
> NOT ** updating 'Forex History'!A2:'Forex History'!B14 to 'Forex
> History'!A3:'Forex History'!B15.
>
> I am also ** NOT ** updating 'Forex History'!A15:'Forex History'!B21
> to 'Forex History'!A16:'Forex History'!B22
>
>
> I am sure there must be some way to selective disable the automatic
> cell adjustment feature when specifying the formula (something akin to
> using an escape sequence in the Unix world).
>
> Thanks in advance,
> Bhat
>