Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Array Formula to get text

    I'm tryng to eliminate all vba code from a spreadsheet I have inherited. The code is essentially a substitute for an advanced lookup/sumif/array formula - not particularly complicated but I want shot of the code as it takes for ever to calculate.

    Basically I have a data sheet and a presentation sheet. The data sheet has country in column a, distributor in column b and numbers in c through m. There is only ever one country/distributor combination.

    I'm picking up the numbers from the data sheet and putting them in the correct place using an array formula of the following nature:

    {=SUM(($B17=data!$B$3:$B$200)*($A$9=data!$A$3:$A$2 00)*data!H$3:H$200)}

    (b17 is current distributor, a9 current country)

    My problem is that the final column of the data sheet contains text which I need to drag onto the presentation sheet. The sum function obviously doesn't work but I can't figure out what will. I've had a quick search here under array formula, but can't see anything (hopefully I'm not going blind!), checked out Chip Pearson, ditto, and don't think the format of the report will support DGET(). Any suggestions as to an array that will do the trick?

    Thanks in Advance

    Brooke

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array Formula to get text

    How about something like this:

    <pre>=INDIRECT(ADDRESS(MATCH($A$9&$B$17,data!$A$3: $A$200&data!$B$3:$B$200,0),14))
    </pre>


    The above assumes that the text is in column N (14). If it is not, then you will need to change the 14 near the end of the formula to the correct column number.
    Legare Coleman

  3. #3
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array Formula to get text

    Legare,

    Many thanks indeed!
    The final formula is:

    {=INDIRECT("data!"&ADDRESS(MATCH($A$9&$B15,data!$A $3:$A$200&data!$B$3:$B$200,0)+2,17))}

    without the "data!"& it was picking up off the presentation sheet (that had me confused for a while!), the +2 is because my range started at row 3, not 1, and the 17 is because the text wasn't in N as I suggested.

    I don't suppose you can suggest why the following was giving me intermittent results?

    {=CONCATENATE(IF((($A$9=data!$A$3:$A$200)*($B15=da ta!$B$3:$B$200))*1=1,data!Q$3:Q$200,""))}

    Thanks again

    Brooke

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array Formula to get text

    The Concatenate function does not appear to accept array arguments.
    Legare Coleman

  5. #5
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array Formula to get text

    Ah. That would do it.
    Thanks again for your help.

    Brooke

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •