Formula to Static Value

Dear All,

I have a cell (quantity) where I keep on changing the values so that this will be mapped to different sheets

Here my requirement is,

formula shouldnot convert to value (as I use this formula daily), but value should be static after re-writing the value in the quantity (as this shouldnt be changed once it is mapped in different sheets)

Ex: Please refer the attached excel file 2 Sheets.. When I enter quantity in Sheet1, it will mapped to certain cell under receipts column in sheet2 as per the below formula

=IFERROR(IF(AND(HLOOKUP(A$1,Sheet1!$E$1,1,0)=Sheet1!$E$1,HLOOKUP($A3,Sheet1!$E$3,1,0)=Sheet1!$E$3),Sheet1!$E$5,0),0)

But here challenge I'm facing is, When I enter quantity for present receipt, it is displaying the value but previous entered value is becoming zero which I should restrict not to happen

Please do the needful and if not understood my requirement please let me know

This one add the value, when you enter the field quantity
if that is what your after



Kind regards
Leo

So is done in this one, just take care that sheet names have the same spelling
as the validation in E4 of sheets Receipts UF.

Kind regards
Leo

Thanks Buddy!!!!! Really helped me to the peaks

Anyway, I've marked this as solved. Just for your info, When Model is also a number, debug is being raised when I'm entering the quantity. So wherever model is also a number, I filled those models as items name - model name so that it is converted into a string.

Hi LeoTaxi,

Its my pleasure meeting you after long time. I'm using your logic and implementing it successfully everyday, but its done upto November.

I don't know the reason but now in December, whatever entered quantity, it is showing as it is being added in the corresponding cell but the value is not showing in the cell.

Request you to please check and do the needful.

Hi Laansesu

last file i posted had no formulas in columns receipts


Kind regards
Leo

Originally Posted by LeoTaxi

Hi Laansesu

last file i posted had no formulas in columns receipts


Kind regards
Leo

Sorry..its my mistake..but even tried after removing those formulae

Is this error because of formulae?

Please help me

But i dont have error
as far as i can see the formulas give everywhere 0 as result
so it is normal first time you add a quantity it takes this quantity and remove the formula
second time it will give the new total.


Kind regards
Leo

Error is Type Mismatch. Why is it so??

Error only for 1 ste of december cause this

sheets production cell AH1 contains

="01-"&TEXT(TODAY(),"mmm")&"-"&TEXT(TODAY(),"YYYY")

change to

"=VALUE(01-"&TEXT(TODAY(),"mmm")&"-"&TEXT(TODAY(),"YYYY"))

Kind regards
Leo

You are genius buddy!!!!! Its perfectly worked!!!