Array Formula to get text

Topic

New Reply

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$200)*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

Reply | Quote

Replies

How about something like this:

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

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.

Reply | Quote

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=data!$B$3:$B$200))*1=1,data!Q$3:Q$200,””))}

Thanks again

Brooke

Reply | Quote

The Concatenate function does not appear to accept array arguments.

Reply | Quote

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

Brooke

Reply | Quote