How to extract data from many sheets to one sheet?

Asked By Kaylie Jane 30 points N/A Posted on - 07/29/2012

Hi expert,

I am keeping record of a company in an excel sheet which includes data about suppliers and their value of item purchased from them. There are more than 500 suppliers and each supplier has separate excel sheet. Following is a sheet of one supplier.

All sheets are in the same format. I want to create a summary sheet which includes all names of suppliers in to sheet at the front. Can you tell me a way to get all names to a one sheet easily without copy paste?

• Status: Open
• Question Views: 1131
• Answer Count: 2
• Vote Up 0 Vote Down

• Answer Accepted: Yes
• Question Category: MS Excel

Best Answer by Geisha Nitta

Go To Solution

Best Answer

Best Answer

Answered By Geisha Nitta 0 points N/A #134241

How to extract data from many sheets to one sheet?

I went through your problem deeply and arrived at a solution. I will take 4 excel sheet with 4 supplier name as an example to explain that.

Please see the image below.

In the sheet 1 type headings as “Number”, “Supplier name” in column A, B.

Then type 1 to 4 numbers in cell A2 to A5.

Then type the following formula in cell C2.

=".='"&A2&"'!B2"

Then copy it down up to cell C5. Then copy the entire data the column (from cell C2 to C5).

Now you have to go to cell D2 and right click and click on the paste special.

After clicking on the values (under paste section) check box and click ok.

Now delete data in column C and select data in column D.

Click on the data tab and click on the text to column.

Select Delimited and click next.

Now click on the other check box and type the full stop (.) in the box.

Click next and finish. Now supplier names will be appearing in Column E. Copy it and value paste (as mentioned above) to column B.

About Geisha Nitta

Questions
1

Answers
55

Best Answers
27

• Vote Up 0 Vote Down

• Posted on - 07/29/2012
• Question Category: MS Excel

Answered By Anthew Staw 0 points N/A #134242

How to extract data from many sheets to one sheet?

If all sheets then in the same format try inserting a new “sheet”, this will serve as your summary sheet.

Then key in sheet 1 in cell “B1”. After that drag it down until you reach the total number of sheets that you want to include in your summary. Then key in this formula “=INDIRECT(ADDRESS(2,2,,,B1))” to cell “C1” then do the same thing you did for the sheer “B1”. 

Hope this helps.

About Anthew Staw

Questions
3

Answers
34

Best Answers
3

• Vote Up 0 Vote Down

• Posted on - 07/29/2012
• Question Category: MS Excel