Excel work book help needed

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

 

Hello,

I have a excel work book with worksheets which named as 1, 2, 3 Up to 100 and a summary sheet.

Each and every sheet includes a name in cell C2. I want to have each name of every sheet to the summary sheet in column B on after the other row by row.

For example In Cell B2 – Name of the Sheet 1

                                Cell B3 –Name of the sheet 2

Can you tell me a easiest way to do this.

Thanks.

SHARE
Best Answer by Sherin Viluel
Best Answer
Best Answer
Answered By 0 points N/A #161797

Excel work book help needed

qa-featured

 

You can use a combination of excel function to success your expectation as follows.

First, in your summary sheet, type 1 to 100 in a single column row by row.

Then type the following formula in the first cell in the next column as follows.

 

 

 

Then copy the same formula in to other 99 cells.

Now copy that entire column, right click on the same selected data and click on the paste special.

 

 

 

In the next window tick on the values and click ok

 

 

 

Now select the entire column and “Text to Columns” in the Data tools section in the data tab.

Click next in the Text to column window and tick other and type the full stop in the box.

 

 

 

 

 Now click next and click finish.

Now the name which are in the other sheet will be appear in the summary sheet as follows.

Answered By 0 points N/A #161798

Excel work book help needed

qa-featured

 

What you’re saying is that you have:

                                 COLUMN A                     COLUMN B

1                                              

2                                             (name of sheet1)

3                                             (name of sheet2)

4                                             (name of sheet3)

5                                             (name of sheet4)

 

What you should do is key-in the function below on cell B2:

=IFERROR(IF(A2="","",INDIRECT(ADDRESS(2,3,,,"sheet"&A2))),"")

                                  COLUMN A                     COLUMN B

1                                              

2                                             =IFERROR(IF(A2="","",INDIRECT(ADDRESS(2,3,,,"sheet"&A2))),"")

3                                             =IFERROR(IF(A3="","",INDIRECT(ADDRESS(2,3,,,"sheet"&A3))),"")

4                                             =IFERROR(IF(A4="","",INDIRECT(ADDRESS(2,3,,,"sheet"&A4))),"")

5                                             =IFERROR(IF(A5="","",INDIRECT(ADDRESS(2,3,,,"sheet"&A5))),"")

You can actually just drag down cell B2. Hope this helps. 

 

Login/Register to Answer

Related Questions