## How to extract data from many sheets to one sheet?

Asked By 30 points N/A Posted on -

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?

SHARE
Answered By 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.

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.

Answered By 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.