How to extract data from many sheets to one sheet?

Asked By 30 points N/A Posted on -
qa-featured

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.

Extracting data from other sheet

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?

SHARE
Best Answer by Geisha Nitta
Best Answer
Best Answer
Answered By 0 points N/A #134241

How to extract data from many sheets to one sheet?

qa-featured

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.

Extracting data from many sheet

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.

collecting data from many sheet

Then type the following formula in cell C2.

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

Extracting data

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.

Use special Paste options

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

Paste Special Options

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

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

Text to column option

Select Delimited and click next.

Text to column to separate data

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

Set delimiters for data

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

Result of using Text to column
Answered By 0 points N/A #134242

How to extract data from many sheets to one sheet?

qa-featured

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.

Login/Register to Answer

Related Questions