• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to extract same data from different sheet from same excel workbook

How to extract same data from different sheet from same excel workbook

  • to extract same data presenting in different sheet of same workbook

    Votes: 0 0.0%
  • to extract duplicate data from different sheets of same workbook

    Votes: 0 0.0%

  • Total voters
    0

Dinesh Lama

New Member
In the file uploaded there are 3 sheets: A Store, B Store and C Store.
In each sheet "Items" are same and Qty, Price and Total are different.
Now, here I want the data of Pen(Items) not only from the A Store but also from the
B Store and C Store.
I want Qty, Price and Total data from multiple sheets relating to any one Items.
 

Attachments

  • New Microsoft Office Excel Worksheet.xlsx
    9 KB · Views: 3
Hi Dinesh,

See the file, it uses pivot table.

Regards,
 

Attachments

  • New Microsoft Office Excel Worksheet (1).xlsx
    14.1 KB · Views: 6
Actually I want the data of one Item let us suppose Pen
I want the data of Pen from 3 sheets in one sheet.
I have added one more sheet called Output, this is the result I want.
Could you please help me?
 

Attachments

  • New Microsoft Office Excel Worksheet (1).xlsx
    9.8 KB · Views: 3
Sorry I could not get it.
Could you please go through the file that I have uploaded here and please see the sheet named "Output". Hoping to here from you soon.
 

Attachments

  • XL.xlsx
    9.8 KB · Views: 2
Hi Dinesh,
Not sure about your requirement...
If your requirement is simply getting the data from 3 stores in output sheet, then use simple sheet reference....
='A Store'!C2 formula in C2 (Output Sheet )
='A Store'!D2 in D2 (Output Sheet )
='A Store'!E2 in E2 (Output Sheet )
='B Store'!C2 in C2 (Output Sheet )
='B Store'!D2 in D2 (Output Sheet )
='B Store'!E2 in E2 (Output Sheet )

and so on....
 
Thanx for the help Mr. somendra Misra J
Yes, this is the result that I want.
Bt I could not understand the method that you have used in the Sheet 7.
Lets talk about the column, there in one column though the formula is same but its changing its value, how items are changed according to A store, B store and C store. I mean to say that how the relation between items and sheets are set up.
 
@Dinesh Lama

First note few things:
1. Thers is named ranged SheetList in which all the sheet names are mentioned. The same can be seen by going to Name Manager by Formula Tab or by the keyboard shortcut Ctrl+F3.
2. The formula in each column is a array formula entered in the entire column by selecting number of cells in which you expect the output.

Now coming to formula, (COLUMN B formula need no explanation), rest are not all same, if you notice carefully there will be a slight change. The red portion shown below.

=INDIRECT("'"&SheetList&"'!A"&MATCH($G$1,INDIRECT("'"&SheetList&"'!B:B"),0))

So say you want sr. no. from all sheets, and you have 3 sheets, select 3 cells-- press F2 and enter above formula then press Ctrl+Shift+Enter. Suppose you had only 2 sheets and you tried to entered the same in 3 cells then the last cell will show an error.

Regards,
 
Back
Top