Use Cell Variable In Formula

    I am having an issue using vlookup with named ranges.


    I have a work book with 3 sheets on, the 3rd sheet has lists defined as named ranges and is named as lists.


    The first and second sheet has references to the lists on this sheet, using the validation e.g
    =(named range)


    I was using the following if statement =IF($E$7="web policy",web_policy,IF($E$8="NUD",BU,IF($E$8="RAC",BU,IF($E$8="IB",IB,IF($E$8="BAU",BAU,not_known)))))


    But I now need to add some more to this and have decieded to use Vlookup instead.


    I have the vlookup in the 3rd sheet (lists sheet) and when the vlookup result is returned it returns the list name that I need to select, the problem I am facing is using this result to produce the list on sheet 1 or sheet 2.


    The vlookup I am using is =VLOOKUP('Test Conditions'!E8,AD1:AE7,2,FALSE), but I can't use this on the first sheet as validation can't look at other sheets.


    Any ideas of a way round this.


    Thanks


    Nick[hr]*[/hr] Auto Merged Post;[dl]*[/dl]I have now changed formula slightly to;=VLOOKUP(ad32,AD1:AE7,2,FALSE), AD32 contains the formula that references the first sheet, but when I paste this into the validation I get the following error.
    The list source must be a delimited list, or a reference to a single row or column


    I realise this is a bit of a crude way of doing it, but if it works I will be happy....

    Re: Vlookup Instead Of Nested If's


    A very small sample of the workbook would help here as its a bit difficult to envisage exactly what you are doing... I am thinking of INDIRECT but I'm not quite sure because it would be clearere if I saw the data layout

    Re: Use Cell Variable In Formula


    In the data validation box you would place in the formula


    =INDIRECT(E8)


    However - you will not be able to refer to dynamic ranges with indirect, so you will have to hard name the ASDA list as U1:U5 in the lists sheet as opposed to using something like =OFFSET(Lists!$U$2,0,0,COUNTA(Lists!$U:$U)-1,1) which it looks like you are currently doing.

    Re: Use Cell Variable In Formula


    I thought as much, I had tried that and got it working, is there no other way round it then?


    Nick

    Re: Use Cell Variable In Formula


    Hi how would that work then, surely that would just require more if statements to look up the named ranges?


    Nick

Participate now!

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

Register Yourself Login