Excel 2007: Auto-populate from list

Hi, I'd like to have formulas in column B of Sheet1 to automatically populate a list of cost center based on the BU specified in column A.  Please help.  Thanks!
Auto-populate.xlsx
JCJGAsked:
Who is Participating?
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
If you can use a helper column then it will make life very easy. See attached.
Copy-of-Auto-populate-1-.xlsx
0
 
ltswebCommented:
Use Vlookup.  I will upload fix.  You have to switch the column orders in the Lookup sheet to use the function.

=VLOOKUP(B2,Lookup!$A:$B,2,FALSE)
Auto-populate-fix.xlsx
0
 
JCJGAuthor Commented:
This is not what I am looking for.  I'd like to be able to enter a BU in cell A2 and column B will be automatically populated all cost centers that belong to that BU.  For example, if I enter "A" in cell A2, column B will list 3 cost centers.  If I enter "B" in cell A2, column B will list 8 cost centers.  Thanks.
0
 
JCJGAuthor Commented:
Thanks for the simple but effective formulas!
0
 
Saqib Husain, SyedEngineerCommented:
These are the functions used in the file

=IFERROR(INDEX(Lookup!B:B,Sheet1!C2+1),"")

How does Sheet1!C2+1 above use as the row number?

=IFERROR(MATCH(Sheet1!$A$2,OFFSET(Lookup!$A$1,Sheet1!C1+1,0,10000),0)+C1,"")

The match function
=MATCH(Sheet1!$A$2,range below previous value,0)

returns the distance of the next occurrence of that BU after the previous BU based on the "range below previous value"

The "range below previous value" can be calculated using the formula

OFFSET(Lookup!$A$1,Sheet1!C1+1,0,10000)

which returns a range starting C1+1 rows after A1 and is 10000 rows long.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.