Offset a Dynamic Range

[mharper90]

I am trying to create cascading drop down boxes on another sheet in my workbook. The picture shown is aggregated from a sheet in the workbook where materials ordered from suppliers are entered. This sheet just collects the first 2 columns from that materials table, "Category" and "Description". This sheet is dynamic and changes as the materials table is updated.

I have the first drop down working for "Categories", but I'm trying to create a formula for the "Description" drop down that will show just the items in each category, based on which category is selected in the "Categories" drop down list.

Here is my formula so far: Note that the actual drop downs to be used are located on another sheet, hence the cross-sheet references to this sheet (Materials Back-End).

Categories drop down, located at: '1003A'!A6 : =OFFSET('Materials Back-End'!$A$7,,,COUNTIF('Materials Back-End'!$A$7:$A$203,"?*"))

Description drop down is located at: '1003A'!B6 : and I'm struggling to use match to find the right column, and then set the range to the number of items in that category all in one formula.

 

[Peter_SSs]

The best answer for you may well depend on what version od Excel you are using. Suggest that you update your account details to include that information.

Result:

 

[mharper90]

The best answer for you may well depend on what version od Excel you are using. Suggest that you update your account details to include that information.

Thanks. I didn’t know about that. I’ve updated it. I’m using 365 on a Mac.

 

[Peter_SSs]

I’m using 365 on a Mac.

OK, thanks, but that does lead to some further questions:

1. Does your particular version have the =FILTER() function and the =UNIQUE() function available?
   
   
2. If so, can you please provide the current formulas from the sheet shown above for cells ...
   A7 (& confirm copied down?)
   C7 (& confirm copied across?)
   C8 (& confirm copied down?)
   

 

[mharper90]

OK, thanks, but that does lead to some further questions:

1. Does your particular version have the =FILTER() function and the =UNIQUE() function available?
2. If so, can you please provide the current formulas from the sheet shown above for cells ...
   A7 (& confirm copied down?)
   C7 (& confirm copied across?)
   C8 (& confirm copied down?)

1. I do not believe these are available. They don't auto populate if I attempt to type them in.
2. Even though I didn't meet the criteria for question 1, here are my formulas...
A7, and it is copied down from A7 to A203:
=IF(IFERROR(INDEX(MaterialsMaster[Category],MATCH(0,INDEX(COUNTIF('Materials Back-End'!$A$6:A6,MaterialsMaster[Category]),),0)),"")=0,"",IFERROR(INDEX(MaterialsMaster[Category],MATCH(0,INDEX(COUNTIF('Materials Back-End'!$A$6:A6,MaterialsMaster[Category]),),0)),""))
C7, and it is copied across from C7 to GT7:
{=TRANSPOSE(A7:A206)}
C8, and it is copied down and across from C8 to GT1507:
{=IF(IFERROR(INDEX(MaterialsMaster,SMALL(IF(MaterialsMaster[Category]='Materials Back-End'!C$7,ROW(MaterialsMaster)-3),ROW(1:1)),2),"")=0,"",IFERROR(INDEX(MaterialsMaster,SMALL(IF(MaterialsMaster[Category]='Materials Back-End'!C$7,ROW(MaterialsMaster)-3),ROW(1:1)),2),""))}

And just for context, here is a shot of the table, MaterialsMaster, for which the information on 'Materials Back-End' sheet is pulled from. My goal is to have the master materials list for the items my business orders in, and then have a separate sheet for each SKU ('1003A' is the example I'm working on for designing this file) of finished item that we produce for SKU specific price breakdowns. On the SKU specific price breakdown sheet, I will have a table of drop downs for "category" and "description" that allow for materials to be added to the build. Once the drop downs are working, the per unit pricing will auto populate for the correct material in this product specific table so that we can then enter how much of that material goes into each SKU.

Thanks for your help!

 

[mharper90]

So I've made some progress. I used this formula in the "Description" drop down and get the data I want.

=OFFSET(INDEX('Materials Back-End'!C7:L7,,(MATCH(A6,'Materials Back-End'!C7:L7,0))),1,,5)

But now, I want to find a way to replace the "5" at the end of the formula with a dynamic range based on the actual number of list items. Right now, looking in the LABEL category, it shows me the 3 items plus 2 blanks.

*Note, I used C7:L7 just as a smaller range for troubleshooting. 5 was also just a small number picked for testing. It works, but not quite as refined as I want it to be.

 

[mharper90]

And a little more progress/cheating.

I added a counting row (row 5) to the sheet Materials Back-End:

=COUNTIF(C8:C1507, "?*")

Then I reference that cell to replace the "5" I talked about in my last post:

=OFFSET(INDEX('Materials Back-End'!C7:GT7,,(MATCH(A6,'Materials Back-End'!C7:GT7,0))),1,,OFFSET(INDEX('Materials Back-End'!C7:GT7,,(MATCH(A6,'Materials Back-End'!C7:GT7,0))),-2,))

While I'd be curious to figure out if there's a single formula solution, this at least gives me the same result, and because that's a "back-end" sheet, an extra row of data doesn't clutter anything. Please let me know if anyone does have a better solution for me. Thanks for the help!