without affecting past data If I want to change rate in same column

[Mdolasiya]

Hii,


I have 2 sheet "data" and other one "rate".

Sheet1
Product Kg Rate
A 1 10
B 2 20
C 3 30

Rate formula(=IF(A1="A",LOOKUP(B2,Rate!$A$2:$A$4,Rate!$B$2:$B$4),IF(A1="B",LOOKUP(B2,Rate!$A$2:$A$4,Rate!$B$2:$B$4) ...so on

Sheet2(Rate)

Kg Rate
1 10
2 20
3 30

Is it possible that without affecting past data If I want to change rate in same column in sheet2 and when i enter new data it should appear according to the new rate.

Thank you...

[anand_babu123]

Why dont you use paste the formula as values once it is derived from next Tab ?

[boopathiraja]

Hi, No by regular formula solution,

[etaf]

you could use a date and add the new rate and use that date to apply the correct rate for the period
OR
you could copy and past special - before changing the rate

do you have another sample with how you want to apply to the actual rate - not sure i see it in your current sample

[boopathiraja]

HI, Ref the attachment..

[boopathiraja]

Yes, see you can adopt the method as etaf suggested ... in the above post