Drop Down Boxes and Correlating Data

  • Thread starter Thread starter Sue
  • Start date Start date
S

Sue

Hi,

I've taken over the administration of a vendor timesheet
in my group. (The previous person has left the company or
else I would have asked them how they did this.)

Anyway, what they did was have a drop down box with all
our locations so that when someone picked that location,
the project code that went with that location would be
poppulated for the user. The formula on the column next
to the drop down box reads: =INDEX(ControlSheet!
$B$3:$B$132, ControlSheet!A2,1)

I've been asked to add another column so that when that
location is picked, not only does the project code
populate but so does the PO number.

On the control sheet, column A contains all the
locations, column B contains the project codes, and now
column C should contain the PO numbers. What's wierd to
me is I can't see how this is working. Row 1 has a
statement that says DO NOT REMOVE OR ALTER THESE NUMBERS.
The numbers in cell A2 and B2 seem to change when you
pick a different location on the timesheet tab. Cell C2-
L2 contains 1. Row 3 is blank and row 4 is where the data
starts.

How do I get the formula to work on the timesheet tab?
Meaning, how do I get all 3 columns on the control sheet
to correspond with each other?

I hope this didn't confuse you even more. Any help is
greatly appreciated!
 
Hi Sue
not quite sure I understood it correctly b ut I assume your drop down
box is on a separate sheet - the timesheet tab?.
Try the following (assumption the drop down is in A1)
- in B1 enter the formula
=VLOOKUP(A1,'controlSheet'!$A4:$C$300,2,0)
and in C1 enter
=VLOOKUP(A1,'controlSheet'!$A4:$C$300,3,0)

I made the assumption that the control sheet contains all locations
together with their corresponding project code + PO number in ONE row
 
Yes, the control sheet does contain all location together
with their corresponding project code + PO number in one
row and the drop down is on a separate sheet named the
timesheet tab.

When I entered your formula in, changing the value from
A1 to D14 (where the drop down starts), it returns the
value #N/A.

What does that mean?
 
Hi Sue
I'm not so sure about your current spreadsheet layout. But the value
from D14 has to be found in column A of your control sheet. Thats is
you enter the location in D14 and column a of your control sheet
contains all possible locations

If you still have problems you may mail me your file
(frank[dot]kabel[at]freenet[dot]de)
 
Back
Top