Dynamic Lists

Hi squatcher. Welcome to the forum.

Please upload an Excel workbook sample (not pics or screenshots). It saves retyping data. You will often get more and faster response when you do.

To attach a file to your post,

• be sure to desensitize the data
• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”

The file name will appear at the bottom of your reply.

Try pasting the following formula into cell E3 on sheet1:

Formula:

=IFERROR(IF(INDEX(INDIRECT($D$3),ROW(1:1),COLUMN(E:E))=0,"",INDEX(INDIRECT($D$3),ROW(1:1),COLUMN(E:E))),"")

To get all four entries in the ODORIZER table you'll need to change the 'Refers To:' in the 'Name Manager' so that it reads: =Table!$A$3:$I$6
Let us know if you have any questions.

I added what you gave me and it worked great for the ODORIZER table, but when I went to add another component (heater), it did not retrieve it's information.

The formula in E3 is this "IFERROR(IF(INDEX(INDIRECT($D$3),ROW(1:1),COLUMN(A:A))=0,"",INDEX(INDIRECT($D$3),ROW(1:1),COLUMN(A:A))),"")"
in E2 is this =IFERROR(IF(INDEX(INDIRECT($D$3),ROW(2:2),COLUMN(A:A))=0,"",INDEX(INDIRECT($D$3),ROW(2:2),COLUMN(A:A))),"")

ODORIZER TABLE has 4 components

So after all of the components for ODORIZER are shown, the next cell is E7.
IN E7 the formula is (=IFERROR(IF(INDEX(INDIRECT($D$3),ROW(5:5),COLUMN(A:A))=0,"",INDEX(INDIRECT($D$3),ROW(5:5),COLUMN(A:A))),""))

I think the $D$3 part of the formula should be checking to see if the third column is empty and keep check upwards till if finds an occupied cell.

Also, the ROW (1:1) advances as each portion of the ODORIZER table is added, but afterwards when I place a value in $D7, shouldnt the ROW(x:x) portion of the code change to acknowledge the change in column 3?

I am not sure I am saying this correctly, so please bear with me.

I will include the file.

Thanks for the help so far.
Richard

Added a helper column (XFD) which is populated using: =IF(D3<>"",D3,XFD2)
The formula that populates column E:J now reads:

Formula:

=IFERROR(IF(INDEX(INDIRECT($XFD3),COUNTIFS($XFD$3:$XFD3,$XFD3),COLUMN(A:A))=0,"",INDEX(INDIRECT($XFD3),COUNTIFS($XFD$3:$XFD3,$XFD3),COLUMN(A:A))),"")

Let us know if you have any questions.

Some changes that have been made.
There are now four helper columns XFA:XFD
The formula for XFA is*:

Formula:

=IFERROR(IF(LEFT(XFD3,2)="JB",INDEX(D4:D$42,SMALL(IF(D4:D$19<>"",ROW(4:$19)-ROW(3:3)),COUNTIFS(XFB$3:XFB3,1))),XFD3),"")

The formula for XFB is:

Formula:

=IF(LEFT(XFD3,2)="JB",IF(SUM(M2,1)>MIN(XFC3:XFC11),1,SUM(M2,1)),IF(COUNTIFS(XFD$3:XFD3,XFD3)<=XFC3,COUNTIFS(XFD$3:XFD3,XFD3),""))

The formula for XFC is: =IFERROR(ROWS(INDIRECT(XFD3)),"")
The formula for XFD is still: =IF(D3<>"",D3,XFD2)
The formula for the range E3:J42 is:

Formula:

=IFERROR(IF(INDEX(INDIRECT($XFA3),$XFB3,COLUMN(A:A))=0,"",INDEX(INDIRECT($XFA3),$XFB3,COLUMN(A:A))),"")

*Denotes an array entered formula which is confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER before being copied. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Let us know if you have any questions.

Good call, in cell XFB3 both references to M2 should be references to XFB2. I did the initial set up and testing of the formulas in columns L:O so that I could verify my results as I went along. I guess that I forgot to change that reference when I moved the formulas to columns XFA:XFD.
If the proposed solution meets expectations, please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. A good way to see what a formula is doing is to use the Evaluate Formula feature which is on the Formulas tab.