Extracting Data Using Array Aggregate & Multiple Sheets

[DoctorK]

Hi,

I'm using version 2013 and find that the Aggregate function makes the extraction somewhat easier.

One small issue I've overcome by that I set up a separate table to reference the values from the source sheet to help my formula because I have 4 ranges to reference and they're not consecutive and there are "too many arguments". So on the reference table, I'm using an If statement.

 =IF('DID Ranges & Phone Numbers'!$B18="","",'DID Ranges & Phone Numbers'!$B18)

This is referenced on the 'Data sheet do not delete' N2:N225

On my sheet where I want to extract the lines to ('TN Detail Porting') This is the formula I have for my array.

 =IF(ROWS(B$9:B9)>COUNTA('DATA SHEET DO NOT DELETE!'!$N$2:$N$225),"",INDEX('DATA SHEET DO NOT DELETE!'!$N$2:$N$225,AGGREGATE(15,7,(ROW('DATA SHEET DO NOT DELETE!'!$N$2:$N$225)-ROW('DATA SHEET DO NOT DELETE!'!$N$2)+1)/('DATA SHEET DO NOT DELETE!'!$N$2:$N$225<>""),ROWS(B$9:B9))))

I have all of my numbers ascending from the top like I want, but the IF statement isn't having any kind of effect to remove the blanks that are below my numbers and are generating the "#NUM!" error. I can't figure out what is causing this because the logic checks out.

Any ideas?



Thank you!


~Doctor K

 

[iliace]

Hard to tell without seeing the worksheets, but for the formula on the 'TN Detail Porting' sheet, wouldn't this cause a #DIV/0 error for any blank cell?

/('DATA SHEET DO NOT DELETE!'!$N$2:$N$225<>"")

Perhaps you should use multiplication instead?

Just a thought.

 

[DoctorK]

It does, but the Aggregate option allows the ability to ignore errors. In this case, it's option 7 - why it won't ignore the #NUM! error, I don't know.

[COLOR=#333333]AGGREGATE(15,[/COLOR][COLOR=#ff0000]7[/COLOR][COLOR=#333333],[/COLOR]

(Ignore hidden rows and error values). I think I understand where my problem is at. The range that I am referencing is based off of a formula that is depended upon another sheet's input. Because it's off of a formula, the source data is blank or has input and the reference sheet is based on a condition and so that means the COUNTA operator will only return a higher number than the current row I set it to. I can't substitute anything like a dash or dot or otherwise the numbers won't be ascending from the top. =(

I've made a work around and made a conditional statement on the error instead.