Combining Columns Into One List that Updates Itself

[brockco]

Hi,

I am trying to create one list which contains the contents of 4 other columns from different parts of my workbook. I would like to have a formula that would always update the list when another entry is created in any of the 4 other columns. For example:

......A........B.........C.......D.........E..........F........
1...List1...List2.....List3...List4..............NewList..
2....2........6..........9.......13...................2.......
3....3........7..........10......14..................3.......
4....4........8..........11......15..................4.......
5........................................................6.......
6........................................................7.......
7........................................................8.......
8........................................................9.......
9........................................................10.....
10......................................................11.....
11......................................................13.....
12......................................................14.....
13......................................................15.....

How could I write a formula that would look at the entire column and only select the cells that have entries? In the example if I were then to add "5" into cell A5, NewList would then have "5" between the 4 and 6.

Thank You in advance!
Brock

[krjoshi]

Hi Brock,
See the attached. Let me know if any issue.

Brock.xlsx

[brockco]

I'm not looking to get the smallest value in each row but rather create a self updating single list of the 4 lists in columns A-D.

[MarvinP]

Hi brockco,

Try this formula and pull it down... See the example file attached:
=IFERROR(SMALL($A$2:$D$20,ROW()-1),"")

[brockco]

Thanks MarvinP for that. I should clarify that I am using part names in these lists and not numbers. How would I go about that?

[MarvinP]

Hi brockco,

Find your answer, but it took some VBA code to do it. Simply add or change a name in the "Start" named range and the list in Column E will update. Look behind sheet1 for VBA Event Code and in Module1 for the Code that does the work. Hope this helps....