I have a file in which I have around 50 sheets with extensive data in each sheet.
I made a list of all the sheet names so that I can create a tabular data of all sheets’ data using indirect address function. I have given the reference sheet names from the list of the sheets in my indirect address function. in Column A, I have listed all the sheets’ names. In rest columns I have Indirect function in which, I have formula like this:- =@INDIRECT(ADDRESS(B$3,B$4,1,,$A7)). In B3.B4 I have the row & column numbers and in A7 I have sheet name. so on….. It is working fine and I am getting dynamic data from each sheet in this single sheet.

However, the file takes lot of time while saving. So, I am planning to take out this sheet in which I am using the Indirect Address function database from this excel file and want to create a new excel file with this database of indirect address function. But I am unable to put reference of the sheet names form another excel file. All database become #REF as the excel sheet names are in another excel file.
When I put “=” and click on the excel sheet name of another file, I am getting error “Excel found problem with one or more formula……….”
So, how to enter the sheet names of the another excel file in Column A.
I want to just enter only the sheet name list referencing all the sheets from another excel file so that I need not to change the entire database in which I have used Indirect Address function.
Need solution.

Thanks,

Need only the correction to be done in Column A of new excel file.