Thread: Dynamic List from Table Thanks: 0 Likes:  1 Post #5320298 (1)

1. Re: Dynamic List from Table

Originally Posted by TheRogue
Peter - Thank you for your response. The Data in columns B&C are my starting point (that's the data provided), Column A is the result that I'm trying to achieve. However, I need to do it in a way that is dynamic (so that if one of the quantities in column B are changed, the List in column A automatically adds or removes entries, as appropriate.

Would a helper column (could be hidden) be acceptable?

C1 is a stand-alone formula
C2 copied down to the end of your data.
B11 copied down as far as you might ever need.

List

 A B C 1 COUNT ITEM 11 2 3 COLA 1 3 2 GRAPE 4 4 3 CHERRY 6 5 0 DIET 6 1 GINGER ALE 9 7 2 ORANGE 10 8 0 LEMON 9 10 11 COLA 12 COLA 13 COLA 14 GRAPE 15 GRAPE 16 CHERRY 17 CHERRY 18 CHERRY 19 GINGER ALE 20 ORANGE 21 ORANGE 22 23

 Cell Formula C1 =SUM(A2:A8) C2 =IF(A2,SUM(A\$1:A1)+1,"") B11 =IF(ROWS(B\$11:B11)>C\$1,"",LOOKUP(ROWS(B\$11:B11),C\$2:C\$8,B\$2:B\$8))

Excel tables to the web >> Excel Jeanie HTML 4

2. Re: Dynamic List from Table

Yes. Thank you. This does exactly what I want it to. Now, I just have to see if I can "tweak" it some. (The provided data is a table. There is no problem w/ adding a helper column to the table, but the result has to live elsewhere.) You've showed me the concept (which I now understand), now I just have to apply it to my situation.

3. Re: Dynamic List from Table

Originally Posted by TheRogue
Now, I just have to see if I can "tweak" it some. (The provided data is a table. There is no problem w/ adding a helper column to the table, ...
Try something like this

List

 A B C D E F 1 COUNT ITEM Helper List 2 3 COLA 1 COLA 3 2 GRAPE 4 COLA 4 3 CHERRY 6 COLA 5 0 DIET GRAPE 6 1 GINGER ALE 9 GRAPE 7 2 ORANGE 10 CHERRY 8 0 LEMON CHERRY 9 CHERRY 10 GINGER ALE 11 ORANGE 12 ORANGE 13

 Cell Formula C2 =IF([@COUNT],SUM(INDEX([COUNT],1):[@COUNT])-[@COUNT]+1,"") F2 =IF(ROWS(F\$2:F2)>SUM(Table1[COUNT]),"",LOOKUP(ROWS(F\$2:F2),Table1[Helper],Table1[ITEM]))

Excel tables to the web >> Excel Jeanie HTML 4

4. Re: Dynamic List from Table

Thank you very much! This is going to work perfectly.
As usual, I had gotten "lost in the weeds" & was over-complicating it.