Re: Changing number of rows according to information from multiple tables

**dami** · 01-25-2008, 10:29 AM

Hi,
I want names of products from multiple tables (like metal parts in 1 table, fabric in table 2, other material in table 3 and etc...) in sheet 1 to show up in order in another single table in sheet 2 with one entry after the next entry (in sequence with no spaces between different tables from sheet 1). Tricky part is not all the rows of the tables in sheet 1 are filled at all time (# of entries in each table alters depending on the product in sheet 1). So the table in the sheet 2 should make space in its table if something is entered. I hope this makes sense.

thx

**jevni1974** · 01-25-2008, 11:18 AM

Hi Dami,

I have read your message, but I was wondering what you are trying to accomplish ultimately.

Can you please give a little more background info?

**dami** · 01-25-2008, 02:10 PM

Originally Posted by jevni1974

Hi Dami,

I have read your message, but I was wondering what you are trying to accomplish ultimately.

Can you please give a little more background info?

I am going to do calculations for each product obtained from sheet 1 (like cost, weight, life time and etc...) in the sheet 2. It would be nice if there were no spaces between the rows, since my actual list have many products. Also it would be benificial when printing it

BTW file attached is a sample

**jevni1974** · 01-25-2008, 08:51 PM

Okay,

I am wondering why you would keep the same data in separate lists.
Why don't you create one list:

column A, Column B
gold, metal part
etc.

and use that list as a base?

J.

**dami** · 02-04-2008, 10:22 AM

Sheet 1 is a standard sheet that must remain unchanged

Originally Posted by jevni1974

Okay,

I am wondering why you would keep the same data in separate lists.
Why don't you create one list:

column A, Column B
gold, metal part
etc.

and use that list as a base?

J.

**jevni1974** · 02-07-2008, 01:02 PM

Hi Dami,

Then life will get quite complicated and you need to use VBA macros to keep things sychronised. I don't see a possibility to do this with worksheet functions.

**dami** · 02-07-2008, 02:57 PM

Originally Posted by jevni1974

Hi Dami,

Then life will get quite complicated and you need to use VBA macros to keep things sychronised. I don't see a possibility to do this with worksheet functions.

thx for replying, that's a bummer

**Max, Singapore** · 02-08-2008, 08:28 AM

Maybe one formulas play which might work here
Implemented in this sample:
http://www.freefilehosting.net/download/3bjd3
Tables_dami.xls

In Sheet2,

In A2:
=IF(OFFSET(Sheet1!$A$1,ROWS($1:1),)="","",
IF(ISNUMBER(MATCH(OFFSET(Sheet1!$A$1,ROWS($1:1),),
{"Metal parts";"Fabric";"Other"},0)),"",ROW()))
Leave A1 empty

In B2:
=IF(ROWS($1:1)>COUNT(A:A),"",
INDEX(Sheet1!A:A,SMALL(A:A,ROWS($1:1))))
Select A2:B2, copy down to cover the max expected extent of source data.
Minimize/hide col A. Col B returns the required results, all neatly bunched at the top.

**dami** · 02-08-2008, 11:30 AM

Thanks a lot this helps a lot!!, way better than what I have now!!
I'll try to implement this method to my actual doc and see.

If you can adjust the length of the table (so there won't be empty rows at the bottom too, cuz there's another table after the first), that would be awesome, but this method is good enough for now!!

Originally Posted by Max, Singapore

Maybe one formulas play which might work here
Implemented in this sample:
http://www.freefilehosting.net/download/3bjd3
Tables_dami.xls

In Sheet2,

In A2:
=IF(OFFSET(Sheet1!$A$1,ROWS($1:1),)="","",
IF(ISNUMBER(MATCH(OFFSET(Sheet1!$A$1,ROWS($1:1),),
{"Metal parts";"Fabric";"Other"},0)),"",ROW()))
Leave A1 empty

In B2:
=IF(ROWS($1:1)>COUNT(A:A),"",
INDEX(Sheet1!A:A,SMALL(A:A,ROWS($1:1))))
Select A2:B2, copy down to cover the max expected extent of source data.
Minimize/hide col A. Col B returns the required results, all neatly bunched at the top.