Posts by NBVC

    Re: Linking Named Cell Ranges Within A Formula


    Not quite sure what you mean about putting 3 words in the first column of G.


    You mean you tried 3 different words and they all didn't work?


    Note...if you copied my formula, I didn't quite change it up to match you locations1, 2, 3.....so maybe that was the issue?



    I fixed it now:


    =IF(ISNUMBER(MATCH("*"&Search1&"*",locations1,0)),INDEX(locations1,MATCH("*"&Search1&"*",locations1,0)),IF(ISNUMBER(MATCH("*"&Search1&"*",locations2,0)),INDEX(locations2,MATCH("*"&Search1&"*",locations2,0)),IF(ISNUMBER(MATCH("*"&Search1&"*",locations3,0)),INDEX(locations3,MATCH("*"&Search1&"*",locations3,0)),"No Place Found")))


    Make sure the names you assigned are exactly as they are in the formula or vice versa.


    If you still have issues, can you post the actually sheet?

    Re: Linking Named Cell Ranges Within A Formula


    Quote from Addy

    Ah!, i get you, sorry it was linked, okay, the test sheet now works, great!! so, if i copy that into the main one, it still dose not work, because the named Cell range is to big, or would it not matter how the cells are placed in a worksheet or book, as long as they are assigned a name ?


    As long as the named cell range is a 1-column range, it should work. If you are looking through multiple ranges as you indicated at the beginning, then I think you will need to search through those locations individually.


    e.g. =IF(ISNUMBER(MATCH("*"&Search1&"*",locations,0)),INDEX(locations,MATCH("*"&Search1&"*",locations,0)),IF(ISNUMBER(MATCH("*"&Search1&"*",locations,0)),INDEX(locations,MATCH("*"&Search1&"*",locations2,0)),IF(ISNUMBER(MATCH("*"&Search1&"*",locations,0)),INDEX(locations,MATCH("*"&Search1&"*",locations3,0)),"No Place Found")))

    Re: Linking Named Cell Ranges Within A Formula


    Quote from Addy

    Just tried that, and it not pick anything, just Place not found.


    The idea is that if I was to put in Abbott Close, the whole address thats in I23.


    its not doing this though?


    Your "locations" range doesn't have "Abbot Close", it has "Abbat Close". Try that.

    Re: Linking Named Cell Ranges Within A Formula


    Quote from Addy

    okay, ill try that, but i thought I2 was only a adding A,B,C,D,E,F contents into one cell.


    shall try that now, thanks


    I didn't see any formulas in I...looks like they were manually concatenated?


    If you want to concatenate with a formula, then try:


    =A2&" "&B2&" "&C2&" "&D2&" "&E2&" "&F2&" "&G2&" "&H2


    Copied down.

    Re: Linking Named Cell Ranges Within A Formula


    First you need to redefine your "locations" range to include only column I. Right now it includes I and J....Match() only works with 1 column ranges.


    Then try formula:


    =IF(ISNUMBER(MATCH("*"&Search1&"*",locations,0)),INDEX(locations,MATCH("*"&Search1&"*",locations,0)),"No Place Found")

    Re: Linking Named Cell Ranges Within A Formula


    Are you looking for something like this?


    =IF(OR(ISNUMBER(MATCH("*"&Search1&"*",Locations,0)),ISNUMBER(MATCH("*"&Search1&"*",Locations2,0)),ISNUMBER(MATCH("*"&Search1&"*",Locations3,0))),Search1&" found","No Place Found")


    Edit: Forgot to insert "Locations3" in last Isnumber(match())) argument

    Re: Dynamic Use Of Sum (without Vba)


    I think it does get sent so that when your receiver gets it, it opens in R1C1 style (note. Their Tools/Options setting will be set to R1C1 too...so if they open a new workbook, they have to reset).....or they can just open another Excel session.