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]

You could just use an If statement as a error catcher. Something like

=IF(C2="","Need Quantity",C2*D2)

 

[Tigs81]

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>

Great work Sharpefiction, I didnt know that you could use a formula there. Thank you.

Gaggie
This would be the best option and easiest.

I think that you will need to untick the 'ignore blank' tick box in the validation window =IF(ISBLANK(C1),FALSE,TRUE) to make it work.


Thanks
Tigs

 

[Gaggie]

Thanks i will give that a try, i will come back later

 

[Sharpefiction]

Great work Sharpefiction, I didnt know that you could use a formula there. Thank you.

Gaggie
This would be the best option and easiest.

I think that you will need to untick the 'ignore blank' tick box in the validation window =IF(ISBLANK(C1),FALSE,TRUE) to make it work.


Thanks
Tigs

No worries, glad to help!

There are lots of options with formulas within the validation of cells and it can save a lot of programming.. The above is just one side of it.

I'd also go with the suggestion of conditional formatting the quantity column so it highlights the cell if it remains blank when there is a value in the first column. I say that as I've always found it best to make things as obvious to the user as possible about what is needed