Conditiona Formatting to "white out" #N/A

L

Lele

I have pre-populated a spreadsheet to be used as an order form with
v-lookups. Until the user enters an item the #N/A text appears. Is there a
simple way I can use conditional formatting to make the #N/A font color white
so it will appear invisible until a line item is entered at which time I
would change font color back to black?

Also, I would like to show a sums at the bottom of the spreadsheet, but
since I don't know how many items the customer will order I don't know the
number of rows to include

Thanks so much
 
V

Vergel Adriano

I assume that:

1. there's a maximum number of orders in your order form
2. You're using the VLOOKUP function to lookup the price or some numeric value
3 The #N/A is what prevents you from simply summing the entire column with
vlookups.

Maybe something like this would work for you

=IF(ISNA(VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)),
0, VLOOKUP(lookup_value, table_array, col_index_num, range_lookup))
 
S

Shu of AZ

In conditional formatting for the cell you want to ( hide NA ) - FORMULA IS
=ISERROR(Cell#) then press the format button and format cell color as white
 
V

Vergel Adriano

If you want it to be blank instead of showing "0",

=IF(ISNA(VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)),
"", VLOOKUP(lookup_value, table_array, col_index_num, range_lookup))
 
Top