Create an if statement in VB to display value or leave blank.

I've exhausted the amount of IF statements i can use in the formula bar in excel 2003, and now must rely on VB editor to create the appropriate function.

But need a little guidance. Here is what my if statement should look like in the formula bar if it was supported:

=IF(SPECS!B170="P",SPECS!I170,IF(SPECS!I170="NA","",IF(SPECS!B170="K",SPECS!I170,IF(SPECS!I170="NA","",IF(SPECS!B170="C",SPECS!I170,IF(SPECS!I170="NA","",IF(SPECS!B170="F",SPECS!I170,IF(SPECS!I170="NA",""))))))))

There are four letters that could be displayed in cell SPECSB170 depending on choice of vendor (P = "pacific", K = "keller", etc.). Based on that vendor choice, the appropriate information represented in the cell SPECS!I170 ought to display.

If the information comes up as "NA," then the cell should be left blank.

I understand how this would be created in the formula bar with a basic if/else statement, but when transitioning over to Visual Basic editor, the formatting need to be a little different, i understand.

If possible, could you please help me convert the above formula to a readable format for VB editor. Thanks!

Here's what i attempted:

Please Login or Register  to view this content.

It doesn't work.

VLookup() will display the content within the cell itself, but, when that content itself happens to be"NA," then the cell should be set to blank.

The cell that i'm referencing, already is referencing from another source. And that original source may have content as "NA" to signify that this product is unavailable. Forgive me if i didn't point this out in my first posting.

I'm familiar with Vlookup(), but instead i'm using index, here's why: http://www.mbaexcel.com/excel/why-in...-than-vlookup/

here you go.

TestWBforNAblank.xls

The invoice sheet is the result of the bid sheet, which pulls that information from the inventory. Basically, the invoice allows for a printer-friendly list of items that are put in the bid. So they must reference one another, leading all the way from the inventory sheet.

The challenge at this point, is to display the inventory items off the bid sheet onto the invoice sheet, but should an item be considered unavailable ("NA"), the cell ought to display as blank so as to not convolute the invoice sheet with a list of NA's, but rather, blankness.

The formula in the formula bar won't recognize if("specified cell"="NA", "") towards the end of the formula, because prior to that statement, the previous statements already stated, supersedes the latter. Ergo, when i say If('specified cell'="P",'cell content',...), so the cell content has seniority over the latter statement [if("specified cell"="NA", "")] and will display the "NA" irrespectively, which should be blank.

If this doesn't make sense, and if your willing, i'd be willing to do a screen share with you.

Hope this makes sense,

Cris

Good evening Marvin,

First I would like to say that I appreciate your assistance with this formula.

The NA you see in the inventory sheet in the excel file I attached, is a physical NA and not a result from any errors. In fact, it's done by intention for the bid, to show that this item must not be added to the invoice. When the bid items get sent over to the invoicing sheet, they all get sent together. I, therefore, must hide the any items that have been NA'd, so i go ahead and hide them. Now, there will always be a several items left intentionally blank towards the bottom; when i say blank, i mean, NA'd. Now instead of having those line items say NA, i would prefer, or rather, my boss would prefer them to be blank. This results in a better looking and professional invoice, while maintaining its dynamic functionality. So, you see, there will always be content to be had; whether the description of an item or simply 'NA.' But the trick I would like to overcome is: How can I write this formula so that, if the value reads 'NA' to return a blank cell, while maintaining readability if not NA.

Again, if you have any confusions about my requests, I'd be glad to do a screen share with you.

Cris