CSCP_EXCEL
New Member
- Joined
- May 7, 2020
- Messages
- 6
- Office Version
- 2016
- Platform
- Windows
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
" =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