Automate data from main work sheet into different tabs

[xljgd]

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 1	ABC	Role1
Project 2	XTC	Role2
Project 3	DDD	Role3
	ABC	XTC	DDD
	Role1	Role2	Role3
Tabs	Project 1	Project 2	Project 3
"INDEX('Status Summary'!A2:A97,MATCH('Project 1'!A1,'Status Summary'!B2:B97))"
"TEXTAFTER(CELL("filename",A47),"]")"

Attachments​

 

[Monty]

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.