0

I am having a hard time articulating my need. So, I don't know the key words to use that will find a solution.

I have three columns:

  1. Project_Number
  2. Line_Item
  3. New_Line_Item

A Project_Number can have 50-100 Line_Items.

I am trying to provide a select list populated by Line_Items associated with the Project_Number. The user will use the select list to transfer funding from one Line_Item to another.

This is a simplistic representation of my data. Actual data is not so orderly. I'm just trying to show the concept. The value of the New_Line_Item cell would be selected from a list of Line_Items that have "P1" in the Project_Number column.

     A                   B              C   
1    Project_Number      Line_Item      New_Line_Item
2
3    P1                  P1-A01         Drop down list with values P1-A01, P1-A02, P1-A03
4    P1                  P1-A02         Drop down list with values P1-A01, P1-A02, P1-A03
5    P1                  P1-A03         Drop down list with values P1-A01, P1-A02, P1-A03 
6    P2                  P2-A01         Drop down list with values P2-A01, P1-A02
7    P2                  P2-A02         Drop down list with values P2-A01, P1-A02
7
  • Well, my data representation didn't come across very well.
    – user324275
    May 16, 2014 at 13:57
  • 1
    I made an update to your post.. I added column names and rows, so we can reference the 'cells'. Do take the time to get used to the formatting used here, it will really help you to make your questions clearer :)
    – Dave
    May 16, 2014 at 14:02
  • Can you confirm, using the value you've shown, would C6 show as P2-A02 ? Also, is C3 (the P1-A03) actually a drop down list?
    – Dave
    May 16, 2014 at 14:02
  • Thanks, Dave. Every cell in Column C would be a drop down list. The drop down list in cells C3:C5 would be populated with values in cells B3, B4, and B5. The drop down list in cells C6:C7 would be populated with values from cells B6 and B7.
    – user324275
    May 16, 2014 at 14:20
  • The only time column C will be selected will be when a user is transferring funds to another line_Item in the project. The real data has much longer Line_Items and many more rows per project_Number.
    – user324275
    May 16, 2014 at 14:26

1 Answer 1

1

What you need, and would search for is "Dynamic Dependent Drop Down List".

A general form of this would be setting a name with a value of:

=OFFSET(*top_cell_of_picklist_possibilities*,MATCH(*lookup_value*,*col_of_lookup*,0)-2,0,COUNTIF(*col_of_lookup*,*lookup_value*))

If your data is already sorted and unique by project and then Line, then create a Name, let's call it "Picklist" with a value of:

=OFFSET(Sheet1!$B$2,MATCH(Sheet1!$A2,Sheet1!$A:$A,0)-2,0,COUNTIF(Sheet1!$A:$A,Sheet1!$A2))

Then, set your column C to use a Data Validation List, with a Source:

=Picklist

If it is not sorted and unique, then create a pivot that will make a list of unique Project-Line combinations, sorted by Project_Number then Line_Item, and reference that instead.

1
  • Assuming it works, this is truly excellent :) +1
    – Dave
    May 16, 2014 at 15:08

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .