Auto expand a table based on the number of populated rows in another sheet

**geekyuu** · 09-28-2012, 01:40 PM

I have 3 sheets in an Excel workbook. A full product list is on Sheet 3, on Sheet 2 I have a table that uses dropdown buttons so that the user can scroll through the product list and select the product they want to include, on Sheet 1 I have a form that I would like to populate with the information from the table on Sheet 2.

The tricky part is that I only want information from certain columns of the table on Sheet 2 to be included on Sheet 1, also there will always be a variable number of rows in the table on Sheet 2. So is it possible to link a single column from a table on Sheet 2 to a column on Sheet 1? Also is it at all possible to have the column on Sheet 1 expand (automatically add rows) to match the number of rows that are in the table that is on Sheet 2?

I had originally thought there would be a simple solution for this but now I'm thinking it may not be possible to acheive this in Excel. Any help, suggestions or insight you can provide would be greatly appreciated.

Thank You,
Geeky UU

**Pete_UK** · 09-29-2012, 07:42 PM

It would be helpful if you posted an example workbook, and in there show what you would like to achieve.

Pete

**geekyuu** · 10-03-2012, 01:47 PM

Please find a copy of my sample workbook in attachment. Thanks for the advice.

**Pete_UK** · 10-03-2012, 02:22 PM

I'm just going out now - I'll take a look later if I get chance.

Pete

**Pete_UK** · 10-03-2012, 08:33 PM

It looks like you want to expand row 36 on the SLI sheet to accommodate however many rows you have on the Products in Shipment sheet. A formula cannot insert a new row, so if you wanted a formula solution the approach would be that you pre-define the maximum number of rows that you are likely to need and put formulae in there to bring the data across, and then you could apply autofilter to hide the rows that are not needed (i.e. that are blank). It should be noted, though, that this is not an automatic feature, so if you were then to add a new record to the Shipments sheet you would have to manually refresh the filter to have that record displayed on the SLI sheet.

It might be better for you to seek a VBA solution to this problem.

Hope this helps.

Pete

**geekyuu** · 10-04-2012, 10:38 AM

I appreciate you taking a look and the input Pete. I think I knew I would have to get into VBA to acheive what I wanted but was trying desprately to avoid it.

**Pete_UK** · 10-04-2012, 11:12 AM

Well as I said, it CAN be done with a formula-based solution - it's just that it won't be fully automatic to hide the blank rows.

Pete

P.S. If you think this thread is now done, can you mark it as Solved - the FAQ describes how to do this.