Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 17
Default How to update data from multiple sheets to one specific sheets

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
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 29
Default How to update data from multiple sheets to one specific sheets

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

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 17
Default How to update data from multiple sheets to one specific sheets

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

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 17
Default How to update data from multiple sheets to one specific sheets

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

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 29
Default How to update data from multiple sheets to one specific sheets

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Update same charts in multiple sheets at once Div[_2_] Excel Worksheet Functions 1 January 28th 09 09:10 PM
Update multiple sheets Dave Excel Worksheet Functions 1 March 7th 08 05:03 AM
Update multiple sheets at once Sinner Excel Discussion (Misc queries) 1 September 2nd 07 07:54 AM
Multiple Sheets (Need to create 500 individual sheets in one workbook, pulling DATA Amaxwell Excel Worksheet Functions 4 August 17th 06 06:23 AM
search multiple sheets for specific date, return data in cell to r NonIllegitimiCarborundum Excel Discussion (Misc queries) 0 April 28th 06 09:02 PM


All times are GMT +1. The time now is 10:19 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"