Conditiona Formatting to "white out" #N/A
 

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
--
Lele

Conditiona Formatting to "white out" #N/A
 

There's an example here for hiding errors with conditional formatting:

http://www.contextures.com/xlCondFormat03.html#Errors

For the totals, in Excel 2003 you can change the table to a List
(DataListCreate List)
Then choose DataListTotal Row

Lele wrote:
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


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Conditiona Formatting to "white out" #N/A
 

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))



"Lele" wrote:

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
--
Lele

Conditiona Formatting to "white out" #N/A
 

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

"Lele" wrote:

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
--
Lele

Conditiona Formatting to "white out" #N/A
 

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))



"Vergel Adriano" wrote:

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))



"Lele" wrote:

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
--
Lele