A Formula For This?

[Skovoska]

Hey guys,

Not too good with excel, but I have an odd question about formulas. I will try to explain this the best I can.

I was wondering if there is a formula for a situation like this: Imagine an inventory report: if Column C is correct, Column D has a checkbox in it, Column E would reflect Column C. If the inventory in Column C is correct, you check the box in Column D and then it would reflect Column C's value in Column E. If it isn't correct, in Column E, you'd type in the value that was counted in inventory and Column F would have the difference between Column E and Column C to show that it was incorrect in inventory and the difference being in column F is how much the inventory is off by +/-. 

Thanks in advance!

[#2]

@Skovoska 

You can have the suspected inventory on hand be column C.  I'm guessing you want Column D because you don't want the inventory person to have to enter by hand the actual number for each row, and short circuit is to check a box if they match.  

Simple way to do this would be to have column D be a "Match expected?" header and just put a "Y" or "N" in the column to indicate yes or no.  ( or just Y for yes, blank is implied no )

Column E is the actual inventory on hand (which if you enter all the actuals, then you can simply check E vs C for variance.

Then you can have a formula like this for column F:

=if($D1="Y",$C1,E1-C1)

You can color format column F for Green/Red by variance threshold etc...

If you don't want to do the "Y" / "N", you could do a box click, but would be more involved to make that work, and would be VB coding as well to align.  Above is simpler IMHO.

hope this helps.