Collecting Data from multiple worksheets

[Mickdelo]

Hi All,

I have a load of worksheets that i'd like to collect data from and would appreciate some help with a suitable formula - see following info.

Wsht A is one of over 500+ worksheets, each Wsht is a standalone sheet for a particular BOQ (WBS) item of work. The wksht is split into 2 sections, the MTO section and the ACTUALS section.

The MTO section contains the engineering takeoff for the particular BOQ item and each row relates to a unique TAG No. and details info such as descr., tag no, area & sub-area locations, qty and other info not shown in the sample here.

The ACTUALS section is used to collect data on the various stages of installation for the unique TAG No. - see columns H to O. As TAG's are installed at the various stages, the info is entered into the ACTUALS section.

Sometimes the engineering takeoff is incomplete and ACTUAL field installations result in additional TAG No.s being installed in the field, so additional rows have to be inserted in the spreadsheets - therefore it's not a fixed & static range.

Wsht B is a Summary Collection worksheet that collects data from all the BOQ(WBS) worksheets in a per AREA / SUB-AREA breakdown. That is, all BOQ items are listed down column A and data collected on the ACTUAL installation status per AREA/SUB-AREA in the succeeding columns.
At top of Wsht B is the Period section that is used to nominate the period in which data is sought for reporting purposes .eg. report out what quantity of BOQ(WBS) items were installed for week commencing 23-Feb-09 and ending 27-Feb-09. Other reports such as Daily, Monthly, Year to Date or Job to Date type collections could be obtained by changing the dates in the "FROM" and "TO" cells - see sample result manually inputed for period 23-Feb to 27-Feb.

What I'm looking for is the formula to do this. That is, formula in Wsht B cell I28 that looks at the Period (cells B20 to C20) and collects from the respective BOQ(WBS) worksheet (in this example Wsht A for BOQ item no. 16.8.5) the total quantity installed from column N per SUB-AREA (column E).
One of the challenges is, as stated above, the range my not be fixed (static) as additional rows may be inserted from time to time to cater for additional Tag No.s. Also, and similarly, each worksheet has differing number of rows .eg. some worksheets only have 20 - 30 rows, other worksheets may have 2000 rows - dependent on the number of TAG items for that particular BOQ worksheet.<SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis...IPT><CENTER><TABLE" target="_blank"></CENTER>

 

[Mickdelo]

All,

I'm having trouble getting the worksheets sample to appear in the post properly; will try to get it fixed to see if will display better.

Regards,
Mickdelo

 

[ColinKJ]

Hi Mickdelo,

Any chance you could attach a copy of one of the BOQ WBs.

ColinKJ

 

[Mickdelo]

Hi Colin,

I didnt think you could attach spreadsheets, the alternative being the Colo HTML Maker, which is where i'm having the problem trying to get it to display the sample spreadsheets properly.

If there is another alternative i would be pleased to find out.

Best regards,
Mickdelo

 

[VoG]

Try Excel Jeanie http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

 

[Mickdelo]

VoG,

Thanks for that; downloaded Jeanie and tested it and seems to work better; adding in the sample spreadsheets below here, hopefully comes out ok - thanks for your help.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
2	Wsht A
3	MTO Section							ACTUALS Section
4								Calibrate	15%	Install	70%	Comms	15%	Total
5	BOQ	Description	Tag No	Area	Sub-Area	MTO Qty		Qty	Date	Qty	Date	Qty	Date	Qty	Date
6
7	16.8.5	Pressure TX	124-30801-PTX	FA	FA02	1		1	17-Oct-08	1	12-Nov-08	1	6-Jan-09	1.0	6-Jan-09
8	16.8.5	Pressure TX	124-30802-PTX	FB	FB10	1		1	17-Oct-08	1	18-Nov-08	1	8-Jan-09	1.0	8-Jan-09
9	16.8.5	Pressure TX	124-30803-PTX	FB	FB39	1		1	12-Dec-08	1	16-Jan-09	1	23-Feb-09	1.0	23-Feb-09
10	16.8.5	Pressure TX	124-30804-PTX	FD	FD02	1		1	12-Dec-08	1	16-Jan-09			0.9	16-Jan-09
11	16.8.5	Pressure TX	126-20006-DPTX	FC	FC39	1		1	12-Jan-09					0.2	12-Jan-09
12	16.8.5	Pressure TX	126-20007-DPTX	FD	FD02	1		1	12-Jan-09	1	25-Feb-09			0.9	25-Feb-09
13
14
15	Totals					6.0								4.9	25-Feb-09
Wsht A

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
17	Wsht B
18	Period:	FROM	TO
19		23-Feb-09	27-Feb-09	AREA / SUB-AREA
20				FA			FB			FC			FD
21	BOQ	Description	Service	FA02	FA10	FA39	FB02	FB10	FB39	FC02	FC10	FC39	FD02	FD10	FD39
22
23	16.8.1	Temp TX	Gas
24	16.8.2	Temp HS	Gas
25	16.8.3	Temp IND	Gas
26	16.8.4	Flow IND	Gas
27	16.8.5	Pressure TX	Gas						1				0.9
28	16.8.6	Pressure TX	Gas
29
Wsht B

Any help on workable formula is much appreciated.
Regards,
Mickdelo

 

[ColinKJ]

Hi Mickdelo,

That's good thanks, I'll get back to you on this.

ColinKJ

 

[ColinKJ]

Hi Mickdelo,

I have put together a routine (code below) which should provide what you require.

In putting this together, I have made the following assumptions.

The Headers in your Sheet A & B are on rows 1 to 4.

Also, in your sheet B results, you have a header "Service", which has been populated, however, on your sheet A there is no "Service" Header.

In the code below, reference to Sheet "Summary" reprosents your sheet B.

In addition, there is a sheet named "BASE", where I store a copy of the Summary Headers, from which I restore the Summary Headers after clearing the sheet prior to a new search.

You can have as many sheet A's as you require.

I'm using a Command Button to start a new search, which also validates the dates before running the main routine SummaryUD:

Code:

Private Sub CommandButton1_Click()
SD = Cells(2, 2)
ED = Cells(2, 3)
'If SD Or ED = "" Then GoTo endd
If IsDate(SD) = False Then GoTo endd
If IsDate(ED) = False Then GoTo endd
If (SD <> ED And SD > ED) Then GoTo endd
SummaryUD
endd:
End Sub

Sub SummaryUD()
Sheets("Summary").Activate
SD = Sheets("Summary").Cells(2, 2)
ED = Sheets("Summary").Cells(2, 3)
Columns("A:O").Select
Selection.ClearContents
Sheets("BASE").Activate
Range("A1:O4").Select
Selection.Copy
Sheets("Summary").Activate
Cells(1, 1).Select
ActiveSheet.Paste
Cells(1, 1).Select
Cells(2, 2) = SD
Cells(2, 3) = ED
SR = 4
For a = 1 To ThisWorkbook.Sheets.Count
If Sheets(a).Name = "Summary" Then GoTo nexta
If Sheets(a).Name = "BASE" Then GoTo nexta
r = Sheets(a).Range("O65536").End(xlUp).Row - 3
For b = 4 To r
If Sheets(a).Cells(b, 15) >= SD And Sheets(a).Cells(b, 15) <= ED Then
SR = SR + 1
Cells(SR, 1) = Sheets(a).Cells(b, 1).Value
Cells(SR, 2) = Sheets(a).Cells(b, 2).Value
SAR = Sheets(a).Cells(b, 5)
x = 4
st:
If Cells(4, x) <> SAR Then x = x + 1: GoTo st
Cells(SR, x) = Sheets(a).Cells(b, 14).Value
End If
Next b
nexta:
Next a
End Sub

If you have any difficulty applying this code, send me a private message with your email address, and I'll send you a copy of my working model.

Regards

ColinKJ

 

[Mickdelo]

Colin,

Thanks very much, your solution in its final revision has worked perfectly.

Really appreciate the help.

Best regards,

Mickdelo