need a formula.

[Gaggie]

I have the following:

a1 = article number which will be typed in.
b1 = vlookup of description
c1 = quantity of article
d1 = price per article
e1 = c1 * d1


If they type in an article number but forget to type in the quantity and they type in a2 an other article.

I do not get a total price, price stays empty, because they haven't filled in the quantity.
But you cannot get back to the quantity cel c1, maybe because i've secured the sheet, except for input cells a1, a2, ... and c1,c2, .....

Does anybody has an idea how to ensure they've filled in a quantity.

With regards,

Gaggie

 

[VBACO]

Would the data validation solve your issue?

 

[Tigs81]

Hi Gaggie

Did you want it to just calculate the quantity as 1, when quantity is blank?

the only other way is to use vba code that i know of to ensure that the quantity field is filled.

Thanks
Tigs

 

[Gaggie]

No, they have to type in any quantity

 

[Gaggie]

No validation does not work for this, because the value of the quantity depends if anything has been filled in a1, otherwise its no problem

 

[VBACO]

Can you post your sheet. I do better when I can see what I am thinking about.

 

[Tigs81]

can you use conditional formatting to highlight the quantity field when its empty?

 

[Sharpefiction]

Could you set data validation on the row below to not allow data entry unless there is a value within the quantity column on the row above?<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
For instance:<o></o>
<o></o>
A1 = 5, B1=Apple, C1=””, D1=””, E1=””<o></o>
A2 = *data validation error* as C1 is blank<o></o>
<o></o>
You could set the validation to something simple like Custom, with a formula ‘=IF(C1="",FALSE,TRUE)’<o></o>

 

[Tigs81]

As i mentioned before the only way that i know of is to use VBA. Is this something that you are able to do.

Eg one way would be to use a Userform, and protect the whole sheet and only be able to enter new data in via the user form. This could definately force the user to enter data into all fields as required.

 

[Gaggie]

i do not know how to attach a file on this forum.
i will have a look at creating a userform.

Thanks