Removing a Blank in a index():index() Function

[CSCP_EXCEL]

Hello, I am creating a dynamic chart where my series values and series axis labels are stored in name manager. The References are an array created by the index():index() function. The problem I am having is that when the array area sometimes has values in it and sometimes it does not as it is also dynamic. This creates an extra place on my chart where the value and axis label "" are created by this "" in the array. How do I remove the blank in a dynamic array? Here is a sample of the formula:

" =INDEX($AT$8:$AT$30,MATCH($U$11,$AT$8:$AT$30,0)):INDEX($AT$8:$AT$30,MATCH($U$12,$AT$8:$AT$30,0)):INDEX($AT$8:$AT$30,MATCH($U$13,$AT$8:$AT$30,0)):INDEX($AT$8:$AT$30,MATCH($U$14,$AT$8:$AT$30,0)):INDEX($AT$8:$AT$30,MATCH($U$15,$AT$8:$AT$30,0)):INDEX($AT$8:$AT$30,MATCH($U$16,$AT$8:$AT$30,0))

"
The AT column has the product I am looking for, which I am matching to another list that only populates the product if the correct combo box for the category is selected. When there are not enough products to fill the list my IF Function that returns a "" is saved in the array, but not all of them, just the first. The U column is where the dynamic product source list populates.

I am happy to clarify if necessary, thank you.

-Tyler

 

[jasonb75]

That is not creating an array, it is creating a simple range from a union, only 2 out of the 6 index functions serve any real purpose.

I don't think that you would be able to pass an actual array to a chart (not tried it so could be wrong), I would suspect that you would need to duplicate your data in a new range with the blanks omitted, then use that range to feed the chart (similar to the method I used in the link below to omit duplicates from column F into column D).

 

[CSCP_EXCEL]

Ok, a Range, pardon. The range needs to expand and contract based on the user input. I am achieving this using the function above but it is also including the first blank cell below it. I would be interested in seeing how you omit blanks(I do not see your link) in copying your range as my current method:

=IF($U14<>"",INDEX($U14:$U$29,$D$8,),"")

Omits them, at least in view; however the index function I am using is picking it up. I am hoping it is something to do with how I am misusing the function. Thank you for your reply.

 

[jasonb75]

Sorry, thought I had pasted the link. This one is removing duplicates rather than blanks, but the principle needed would be very similar.

Validation dependent lists

Hello everyone, This is my first post here. I've been working with Excel for quite a few years now but now stumbled upon this challenge, for which I can't seem to find a solution. If it's even supported in Excel 2016. Please note I'm not using Office 365 so I can't use all function that Office...

www.mrexcel.com