if , any nested formula

    hi
    i have 3 lists, i want them validated but shown in one cell.
    col. A have 5 bill types, (Validated "Bill")
    col. B have 28 product types, (Validated "product")
    col. C have 12 material types, (Valided "Material")


    i want in
    F2 to select any of the 3 lists.
    G2 to recognise them by the validation title.
    H2 to categorise them payable or receivables:


    attached is the example.
    Thank you

    Re: if , any nested formula


    I don't understand the results you show. There is nothing in F2, G2 or H2. Are you looking for two data validations? The first picking from Bills, Products, Materials and the second picking from the corresponding lists in columns A to C?

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

    Re: if , any nested formula


    If the example is actually how you intend setting up the worksheet then I would suggest that you rethink and use a proper data layout.. I would also suggest that you format the data as a Table.


    You can't have 3 lists in one cell, you would need to set up dependant data validation method. See this example


    I'm sure that NBVC will sort your formula out

    Re: if , any nested formula


    Perhaps


    try
    =OFFSET(A1,0,(ISNUMBER(MATCH($H$4,A:A,0))*0+ISNUMBER(MATCH($H$4,B:B,0))*1+ISNUMBER(MATCH($H$4,C:C,0))*2),1,1)

    Triumph without peril brings no glory: Just try

    Re: if , any nested formula


    Or... =INDEX($A$1:$C$1,SUMPRODUCT(($A$2:$C$20=H4)*(COLUMN($A$2:$C$20))))

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

    Re: if , any nested formula


    Quote from NBVC;688405

    Or... =INDEX($A$1:$C$1,SUMPRODUCT(($A$2:$C$20=H4)*(COLUMN($A$2:$C$20))))



    Yes, this formula works more than the above one.
    Appreciated. :)

Participate now!

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

Register Yourself Login