Leave Value unchanged if false

Paul - Feb 14, 2009 at 02:21 PM
 Paul - Feb 15, 2009 at 11:23 PM
Vlookup posts a value to worksheet1/C3 when the trigger is the date. C4thruC20 are "0". When the vlookup trigger is changed to the next date, the value of C3 changes to" 0" and "C4" displays the value.

The vlookup calculation goes like this:
=IF(A3='Payroll Calculator'!M2,VLOOKUP(H1,'Payroll Calculator'!$B$3:$M$28,8,FALSE),0)
(M2 is the date on the payroll calculator sheet, H1 is the employee # on sheet 1)

The desired result is to add all the values of column "a" but all the values change to 0 except the current vlookup because of the "0" at the end of the string.

How can I preserve and total the values of all the previous calculations of column "C"?

Solution that doesn't work:
Transfer the values of worksheet 1/column "C" to worksheet 2 column "C" using
"=IF(OR('sheet1'!C3>0)=TRUE,'Sheet1'!C3,false=do not changeC3).

Of course, "false=do not changeC3" is not a command, but it should be!! The "do not changeC3" could also be put on the end of the vlookup string if it were a command!

Is it Macro time (not desirable since this requires the user to press a button), or is there a command that I am missing that will leave the previously calculated value alone??

Thanks in advance!!

1 response

You may need to try a nested IF statement instead of IF(OR......

Also, try the formula in reverse order:
Instead of:
=IF(OR('sheet1'!C3>0)=TRUE,'Sheet1'!C3,false=do not changeC3)

Thanks, Helper! This does create a circular reference, of course :(...