Sorting data from multiple worksheets into a master sheet/tab

[Guest]

I currently have an Excel document with numerous records for various products
containing information regarding product type, model year, concern, failure
mode, etc. Because each product is handled by a different person, I would
like to split each product into a separate sheet but allow anyone to search
for information based on any of the entered information on a 'master sheet'.
Very similar to the way the 'AutoFilter' option works on individual sheets.
The reason I would like to split the products is to give everyone their own
sheet to update and not have data for products spread around a single sheet.

Thus, if anyone has any input on how to create a master sheet with drop down
lists created by all the informaiton on other sheets, that would be great.

 

[Guest]

For a pure formulas-automated play which works to pull in and stack data from
up to 12 identically structured individual sheets (this would be your
individual product sheets) into a single summary/master sheet, try this
sample from my archives (nicely rendered, full details inside):

http://www.savefile.com/files/236284
Auto summarize n stack lines from 12 primary sheets.zip

The desired stacking sequence for data from the 12 individual sheets within
the summary sheet can be defined easily. Just ensure that the sheetnames
entered within the summary sheet's R5:AC5 are consistent with those on the
actual sheet tabs, ie match exactly, except for case.

Data will be stacked continuously, all neatly bunched at the top, w/o any
intervening blank rows. You could then apply autofilter on the stacked
summary table.

As-is, the sample construct caters for a max of 125 lines/rows expected per
indiv. sheet x 12 sheets = 1500 stacked rows (max) in the summary

 

[Guest]

Thanks for the input. I will give it a try. Am I limited to 125 entries?
Also, as data is entered into each of the attached sheets, will the filtered
list in the 'summ' sheet automatically update?

 

[Max]

Am I limited to 125 entries?

No, that's as-is. It can be adapted to suit

Also, as data is entered into each of the attached sheets,
will the filtered list in the 'summ' sheet automatically update?

Yes, it should but you'd first need to change the book's calc mode from
Manual to Auto (Click Tools > Options > Calculation tab > Check "Automatic"

OK)

---

 

[Guest]

Just a couple more questions.

As I only have 7 differnet sheets, I created 5 dummy sheets for the time
being to utilize the formulas as-is. I wsa not clear on how to
shortenlegthen each formula to accomodate less/more data sheets. For the
columns g-p and r->ae I have tried to modify, but I start loosing data.

Also, if I only have 2 header rows, do the numberical "5's" in the formulas
change to "2's"?

Thanks again.

John

Am I limited to 125 entries?

No, that's as-is. It can be adapted to suit

Also, as data is entered into each of the attached sheets,
will the filtered list in the 'summ' sheet automatically update?

Yes, it should but you'd first need to change the book's calc mode from
Manual to Auto (Click Tools > Options > Calculation tab > Check "Automatic"

OK)

---

 

[Max]

.. As I only have 7 different sheets, I created 5 dummy sheets for the
time
being to utilize the formulas as-is. I was not clear on how to
shorten lengthen each formula to accomodate less/more data sheets.
For the columns g-p and r->ae I have tried to modify,
but I start losing data.

Also, if I only have 2 header rows, do the numerical "5's" in the formulas
change to "2's"?

Yes, essentially. Amend the sample this way ..

Assuming max expected data in any individual sheet is still 125 lines,
but with data now from row 3 down (instead of row 6)

In Summ,

In R6, copied down to R130:
=IF(INDIRECT("'"&R$5&"'!A"&ROW(A1)+2)="","",ROW(A1))

Above replaces the previous:
=IF(INDIRECT("'"&R$5&"'!A"&ROW(A1)+5)="","",ROW(A1))

In S6, copied across to AC6, filled down to AC130:
=IF(INDIRECT("'"&S$5&"'!A"&ROW(A1)+2)="","",ROW(A1)+MAX(R$6:R$130))

Above replaces the previous:
=IF(INDIRECT("'"&S$5&"'!A"&ROW(A1)+5)="","",ROW(A1)+MAX(R$6:R$130))


Similarly, replace the "+5" for the point formulas in A6, G6 and L6 with
"+2", viz:

In A6, copied across to E6, filled down to E1505:
=IF(ROW(A1)>COUNT(_1),IF(ROW(A1)>COUNT(R_11),IF(ROW(A1)>COUNT(R_10),IF(ROW(A1)>COUNT(R_9),G6,
INDEX(OFFSET(INDIRECT("'"&$U$5&"'!A:A"),,COLUMN(A1)-1),MATCH(SMALL(_4,ROW(A1)-(COUNT(R_10))),_4,0)+2)),
INDEX(OFFSET(INDIRECT("'"&$T$5&"'!A:A"),,COLUMN(A1)-1),MATCH(SMALL(_3,ROW(A1)-(COUNT(R_11))),_3,0)+2)),
INDEX(OFFSET(INDIRECT("'"&$S$5&"'!A:A"),,COLUMN(A1)-1),MATCH(SMALL(_2,ROW(A1)-COUNT(_1)),_2,0)+2)),
INDEX(OFFSET(INDIRECT("'"&$R$5&"'!A:A"),,COLUMN(A1)-1),MATCH(SMALL(_1,ROW(A1)),_1,0)+2))

In G6, copied across to K6, filled down to K1505:
=IF(ROW(A1)>COUNT(R_8),IF(ROW(A1)>COUNT(R_7),IF(ROW(A1)>COUNT(R_6),IF(ROW(A1)>COUNT(R_5),L6,
INDEX(OFFSET(INDIRECT("'"&$Y$5&"'!A:A"),,COLUMN(A1)-1),MATCH(SMALL(_8,ROW(A1)-(COUNT(R_6))),_8,0)+2)),
INDEX(OFFSET(INDIRECT("'"&$X$5&"'!A:A"),,COLUMN(A1)-1),MATCH(SMALL(_7,ROW(A1)-(COUNT(R_7))),_7,0)+2)),
INDEX(OFFSET(INDIRECT("'"&$W$5&"'!A:A"),,COLUMN(A1)-1),MATCH(SMALL(_6,ROW(A1)-(COUNT(R_8))),_6,0)+2)),
INDEX(OFFSET(INDIRECT("'"&$V$5&"'!A:A"),,COLUMN(A1)-1),MATCH(SMALL(_5,ROW(A1)-(COUNT(R_9))),_5,0)+2))

In L6, copied across to P6, filled down to P1505:
=IF(ROW(A1)>COUNT(R_4),IF(ROW(A1)>COUNT(R_3),IF(ROW(A1)>COUNT(R_2),IF(ROW(A1)>COUNT(R_1),"",
INDEX(OFFSET(INDIRECT("'"&$AC$5&"'!A:A"),,COLUMN(A1)-1),MATCH(SMALL(_12,ROW(A1)-COUNT(R_2)),_12,0)+2)),
INDEX(OFFSET(INDIRECT("'"&$AB$5&"'!A:A"),,COLUMN(A1)-1),MATCH(SMALL(_11,ROW(A1)-(COUNT(R_3))),_11,0)+2)),
INDEX(OFFSET(INDIRECT("'"&$AA$5&"'!A:A"),,COLUMN(A1)-1),MATCH(SMALL(_10,ROW(A1)-(COUNT(R_4))),_10,0)+2)),
INDEX(OFFSET(INDIRECT("'"&$Z$5&"'!A:A"),,COLUMN(A1)-1),MATCH(SMALL(_9,ROW(A1)-(COUNT(R_5))),_9,0)+2))


---