Excel work book help needed

Asked By shanimadusha 0 points N/A Posted on - 06/20/2012

 

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.

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

• Answer Accepted: Yes
• Question Category: MS Excel

Best Answer by Sherin Viluel

Go To Solution

Best Answer

Best Answer

Answered By Sherin Viluel 0 points N/A #161797

Excel work book help needed

 

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.

About Sherin Viluel

Questions
0

Answers
50

Best Answers
37

• Vote Up 0 Vote Down

• Posted on - 06/21/2012
• Question Category: MS Excel

Answered By Anthew Staw 0 points N/A #161798

Excel work book help needed

 

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. 

 

About Anthew Staw

Questions
3

Answers
34

Best Answers
3

• Vote Up 0 Vote Down

• Posted on - 08/02/2012
• Question Category: MS Excel