help with a problem calculating a cell

I tried this and it does not quite do what I need. I have attached the file so you can see it.

If you look at cell ac13 which is in yellow it is blank. AB13 tell me that 2 is the standard on hand. we are suppose to populate ac13 with the current on hand and the difference will then be the order qty. The formula you gave me is in h13 and it works so long as an entry is made to ac13 but if we are sold out of the product and leave ac13 blank or put a zero then nothing gets ordered instead to the max of 2 getting ordered.

your formula is good if in this case by entering 0 in ac13 we are ordering 36pc but currently it is not reading the 0 and nothing is ordered

If we put a 2 in ac13 nothing gets ordered which is correct
If we put a 0 in ac13 nothing gets ordered which is incorrect as it should be ordering the max of 2
???maybe j13 and k13 should only add where the content is say between 0 and 100 and ignore blank cells

Is it possible to prevent the number entered in ac13 from being greater than ab13 because in this case if you enter 3 it goes neg which is wrong

Originally Posted by Marcol

Maybe, to return a blank in H14

=IF(K14>0,(J14-K14)*F14,"")

or
to return zero

=IF(K14>0,(J14-K14)*F14,0)

You posted workbook is password protected, this makes it difficult to work with.

I have posted a new unlocked version

I have attached a file and the numbers you have quoted are all correct but if you look at the sheet you will see the problem.

All of the area in yellow is blank as it would be every time we go to use a new copy of it. Not having entered a 0 or qty means the order total should be empty but if you look at the total order at top center the order total is over 6,000. It is ordering everything as if a o had been entered in the yellow box and this is incorrect

OK that is great! you are my hero haha cause I could not have fixed that.

Next question is there any way of limiting the amount put in col in yellow to the number shown in col to the left. If there is no number to the left the cell can be locked but otherwise if 12 is the order max as in ab14 then if someone enters more than that in ac14 then the order becomes a neg number and screws up everything

OK so I thought this was done but it still has an odd problem. I have attached a copy with the latest formulas you gave me.

If you look at line 15 where I have entered 1 in col M this should cause 0 to be ordered and the order value should be zero. It seems by entering that 0 now the rest of the blank yellow cells are being defaulted as if they had o in them and causing the max on the line to be ordered.

Col L, T, AB are min on hand and M, U, AC are where I put the order qty.

1 in col M means we have 1 and the order qty is 0
No entry has been made to col U or AC therefore no qty should be ordered.

If you clear contents in just M you will see all of the order for the line will go to zero even though no change was made to U or AC

By putting an entry in M this should only adjust the order based on what is in L

Hi

Thanks so much, I think that resolves the problem. I will but the sheet to real use and see how it functions.

Do you know if there is a way to change the function of the tab button to tab vertical instead of horizontal?

If I want to add A1 minus B1 to get a total in C1 but not populate C1 if B1 is empty how would the formula look?

I get the following error message

The following features cannot be saved in macro free workbooks

vb project

Perfect got that to work....and I am learning haha

I am not sure if you received my last message on this but this is giving me a new problem. It is not happening on all the lines or all the cells so I think it is somehow the way I have pasted the formulas.

By entering in yellow the max number shown to the left which should result in 0 ordered the order is being put into a neg total. The first few lines appear to be fine but if you look at line 18 V and W is going negative however on the same line AB and AC are calculating correctly. What have I done????

Thanks what was the issue as the 2nd part of the form below also has the same problem?

Hi,

I was wondering if you could answer a question for me.

Here is the situation. I have 3 sheets in a spreadsheet and want to enter information in sheet one which will then populate one of the other 2 sheets depending on the information entered on sheet 1.

In sheet 1 column A I will enter A or B and in column B I will type a statement " The Dog jumps over the fence"

If I enter an A in column A (sheet 1) I want the statement In B (sheet 1) to be copied to Column B on sheet 2
If I enter a B in column A (sheet 1) I want the statement in B (sheet 1) to be copied to Column B on sheet 3

Can you tell me if this is possible?

Thanks,

Mike

PS the other sheet you helped me with is working great, Thanks

@ mikerules

Based on your last 2 posts it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

It also appears that your last post is raising a separate issue, unrelated to the issue for which you started this thread. That being the case, you need to start a new thread.