more help please <-noob learning curve

Ok i have a List box where the user selects a product, once selected the price appears in the box next to it (all good)

now if no value is entered in the next row the box where the price should be has #NA in it - this i think is because no product is selected so the VLOOKUP command is returning a no value in the box.

Now the probelm is when i try to use a SUM or range on the price column i get a #!value error becuae it wont count the #NA boxes as a value.

Is there a way to format these cells with a 0 vale rather then #NA?

can the validate data do this?

thanks VBA

where would i apply that though? in the cell doing the sum or in a validate?

ok i tried it but it said there was a circular reference error

what need the sheet to do is i need 150*k3 then i need to divide it by the total value in range I10:I200 but this throws the !value error because of the #NA's

i cannot upload the file because it is 131kb zipped have you got an email i can send it to??

cheers uploaded

http://www.savefile.com/files/259523


thanks again

thanks alot that has completely sorted it out!!!!!!!!!

ok another kwiky for u, in column I how can i set it to on display and count the value less than 0.00 i.e i only want it to show -values and only summ the -values??