• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Automate data from main work sheet into different tabs


Hello All,

I am trying to update data from main sheet "Status Summary" to the different tabs

I have around 20 tabs and want to consolidated all sheets and update the formula for all sheets to update.

I have eg. 3 projects 1, 2, 3 .
Each have its own tab Project 1, Project 2, Project 3.

on my main sheet i have the name of project and the role. I want this to be extracted and displayed in all work sheets.

I tried the following ""INDEX('Status Summary'!A2:A97,MATCH('Project 1'!A1,'Status Summary'!B2:B97))"

I am looking for a project name match in A1 of each sheet if it is found than display the project name.

this work but i have to change the name of each sheet for this to work.

I was reading up about "TEXTAFTER(CELL("filename",A47),"]")" i tested this and in cell A47 it gives me the name of the project tab.

how can i add this to the index formula to make this dynamic .

""INDEX('Status Summary'!A2:A97,MATCH('TEXTAFTER(CELL("filename"'!A1,'Status Summary'!B2:B97))"

struggling with the syntax.

Project 1ABCRole1
Project 2XTCRole2
Project 3DDDRole3
TabsProject 1Project 2Project 3
"INDEX('Status Summary'!A2:A97,MATCH('Project 1'!A1,'Status Summary'!B2:B97))"




  • automated txt name.xlsx
    9.9 KB · Views: 5
Hello,it looks like you want to dynamically reference the name of the current sheet within your formula. You can achieve this by using the CELL function to get the filename of the current sheet and then extracting the sheet name using the TEXTAFTER function.

Here's the corrected formula:

INDEX('Status Summary'!$A$2:$A$97, MATCH(TEXTAFTER(CELL("filename"), "]")&'!A1, 'Status Summary'!$B$2:$B$97, 0))

This formula should dynamically reference the name of the current sheet and match it with the project names in the 'Status Summary' sheet.