Getting rid of #n/a

**rnorris67** · 02-27-2007, 02:22 PM

I have two sheets i am working with in the same file. The first sheet is the reference sheet with my rates listed by location or product code. My formula on my pricing page picks up the rates properly and runs down the collumn continuously. When I do not reference a product or location and leave the cell blank I get #n/a and wont allow my totals to run and I do not want it on the spread sheet.
I would like to make a blank cell on my rate sheet = 0 dollars

**starguy** · 02-27-2007, 02:30 PM

Originally Posted by rnorris67

I have two sheets i am working with in the same file. The first sheet is the reference sheet with my rates listed by location or product code. My formula on my pricing page picks up the rates properly and runs down the collumn continuously. When I do not reference a product or location and leave the cell blank I get #n/a and wont allow my totals to run and I do not want it on the spread sheet.
I would like to make a blank cell on my rate sheet = 0 dollars

make formula like this

=if(iserror(your_formula),0,(your_formula))

**rnorris67** · 02-27-2007, 02:53 PM

on sheet one is my pay rate. Column a=description, column b=rate. On sheet two I type in my description, my quantity and the spreadsheet finds my rate, based on my description, and calculates my totals. I f I leave my description blank I get #n/a in my total column. My formula is=vlookup(d4,payrate!a:b,2,false)

**rnorris67** · 02-27-2007, 02:55 PM

on sheet one is my pay rate. Column a=description, column b=rate. On sheet two I type in my description, my quantity and the spreadsheet finds my rate, based on my description, and calculates my totals. I f I leave my description blank I get #n/a in my total column. My formula is=vlookup(d4,payrate!a:b,2,false)

**rnorris67** · 02-27-2007, 03:40 PM

does anyone have an answer.

**VBA Noob** · 02-27-2007, 03:55 PM

Please don't start new threads for the same question and use help as the title or they will be deleted

Also there doesn't seem to be a question to answer.

FYI Vlookup will return #N/A if it can't find a match

VBA Noob

**starguy** · 03-01-2007, 10:32 AM

Originally Posted by rnorris67

on sheet one is my pay rate. Column a=description, column b=rate. On sheet two I type in my description, my quantity and the spreadsheet finds my rate, based on my description, and calculates my totals. I f I leave my description blank I get #n/a in my total column. My formula is=vlookup(d4,payrate!a:b,2,false)

have you tried this

=if(iserror(vlookup(d4,payrate!a:b,2,false)),0,(vlookup(d4,payrate!a:b,2,false))