Automatically Filling Cells

[Guest]

Good Day,

I have created a form for my company in Excel and we use it quite
frequently and would like to know if I can make it so some of the fields are
entered automatically and how I would go about it.

There are 2 cells One for Department and the Second for Department Code.
I'd like to make it so when I select the Department from the list it
automatically enters the code in the next field.

Is this possible??

Thank you
Jon

 

[JulieD]

Hi Jon

yes it is possible using a mix of data validation / range names & VLOOKUP
function

let's assume that the form that you're using is on a sheet called Sheet1 and
the department is in cell A1 and the department code is in B1

now, create a new sheet (sheet2)
starting in A1 type Department
in B1 type Department Code
in A2 to say A10 list all your departments
in B2 to B10 enter all the codes

select from A1:A10 and choose insert / name / create - ensure top row is
ticked and now you have a named range called "Department" that refers to
A2:A10 on sheet2
now, select from A2:B10 and click in the name box (little box to left of
formula bar) and type Dept_Info and press ENTER, now you have another name
range called "Dept_Info" that refers to A2:B10 on sheet2

now go back to sheet 1
click in A1 and choose Data / Validation - from the Allow drop down choose
LIST
click in the source line and press F3 - this will display all the range
names you have in your workbook, select Department (it will appear with an =
sign in front of it) click the OK button
you will now have a drop down of your departments in A1

then click in B1,
type
=VLOOKUP(A1,
press F3 again and choose Dept_Info - click OK, finish off the formula as
follows:
=VLOOKUP(A1,Dept_Info,2,0)
which means, lookup what's chosen in A1, in the table called Dept_Info, and
return the associated value from the 2nd column of the table.

now, unfortunately, if there is nothing in A1 you will receive #NA in B1, so
to fix this, edit your formula to read
=IF(ISNA( VLOOKUP(A1,Dept_Info,2,0)),"",VLOOKUP(A1,Dept_Info,2,0))

Hope this helps
Cheers
JulieD

 

[Guest]

That works Perfect!! Thank you SO Much!!
Jon

Hi Jon

yes it is possible using a mix of data validation / range names & VLOOKUP
function

let's assume that the form that you're using is on a sheet called Sheet1 and
the department is in cell A1 and the department code is in B1

now, create a new sheet (sheet2)
starting in A1 type Department
in B1 type Department Code
in A2 to say A10 list all your departments
in B2 to B10 enter all the codes

select from A1:A10 and choose insert / name / create - ensure top row is
ticked and now you have a named range called "Department" that refers to
A2:A10 on sheet2
now, select from A2:B10 and click in the name box (little box to left of
formula bar) and type Dept_Info and press ENTER, now you have another name
range called "Dept_Info" that refers to A2:B10 on sheet2

now go back to sheet 1
click in A1 and choose Data / Validation - from the Allow drop down choose
LIST
click in the source line and press F3 - this will display all the range
names you have in your workbook, select Department (it will appear with an =
sign in front of it) click the OK button
you will now have a drop down of your departments in A1

then click in B1,
type
=VLOOKUP(A1,
press F3 again and choose Dept_Info - click OK, finish off the formula as
follows:
=VLOOKUP(A1,Dept_Info,2,0)
which means, lookup what's chosen in A1, in the table called Dept_Info, and
return the associated value from the 2nd column of the table.

now, unfortunately, if there is nothing in A1 you will receive #NA in B1, so
to fix this, edit your formula to read
=IF(ISNA( VLOOKUP(A1,Dept_Info,2,0)),"",VLOOKUP(A1,Dept_Info,2,0))

Hope this helps
Cheers
JulieD

 

[JulieD]

Hi Jon

i'm glad to hear that & thanks for the feedback

Cheers
JulieD