How to use Worksheet Name formula within a formula

[SeliM]

Good afternoon
apologies for the unclear heading!
I'm assisting a small organisation with their procurement system. The main component is a proprietary system that exports to excel.
The export file has a different name each time.

I have put this formula together (after many iterations) to capture the worksheet name, =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,31)
The result of the formula is - 'Final_Report-43859_Q23_24_30_De'!$E:$E,0)

To streamline process, I would like to use the formula (above) within an indirect formula to capture data directly from the exported file to ensure the correct worksheet name is captured.

For example a simple INDIRECT(Lookup!$C$2) where Lookup $c$2 holds - 'Final_Report-43859_Q23_24_30_De'!"&ADDRESS(MATCH("Total Score",'Final_Report-43859_Q23_24_30_De'!$E:$E,0)+1,2,1)

If the export file is named 'Final_Report_Score' of course the formula would fail - I'm trying to avoid having to edit dozens of formulas. (the procurement system can hold up to a 100 tender details).

Any advice you could offer to automate the process would be most appreciated.

Hope this makes sense
Mel

 

[saboh12617]

Hello,

Your request makes sense but I suggest you to be very careful with the use of INDIRECT functions. It is volatile and will be reevaluated at every workbook recalculation. So if calculation mode is set to automatic, a lot of time. On top of this you mentionned you wanted to use it on a relatively big dataset (100 rows) and it can make your workbook really slow very fast.

I understand the convenience of indirect function but maybe it would be time to consider using another method. In my opinion.