INDIRECT to look for sheet and check text in cell

    Hi


    I'm trying to use INDIRECT to go to a particular sheet check a cell for particular text, if it contains that text, do nothing, if it contains something else then display that text.


    I've tried several variations on a theme:
    =IF(INDIRECT("'"&$A3&"'!$M$4="Please Select"," "),MATCH(INDIRECT("'"&$A3&"'!$M$4"))
    and
    =INDEX(INDIRECT("'"&$A3&"'!$M$4"),MATCH(D$2,INDIRECT("'"&$A3&"'!$C$7:$N$7"),0))
    for example but I keep getting the same error at "Please


    Any ideas please?


    Kind regards


    DezB

    Re: INDIRECT to look for sheet and check text in cell


    Hi again,


    Not sure to fully understand your question ...


    1. Is your formula working fine without the test you want to add with the IF() function ...?


    2. If it is working ... have you tried to encapsulate the entire "working formula" into you new test ...?


    Cheers

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Re: INDIRECT to look for sheet and check text in cell


    Hi Carim


    Unfortunately neither version works so I'm obviously getting something wrong somewhere.


    I'd certainly appreciate any suggestions.


    Kind regards.


    DezB

    Re: INDIRECT to look for sheet and check text in cell


    The first formula has a few problems with the brackets:


    =IF(INDIRECT("'"&$A3&"'!$M$4="Please Select"," "),MATCH(INDIRECT("'"&$A3&"'!$M$4"))


    should be something like


    =IF(INDIRECT("'"&$A3&"'!$M$4)="Please Select"," ",MATCH(INDIRECT("'"&$A3&"'!$M$4")))


    And I have no idea what the MATCH function is trying to do. It only contains one of the three arguments it would normally want.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

    Re: INDIRECT to look for sheet and check text in cell


    Hi Batman


    To be honest I was taking a punt on the MATCH. All I wanted the formula to do was look at the sheet name, (the first bit), then go to that sheet, look at a particular cell, the second bit. If that cell says Please Select from the drop down already there, then return nothing. If there is a different selection in that cell to return that.


    Hope this makes sense?


    Kind regards.


    DezB

    Re: INDIRECT to look for sheet and check text in cell


    If the INDIRECT function returns a single cell, as I suspect it will do, then just remove the MATCH function, and associated brackets.


    =IF(INDIRECT("'"&$A3&"'!$M$4)="Please Select","",INDIRECT("'"&$A3&"'!$M$4"))

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

    Re: INDIRECT to look for sheet and check text in cell


    Hi Batman.


    I guess I was just trying to be a bit too clever with it?? Unfortunately it still returns the same error at ="Please for some reason. I've made sure that there are no merged cells, (which I try to avoid anyway). and tried removing the space between Please and Select in the data list and the formula, but no joy I'm afraid.


    Any ideas please :)


    Kind regards


    DezB

    Re: INDIRECT to look for sheet and check text in cell


    Attach your workbook, I can see no reason why Batman's formula should not work if you descriptions are accurate.


    [sw]*[/sw]

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Re: INDIRECT to look for sheet and check text in cell


    You were missing a double quotation mark


    Your formula:


    =IF(INDIRECT("'"&$A3&"'!$M$4)="Please Select","",INDIRECT("'"&$A3&"'!$M$4"))


    should be


    =IF(INDIRECT("'"&$A3&"'!$M$4")="Please Select","",INDIRECT("'"&$A3&"'!$M$4"))


    Note the quotation mark after $M$4 (I have put it in bold red for emphasis)


    Not entirely your fault, I now see Batman missed it out too :)

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Re: INDIRECT to look for sheet and check text in cell


    Hi KjBox


    Thanks for that, it's usually something simple yet easily missed isn't it.


    Really appreciate your help.


    Kind regards.


    DezB

    Re: INDIRECT to look for sheet and check text in cell


    You're welcome.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

Participate now!

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

Register Yourself Login