Validation List values from 3 columns based on INDEX MATCH of value offset from cell

  • I'm having some trouble creating a Data Validation List formula that works.


    I have the following Price list table: [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

    [tr]


    [TD="width: 64"]Item[/TD]
    [TD="width: 64"]LIST[/TD]
    [TD="width: 64"]P1[/TD]
    [TD="width: 64"]P2[/TD]
    [TD="width: 64"]P3[/TD]

    [/tr]


    [tr]


    [td]

    Item1

    [/td]


    [td]

    $5.00

    [/td]


    [td]

    $4.00

    [/td]


    [td]

    $7.00

    [/td]


    [td]

    $2.00

    [/td]


    [/tr]


    [tr]


    [td]

    Item2

    [/td]


    [td]

    $6.00

    [/td]


    [td]

    $5.00

    [/td]


    [td]

    $3.00

    [/td]


    [td]

    $5.00

    [/td]


    [/tr]


    [tr]


    [td]

    Item3

    [/td]


    [td]

    $4.00

    [/td]


    [td]

    $3.00

    [/td]


    [td]

    $5.00

    [/td]


    [td]

    $7.00

    [/td]


    [/tr]


    [tr]


    [td]

    Item4

    [/td]


    [td]

    $5.00

    [/td]


    [td]

    $5.00

    [/td]


    [td]

    $2.00

    [/td]


    [td]

    $3.00

    [/td]


    [/tr]


    [/TABLE]
    On my Main sheet I have a table as follows: [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

    [tr]


    [TD="width: 64"]Item[/TD]
    [TD="width: 64"]LIST[/TD]
    [TD="width: 98"]Price[/TD]

    [/tr]


    [tr]


    [td]

    Item1

    [/td]


    [td]

    $5.00

    [/td]


    [td]

    DROPDOWN

    [/td]


    [/tr]


    [tr]


    [td]

    Item3

    [/td]


    [td]

    $4.00

    [/td]


    [td]

    DROPDOWN

    [/td]


    [/tr]


    [/TABLE]
    What I'm trying to do is on the main sheet, based on the selection on the item column, I need the 3 prices (P1, P2, P3) to be in a dropdown in the "Price" column.


    to get the list column i am using.

    Code
    INDEX(price_list
    [LIST],MATCH(B14,price_list[PN],0))


    I thought maybe by using INDIRECT I could do that 3 times separated by comma's in the DV formula but that doesn't work. I would also have to go to each cell and increment the cell reference (B14). I would like to use OFFSET in the datavalidation formula but I cannot get anything to work. Is this possible?

  • EDIT:


    If I use this in a cell:

    Code
    =INDEX(price_list[P1]&CHAR(44)&price_list[P2]&CHAR(44)&price_list[P3],MATCH(A2,price_list[PN],0))


    I get the expected 3 values separated by a comma. I used CHAR(44) instead of "," because I thought it was conflicting the double quotes in the INDIRECT("..."). However, this does not work in the Data Validation formula:

    Code
    =INDIRECT("INDEX(price_list[P1]&CHAR(44)&price_list[P2]&CHAR(44)&price_list[P3],MATCH(A2,price_list[PN],0))")


    It accepts this, but says resolves to an error and no items in dropdown:

    Code
    =INDEX(INDIRECT("price_list[P1]")&CHAR(44)&INDIRECT("price_list[P2]")&CHAR(44)&INDIRECT("price_list[P3]"),MATCH(A2,INDIRECT("price_list[PN]"),0))


    I feel like this should work... Once I get that figured, I want to replace the "A2" reference with a offset but i'm not sure if that is possible yet.

  • Give this a try:


    =INDEX(INDIRECT("price_list[[P1]:[P3]]"),MATCH(A2,INDIRECT("price_list[PN]"),0),0)

    Where there is a will there are many ways. Finding one that works for you is the challenge!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!