Need help with my If formula and locking formulas in a cell so i can type over

[wiL]

Example of Column D row 27: =IF(C27>0,(VLOOKUP(E27,All,2,0)),"")

Example of Column H row 27: =IF(C27>0,(VLOOKUP(D27,Cost,2,0)),"")

Example of Column I row 27: =IF(C27>0,((H27*(1-G27))*C27),"")

To briefly explain what I'm trying to do is when i fill in Column C I don't get #N/A's coming up in Column D,H,I. So i was wondering what I can add to these formulas to avoid the #N/A.
I made a screen shot of the form and labeled everything on the picture which is the first link. And if anyone wanted to check out the physical workbook I made a link to that also.
Link to a picture further explaining the contents of the form: http://img202.imageshack.us/img202/4237/picforhelp.png
Uploaded the file to maybe further show you whats wrong rapid share: http://rapidshare.com/files/29925096...Page2.xls.html

Long explanation of the whole form:
Wanted to see if you can by any chance help me out with a problem that I ran into when I was perfecting my excel invoice form. First, I wanted to explain what exactly it is. The invoice form includes a quantity, code number for the item in the description, description/name of item, discount, cost of one unit, cost of units inputted into quantity. I have everything figured out and I think you'd be able to understand it better with the picture I made I'll also host the file on the internet for you so you can better understand the problem. Basically what happens is that when for example someone wants to input an item manually in the description nothing will happen which is good but when you input the quantity in column C, "#N/A" comes up in column D because it doesn't recognize the words in column E because of the VLOOKUP. So what I was wondering is how I would restructure my code so that the "#N/A" wouldn't show up. And one solution i thought of was if instead of finding an inequity code for the number in column C and maybe finding a logical formula for seeing if there is text in column E, but then I also have to figure out what I'm going to do with the code in column H which is also based on whatever # code is in column D. What happens in column H once it sees the code in column D it looks up the price for the item and input it into column H and if there isn't a code in there "#N/A" will come up. So, I have no clue how to fix these problems and was hoping for your insight upon this problem. And if it wasn't a bother how would I lock the cells in columns D,H,I so that when you type text into them manually the code isn't deleted.

[ncmay]

Do all of the cells in your Description range contain Data Validation and reference your list?
If so is the data/validation/Error Alert - "Show error alert after invalid data is entered" checked?

If you want to look at another Invoice workbook, follow the link below:
http://sites.google.com/site/normay/excel-files

[wiL]

Well my boss wants it so that the #N/A doesn't come up no matter what. For example if he wants to type in a product manually he doesn't want errors to show up. Their aren't any problems with the form. But, I wanted to add something to each of the formulas just so that error message doesn't come up. Other then that everything is perfect. And also i was wondering if there is a way to lock a cell with a formula and to be able to type in that same cell without deleting the formula

[teylyn]

Wil, as you've been asked before, please have a look at the forum rules. This is a duplicate post.

[JBeaucaire]

Add an error check like so:

=IF(C27>0,IF(ISERROR(VLOOKUP(E27,All,2,0)), "", VLOOKUP(E27,All,2,0)))


As per forum rules, the title of this thread does not match your problem. Please revise it. "Avoiding "N/A" errors" seems more accurate, yes?

[wiL]

Thanks, it worked but, when Column C is blank Column D returns false.

[JBeaucaire]

Well, you didn't ask about that... a different issue.

=IF(C27=0, "", IF(ISERROR(VLOOKUP(E27,All,2,0)), "", VLOOKUP(E27,All,2,0)))

==========
If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)

[wiL]

Thanks so much!!!! :D
My last issue is how do I lock a formula in a cell so that I am able to free write over it without actually deleting the formula itself.

[wiL]

How would I go about doing the same exact thing to this formula?
=IF(C27>0,((H27*(1-G27))*C27),"")

for this i was thinking =IF(C27=0, "", IF(ISERROR((H27*(1-G27))*C27)), "", ((H27*(1-G27))*C27)) , but i have no idea why it doesn't work...

and i still cant figure the thing out about the formula locking.

I also was curious to ask where the advanced button is in the forum I cant find it.

Thanks, David

[JBeaucaire]

1) You can't type in a cell with a formula. Period. Unless you want to delete the formula.

2) =IF(C27=0, "", IF(ISERROR((H27*(1-G27))*C27), "", (H27*(1-G27))*C27))

3) Look below in the QUICK REPLY box...there is a GO ADVANCED button below it. When editing a previous post, there is also a GO ADVANCED button there as well.

[wiL]

where are these so called blue scale icons you speak of? you helped me a lot. you deserve some credit if there is any to give.

[JBeaucaire]

Every post has blue scales across the top of the post...up to the right next to the POST #...above the date.

Don't forget to edit the first post, click Go Advanced and mark the PREFIX box [SOLVED].