Named formula needs to reference any tab and not just the one it was created on

[Mystix]

I'm trying to simplify how I create payroll sheets. So in my example, I have included a couple sample tabs. The tab numbers represent the day the check was issued. We get 1-3 checks per week depending on what work was completed and paid. Every time we get a check issued, I put the customer's name and which workers worked on that job. Having to retype all the formulas every time though is tedious. What I'm trying to accomplish is having a named formula that will work in any tab that is added to the file. I have included an example of how it's supposed to look on each check (this information is then linked to the payroll sheet so the workers can get paid and only the info I need is brought over). My issue is that the formula only works on the tab it was created on, even though the scope of the formula is set to workbook. I have tried several ways to indirect the tab name, but nothing seems to work. As a side note, the date beside CHECK TOTAL is the date the check hits the bank and the workers will get paid. Any help is appreciated, but I would rather not get into VBA if possible. If I need to put the formulas on a new tab, that is fine too. I just need it to work on any tab that's in the sheet.

							Check Amount	Check Date		Customer
							$500.00	3/1/2021		Customer1	-	Worker1	Worker2	Worker3
							$700.00	3/1/2021		Customer2	-	Worker1	Worker4	Worker5
							$35.00	3/1/2021		Customer3	-	Measure
						CHECK TOTAL	$ 1,235.00	3/4/2021
						Owner1 Percent	$ 148.20
						Owner2 Percent	$ 185.25
									HOW IT LOOKS ON EACH PAYROLL SHEET
									Check Date - 03/04/2021
									$500.00	Customer1	-	Worker1	Worker2	Worker3
									$700.00	Customer2	-	Worker1	Worker4	Worker5
									$35.00	Customer3	-	Measure
									Check Total - $1235.00
									Owner2 Percent - $185.25
									Owner1 Percent - $148.20
		MIGHT BE PART OF THE FORMULA							NAMED FORMULA
		=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)							Check Date - 03/04/2021
		2021-0301

 

[Mystix]

For anyone else who needs this, Here is the formula that I was able to basically get working. Still working on other aspects of it, but hope this helps someone else.

=INDEX(INDIRECT("$h$1:$h$50"),MATCH("<lookup item>",INDIRECT("$G$1:$G$50"),0))