In worksheet in sheet-1 I have about 15,000 rows in the following columns
A1(Unique id)
A2 Names of persons
A3 Address
A4 Amount for the year 2005
A5 Amount for the year 2006
A6 Amount for the year 2007 (Blank / no date)

In sheet-2 I have 22,500 rows and have following columns
A1 (Unique id)
A2 Names of person
A3 Address
A4 Amount for the year 2007

Same above column in sheet-3, sheet-4 and sheet-5
I have to update the rows of column A6 of sheet 1 through column A4 of
sheet-2, sheet-3, sheet-4 and sheet-5

Please help to filter the data

Khawaja Saab

you can do the following almighty sumif formula in column A6. I am assuming
that the A1 (unique ids) are not repeated in sheet1 and that the ids may be
repeated in sheet2-5.

=SUMIF(Sheet2!$A$2:$A$22500,Sheet1!A2,Sheet2!$D$2: $D$22500)+SUMIF(Sheet3!$A$2:$A$22500,Sheet1!A2,She et3!$D$2:$D$22500)+SUMIF(Sheet4!$A$2:$A$22500,Shee t1!A2,Sheet4!$D$2:$D$22500)+SUMIF(Sheet5!$A$2:A6,S heet1!A2,Sheet5!$D$2:$D$22500)




"Khawajaanwar" wrote:

In worksheet in sheet-1 I have about 15,000 rows in the following columns
A1(Unique id)
A2 Names of persons
A3 Address
A4 Amount for the year 2005
A5 Amount for the year 2006
A6 Amount for the year 2007 (Blank / no date)

In sheet-2 I have 22,500 rows and have following columns
A1 (Unique id)
A2 Names of person
A3 Address
A4 Amount for the year 2007

Same above column in sheet-3, sheet-4 and sheet-5
I have to update the rows of column A6 of sheet 1 through column A4 of
sheet-2, sheet-3, sheet-4 and sheet-5

Please help to filter the data

Sorry dear i just made a wrong qurey the actual query is:
I have to update the column-J (not row A6) of sheet 1 through column-G of
sheet-2, sheet-3, sheet-4 and sheet-5

Please help to filter the data

"walrus" wrote:

Khawaja Saab

you can do the following almighty sumif formula in column A6. I am assuming
that the A1 (unique ids) are not repeated in sheet1 and that the ids may be
repeated in sheet2-5.

=SUMIF(Sheet2!$A$2:$A$22500,Sheet1!A2,Sheet2!$D$2: $D$22500)+SUMIF(Sheet3!$A$2:$A$22500,Sheet1!A2,She et3!$D$2:$D$22500)+SUMIF(Sheet4!$A$2:$A$22500,Shee t1!A2,Sheet4!$D$2:$D$22500)+SUMIF(Sheet5!$A$2:A6,S heet1!A2,Sheet5!$D$2:$D$22500)




"Khawajaanwar" wrote:

In worksheet in sheet-1 I have about 15,000 rows in the following columns
A1(Unique id)
A2 Names of persons
A3 Address
A4 Amount for the year 2005
A5 Amount for the year 2006
A6 Amount for the year 2007 (Blank / no date)

In sheet-2 I have 22,500 rows and have following columns
A1 (Unique id)
A2 Names of person
A3 Address
A4 Amount for the year 2007

Same above column in sheet-3, sheet-4 and sheet-5
I have to update the rows of column A6 of sheet 1 through column A4 of
sheet-2, sheet-3, sheet-4 and sheet-5

Please help to filter the data

Sorry dear I made a wrong query. The correct query is:
that i have to update the whole column-J (J-1:J15,000) of sheet-1 from
column-G of Sheet-2, Sheet-3, Sheet-4 & Sheet-5.

Looking forward to all of your.

"walrus" wrote:

Khawaja Saab

you can do the following almighty sumif formula in column A6. I am assuming
that the A1 (unique ids) are not repeated in sheet1 and that the ids may be
repeated in sheet2-5.

=SUMIF(Sheet2!$A$2:$A$22500,Sheet1!A2,Sheet2!$D$2: $D$22500)+SUMIF(Sheet3!$A$2:$A$22500,Sheet1!A2,She et3!$D$2:$D$22500)+SUMIF(Sheet4!$A$2:$A$22500,Shee t1!A2,Sheet4!$D$2:$D$22500)+SUMIF(Sheet5!$A$2:A6,S heet1!A2,Sheet5!$D$2:$D$22500)




"Khawajaanwar" wrote:

In worksheet in sheet-1 I have about 15,000 rows in the following columns
A1(Unique id)
A2 Names of persons
A3 Address
A4 Amount for the year 2005
A5 Amount for the year 2006
A6 Amount for the year 2007 (Blank / no date)

In sheet-2 I have 22,500 rows and have following columns
A1 (Unique id)
A2 Names of person
A3 Address
A4 Amount for the year 2007

Same above column in sheet-3, sheet-4 and sheet-5
I have to update the rows of column A6 of sheet 1 through column A4 of
sheet-2, sheet-3, sheet-4 and sheet-5

Please help to filter the data

Perhaps you have some blank columns as well because in sheet1 u have the
following columns

Column
A1 A
A2 B
A3 C
A4 D
A5 E
A6 F

so you are missing Columns G,H and I. Same is the case with your sheets 2-5.
You have given only 4 columns (A1,2,3,4) and that would go to column D in MS
Excel and not G.

can you re-paste a portion of your table here??






"Khawajaanwar" wrote:

Sorry dear I made a wrong query. The correct query is:
that i have to update the whole column-J (J-1:J15,000) of sheet-1 from
column-G of Sheet-2, Sheet-3, Sheet-4 & Sheet-5.

Looking forward to all of your.

"walrus" wrote:

Khawaja Saab

you can do the following almighty sumif formula in column A6. I am assuming
that the A1 (unique ids) are not repeated in sheet1 and that the ids may be
repeated in sheet2-5.

=SUMIF(Sheet2!$A$2:$A$22500,Sheet1!A2,Sheet2!$D$2: $D$22500)+SUMIF(Sheet3!$A$2:$A$22500,Sheet1!A2,She et3!$D$2:$D$22500)+SUMIF(Sheet4!$A$2:$A$22500,Shee t1!A2,Sheet4!$D$2:$D$22500)+SUMIF(Sheet5!$A$2:A6,S heet1!A2,Sheet5!$D$2:$D$22500)




"Khawajaanwar" wrote:

In worksheet in sheet-1 I have about 15,000 rows in the following columns
A1(Unique id)
A2 Names of persons
A3 Address
A4 Amount for the year 2005
A5 Amount for the year 2006
A6 Amount for the year 2007 (Blank / no date)

In sheet-2 I have 22,500 rows and have following columns
A1 (Unique id)
A2 Names of person
A3 Address
A4 Amount for the year 2007

Same above column in sheet-3, sheet-4 and sheet-5
I have to update the rows of column A6 of sheet 1 through column A4 of
sheet-2, sheet-3, sheet-4 and sheet-5

Please help to filter the data