Ask a new question

Formula to insert worksheet name in a cell

Creating data table in a workbook containing several worksheets.

slno       name       id       civil       mech        elect        instt        elex    .......

These are headings in A1, B1, C1, D1, E1 etc... This data is in first worksheet named "MP".
civil, mech .... are also worksheet names like sheet2 is civil, sheet3 is mech etc.

The list MP has students from all disciplines,
If a student comes from "civil" and is say 31st the the list contained in worksheet "civil",
I wish to put 31 against that student name under column civil.

I am using following formula:

=IF(ISERROR(MATCH($C2,CIVIL!$B$2:$B$701,0)),"",MATCH($c2,CIVIL!$B$2:$B$701,0))
Here MP has field "id" in column C whereas worksheet "civil" has field "id" in column "B".

For this I am manually putting "CIVIL" in the formula. Doing this for all the columns.

Is there any way I can start putting "CIVIL" in cell "D1", then "MECH" in cell "D2" using a formula ?

This way whenever a new section (worksheet) is added, I can extend the formula to rightmost column,
and its name is taken from new worksheet !

Next, once Row1 in a column is named through formula, can the formula given above use content of
cell D1 to fetch data from worksheet "civil"?

You can achieve this by a slight workaround.

In each sheet, if you key-in the following formula in say cell A1 then you will get the current worksheet name in cell A1 as an output of the formula.

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

(Note: this formula will work only for a saved workbook - in other words - a newly created workbook will not give you desired result until it is saved)

You can do this for each worksheet - so in each worksheet , the cell A1 will give you that particular worksheet name.

You can then refer to the cell A1 of each worksheet to get that name in your desired worksheet.

Alternatively, have a look at the youtube video on the following URL, which will guide you to create a list of all the worksheet names in your desired worksheet.

https://www.youtube.com/watch?v=VfGreju7Tc0

Hope you find this Helpful.

Excel is Awesome!! One problem always has multiple solutions.
If my answer solves your problem, please tick mark it as Answered.
Cheers RajeshC

36 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Refer below image of sheet named "CIVIL":

Image

Image of sheet named "MP":

Image

Step 1:

Generate a list of all worksheet names by creating a defined name - ex. in Excel 2007, go to Formulas > Name Manager

Name: Names_Sheets

Refer To: =GET.WORKBOOK(1)&T(NOW())

Step 2:

After creating the defined name as above, enter this formula in cell D1 (sheet MP) & copy to the right till you encounter a blank:

=IFERROR(INDEX(MID(Names_Sheets,FIND("]",Names_Sheets)+1,255),COLUMNS($D$1:E$1)),"")

Step 3:

In cell G1, the next sheet name is displayed: a) Where the formula for Sheet name is already present in cell G1 AND when a New Sheet is Added AND RENAMED; OR 2) If the formula is copied from F1 to G1 for an EXISTING sheet;

Step 4:

Enter this formula in cell D2 and copy down and to the right columns:

=IFERROR(MATCH($C2,INDIRECT("'"&D$1&"'!$B$2:$B$701"),0),"")

Note: The file should be a "macro-enabled file" ie. .xlsm.

You may download the excel file from below link wherein this has been illustrated:

http://globaliconnect.com/excel/Microsoft/DownloadFiles/PostPerSheetName_AutoAddSheetNames.xlsm

Regards,

Amit Tandon

www.globaliconnect.com

4 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info

Last updated March 25, 2025 Views 40,883 Applies to: