Automatic Value in Col E based on Col C.

[JokerFMJ]

Is it possible to have Column E automatically enter a value based on a 4 digit number inserted into Column C?

I have about 200 different 4 digit numbers that can be entered into Column C and 8 different corresponding values that need to appear in Col E... Would this be possible / difficult?

Thanks.

 

[iggydarsa]

you have to provide an example but it seems like it can be done by using VLOOKUP

 

[JokerFMJ]

For example...

Col C...................Col E
3177......................D
9097.....................DG

D and DG values entered automatically.

 

[iggydarsa]

create a brand new sheet (if you already dont have one) that will have the list of possible values you might see in col C and col E, and name that sheet "LookupTable"

for example:
..A.........B
3177.....D
9097.....DG
10000....E
15000....FH

then on the actual sheet that you will work on, type this formula on column E:

=VLOOKUP(C1, LookupTable!$A$1:LookupTable!$B$2, 2,0)

drag down the formula as many rows as you want

 

[JokerFMJ]

That works well, thank you.

One more question: Is there any way to leave Column E blank unless there is a value entered in Column C?

Right now, with that formula you provided, when I have no value in Column C I have the following value in Column E: "#N/A"

Any way to keep that blank?

Thanks.

 

[iggydarsa]

try this

=IF(ISERROR(VLOOKUP(C1,LookupTable!$A$1:LookupTable!$B$2,2,0)),"",VLOOKUP(C1,LookupTable!$A$1:LookupTable!$B$2,2,0))

 

[JokerFMJ]

That seems to get rid of the #N/A message, but now it's only reading Row 1 and 2 from the LookupTable Sheet...

Any idea why?

 

[iggydarsa]

it is because the lookup table is set as between A1 and B2

=IF(ISERROR(VLOOKUP(C1,LookupTable!$A$1:LookupTable!$B$2,2,0)),"",VLOOKUP(C1,LookupTable!$A$1:LookupTable!$B$2,2,0))

you should change the $B$2 part with the cell address of right most bottom cell:

for example:
..A.........B
3177.....D
9097.....DG
10000....E
15000....FH

in that table right most bottom cell would be B4 so you should change the formula to:

=IF(ISERROR(VLOOKUP(C1,LookupTable!$A$1:LookupTable!$B$4,2,0)),"",VLOOKUP(C1,LookupTable!$A$1:LookupTable!$B$4,2,0))

 

[JokerFMJ]

Bah... If only I understood all that excel code I would've saved you some trouble!

Thanks, I appreciate the help.

I have a supplemental question, if you don't mind...

This spreadsheet is going to be used by a lot of people who don't really understand what they're doing... Is there any way I can protect Column E from them deleting the formula and is there any way I can protect the LookupTable sheet from being edited?

Thanks again, y'all.

 

[iggydarsa]

sure you can do that:

-select all the cells on the sheet (press CTRL+A a few times until all the cells are highlighted
-right click on any cell and select "Format Cells" from the popup menu (make sure all the cells are highlighted)
- on the new window go to "Protection" tab and UNCHECK "locked" - this will unlock all the cells
- now select the cells you want to protect and repeat the steps above but this time CHECK "locked" - this way only desired cells will be locked
- on the menu bar select Tools -> Protection -> Protect Sheet
- press OK on the new popup window and if you want you can type a password there as well but you dont have to

if you want to remove the protection you can go to menu bar again and select Tools -> Protection -> Unprotect Sheet...