If then else?

  • Thread starter Thread starter John D
  • Start date Start date
J

John D

Can someone help me with the following task. I am
creating a template that will be used by others to input
data for branch locations. The top row will contain
branch information, like branch code, name, location,
phone number, etc. To simplify input, I would like to
have the users be able to input the branch code, via a
drop down box, and have the corresponding branch
information automatically populate the respective cells.
There are about 40 branches in all. All suggestions are
appreciated. Thank you.

John
 
John

You can use Data>Validation>List for the drop-down.

VLOOKUP for the rest.

Your branch info could be on a second sheet or in an out-of-the-way place on
sheet1.

Say drop-down list in Sheet1 A1. Branch info in A1:F40 on Sheet2

Select Sheet1 B1:F1 then enter the following in B1(active cell)to get 5 cols
from one formula.

=VLOOKUP(A1,'Sheet2'!$A$1:$F$50,{2,3,4,5,6},0)

Entered with CRTL + SHIFT + ENTER

Click on drop-down arrow on Sheet1 A1 to choose Branch.

For more on DV see Debra Dalgleish's site.

http://www.contextures.on.ca/xlDataVal01.html

Gord Dibben Excel MVP







In B1 enter =VLOOKUP(A1,Sheet2!A1:F40,2,FALSE)
In C1 enter =VLOOKUP(
In D1 enter =VLOOKUP(A1,AA1:AF40,4,FALSE)
 
John,

The dropdownbox : See Data / Validation / List
The other cells can than be filled by the VLOOKUP function.

You have to set up somewhere ("out of the way") a table
with the branchcode in the first column and the rest of the information in
the column thereafter.



--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
John

Ignore the incomplete formulas below my signature in first post. Got started
then changed my mind.

Cleared from this post.

Gord
 
Gord,

Thank you very much for your input, I am familar with Data>Validation>List.
I will try the rest of the solution. I do not visit these newsgroups often,
but when I have, I have always received great support and feedback, from
individuals like yourself. Thank you

John
Gord Dibben said:
John

Ignore the incomplete formulas below my signature in first post. Got started
then changed my mind.

Cleared from this post.

Gord
Click to expand...
 
Back
Top