Discussion A Formula For This? in Excel
https://techcommunity.microsoft.com/t5/excel/a-formula-for-this/m-p/863988#M40410
<P>Hey guys,<BR /><BR />Not too good with excel, but I have an odd question about formulas. I will try to explain this the best I can.<BR /><BR /><SPAN>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 +/-. <BR /><BR /></SPAN>Thanks in advance!</P>Thu, 19 Sep 2019 20:20:13 GMTSkovoska2019-09-19T20:20:13ZA Formula For This?
https://techcommunity.microsoft.com/t5/excel/a-formula-for-this/m-p/863988#M40410
<P>Hey guys,<BR /><BR />Not too good with excel, but I have an odd question about formulas. I will try to explain this the best I can.<BR /><BR /><SPAN>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 +/-. <BR /><BR /></SPAN>Thanks in advance!</P>Thu, 19 Sep 2019 20:20:13 GMThttps://techcommunity.microsoft.com/t5/excel/a-formula-for-this/m-p/863988#M40410Skovoska2019-09-19T20:20:13ZRe: A Formula For This?
https://techcommunity.microsoft.com/t5/excel/a-formula-for-this/m-p/864007#M40411
<P><LI-USER uid="412073"></LI-USER> </P><P> </P><P>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. </P><P> </P><P>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 )</P><P> </P><P>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.</P><P> </P><P>Then you can have a formula like this for column F:</P><P>=if($D1="Y",$C1,E1-C1)</P><P> </P><P>You can color format column F for Green/Red by variance threshold etc...</P><P> </P><P>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.</P><P> </P><P>hope this helps.</P>Thu, 19 Sep 2019 20:29:30 GMThttps://techcommunity.microsoft.com/t5/excel/a-formula-for-this/m-p/864007#M40411gckcmc2019-09-19T20:29:30Z