3 Excel Questions...

[Dan B]

Hi, please help...

1. Is there anyway to have a range of cells filled in with a color for on
screen purposes, but not have that color print when printing the worksheet?

2. How can I lock only certain ranges of cells, not the entire worksheet,
to prevent editing of those ranges only?

3. I am using the following function: =VLOOKUP(A18,Codes!$A$7:$C$38,2)
Is there anyway to copy this to a range of cells without having a #N/A show
up in the rows that do not have a value in the A column. Basically I want
to have the formula there, but have the cells be blank until a value is
entered into the A column.

I hope that makes sense.

Thanks for the help,

Dan

 

[John Wilson]

Dan,

1. Is there anyway to have a range of cells filled in with a color for on
screen purposes, but not have that color print when printing the worksheet?

Yes. Depends on how it's colored. Conditional Format?? Manual highlighting?

2. How can I lock only certain ranges of cells, not the entire worksheet,
to prevent editing of those ranges only?

Select all the cells that you want the user to be able to edit and then choose
Format/Cells/Protection and uncheck the "Locked" box. Then protect the sheet.

Is there anyway to copy this to a range of cells without having a #N/A show

=IF(ISNA(VLOOKUP(A18,Codes!$A$7:$C$38,2)),0,VLOOKUP(A18,Codes!$A$7:$C$38,2))

Regarding the above VLookup.
Noticed that you don't have the last argument set (meaning it's default is
true).
Is your lookup table in order?
Are you looking for an exact match?

John

 

[Lady Layla]

1) In Page Setup Sheet -- Place a mark in the box labeled Print Black & white

2) Use Protection -- Set it, and unlock the cells you want to eb able to edit


: Hi, please help...
:
: 1. Is there anyway to have a range of cells filled in with a color for on
: screen purposes, but not have that color print when printing the worksheet?
:
: 2. How can I lock only certain ranges of cells, not the entire worksheet,
: to prevent editing of those ranges only?
:
: 3. I am using the following function: =VLOOKUP(A18,Codes!$A$7:$C$38,2)
: Is there anyway to copy this to a range of cells without having a #N/A show
: up in the rows that do not have a value in the A column. Basically I want
: to have the formula there, but have the cells be blank until a value is
: entered into the A column.
:
: I hope that makes sense.
:
: Thanks for the help,
:
: Dan
:
:

 

[Dan B]

1. Manual Highlighting. I know I can set it to print black and white, but
I don't want the cells to be shaded when printed.

3. I am looking for an exact match. I will give your formula a try.

Thanks!!

Dan

 

[John Wilson]

Dan,

I am looking for an exact match. I will give your formula a try.

If you're looking for an exact match, the formula that I gave you
won't work.
Try this instead:

=IF(ISNA(VLOOKUP(A18,Codes!$A$7:$C$38,2,False)),0,VLOOKUP(A18,Codes!$A$7:$C$38,2,False))

A little background:
=IF(ISNA(yourlookup),0,yourlookup)
Is the basic construct of testing for the #N/A and if your Vlookup would result
in #N/A
then return a zero (can be changed to a blank by substituting two double quotes
for the 0)

Now, the True/False option.
Your original formula was missing this 4th argument (which, by default, Excel
sets to True)
True is used when you're trying to find a match within a range of values (like a
tax table).
Your data table has to be in order for it to work.
eg.
In column A you would have the values
1
5
7
9
If you did a lookup on 6, it wouldn't find it on the table, but it would give
you
the value for 5 (which is the last one that did match without being over 6)
The false option would look for an exact match of 6 (and would normally return
an #N/A). With the False option set, the data table doesn't have to be in
any particular order either.

John

 

[Dan B]

Thanks John. You have definitely gone above and beyond...The background
part really helps!

As for your first formula, it seems to be working fine. I do have my data
table sorted in ascending order. Is that why its working? I will also try
your second formula too.

Thanks again!!

 

[John Wilson]

Dan,

I do have my data table sorted in ascending order.
Is that why its working?

Well..........kinda'

With the True option, it's not "looking" for an exact match but will use one
if it's found (as long as the table is in ascending order).
Again, take a table with let's say "aba", "abb" & "abd" (in that order)
If you do a vlookup on any of those values with the True option set,
it'll find it. If you look for "abc", it'll give you whatever it finds for "abb"

("abc" was not found, so the last "true" value was "abb")
Now let's set a different odrer "aba", "abd", "abb"
If you do a vlookup on "abb", it returns the value it finds for "aba"
(in order, "aba" was less than "abb" but the next value on the list
is "abd" (which is greater than "abb") so it reverts back to the value at
"aba").

Generally speaking, if you want an "exact" match, always use the False option.
If you want the "closest" value without going over the value that you're
looking up, use the True option (and make sure your table is in order).

John

 

[Dan B]

That makes perfect sense. I added in the False to the formula and it is
exactly what I need. Thanks again,
Dan