Reference tab/sheet name in formula

[alo2279]

I would like to reference a sheet name in a formula that will change based on the sheets name.

For example each sheet will be labeled by date but will then need to index a sheet by the same name with "Export" added

Below is the formula i have so far. I want to be able to use this formula without needing to change the export sheet reference each time i make a new one.

=IF(ISBLANK(INDEX('3-13-23 Export'!$B$3:$AR$83,MATCH(XLOOKUP($J25,Roster!$B:$B,Roster!$A:$A,,,),'3-13-23 Export'!$A$3:$A$83,0),MATCH(L$19,'3-13-23 Export'!$B$2:$AR$2,0))),"",INDEX('3-13-23 Export'!$B$3:$AR$83,MATCH(XLOOKUP($J25,Roster!$B:$B,Roster!$A:$A,,,),'3-13-23 Export'!$A$3:$A$83,0),MATCH(L$19,'3-13-23 Export'!$B$2:$AR$2,0)))

I have underlined the reference i need to treat this way

[Glenn Kennedy]

Without a sample sheet, it's hard to be exact... but there seems to be a lot of redundancy in your formula. Depending on the data the ISBLANK bit seems unnecessary.


=INDEX(INDIRECT("'"&A2&" Export'!B3:AR83"),MATCH(XLOOKUP($J25,Roster!$B:$B,Roster!$A:$A,,,),INDIRECT("'"&A2&" Export'!a3:A83"),0),MATCH(L$19,INDIRECT("'"&A2&" Export'!B2:AR2"),0))

should get you going. Please post a sample sheet so we can complete the job. A2 contains the date of the sheet to be references..e.g. 3-13-23

[alo2279]

I have attached a sample. For reference. Please let me know if I'm over doing it.

[Glenn Kennedy]

1. There is no need to do the calculation twice. the addition of &"" will return nulls appropriately.

2. All those INDIRECTS will slow your sheet down, as they recalculate everything each time anything changes.

3. You don't need them anyway. Since you are only referring to ONE sheet, it's better (by far) just to hard code the name into the formula.

In , copied across and down:

=INDEX('2-13-23 Export'!$B$3:$AS$200,MATCH(XLOOKUP($J25,Roster!$B:$B,Roster!$A:$A,,,),'2-13-23 Export'!$A$3:$A$200,0),MATCH(L$19,'2-13-23 Export'!$B$2:$AR$2,0))&""

[alo2279]

Thank you! Does the &"" take the place of the isblank statement

[Glenn Kennedy]

Yes. 0&"" returns a null.

[Glenn Kennedy]

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.