Replicating of Sheet from Different Workbooks in a faster way to speed up calculation.

[e4excel]

Replicating of Sheet from Different Workbooks in a faster way to speed up calculation.

Dear Forum,

I am working on Project where I need to access several sheets and generate Report, I have created a Generic Template and which pulls up the data from the specific selected sheet done using a Drop_Down and then populates the data correctly..

Now for this to happen I am using INDIRECT function coerced in an Array formula and which is quite expensive and also since I have several columns of Data the file is becoming heavier..

Now my problem is that the Data which I am fetching is from Sheets in the same File/Work Book and that data is bound to increase every year..
This is going to compound the problem for me as it is the File is becoming very slow due to the several Array and INDIRECT functions in the coding..

So my plan is to cut down the size of the File by seperating the Sheets into different Files so that the Increase File will not affect the speed of the File where I am doing the Calculation..

Now I need advise whether is there a better approach that what I mentioned and the other thing is how do I make this in a quicker way?
my thoughts were to create a Replica of the selected sheet at that Instance in a Dump Sheet and then do the regular work of pulling data without using the INDIRECT function but with ARRAYS as I have several MULTI-LOOKUPS ( INDEX( SMALL(IF))) style coding..

I need help on populating the data as values in the DUMP sheet using VBA..in the FORMATTED DUMP sheet from the OPEN / CLOSED workbook with a specific Name which I am going to maintain..

So please advise and help me on this..

Thanks in advance..

Warm regards
e4excel

[BigBas]

It's possible that you don't need the formulas at all once you find a VBA solution. In many (most) cases, there is a VBA solution that can greatly speed up the process. Of course, without seeing the layout of the data and the expected output, it is difficult for us to assist.

Can you attach a workbook that mimics your current workbook (change any sensitive data to fake data). As long as the layout and output are the same, we can work towards a solution.

[e4excel]

Bigbas thanks for the response...

I just need to get an Idea of Pulling any two columns from a different file..
Lets say I am working in a File Called Account Information and the data in this File was stored in different Sheets with the Account Nos as the Sheet Names and now I have moved these Sheets in different Files based on the Name.

Earlier it was a direct reference to the Sheet Name and now the File Name would come into picture I would be keeping all the Files in the same folder..
SO now lets say if Jack had two accounts and so I will have to refer to the File Name "jack" and then the Sheet Name which is the Account nO..

I just need help in getting the information from different files stored in the same folder and the just need help for 2 Columns..
It will take me some time to create a Dummy File for the same as this is extremely confidential information.

I have the After -Report ready using FOrmulas but I need help in getting the information stored in a particular Sheet called "Selected Account" so In my Multi-Lookup Array Formulas I can omit the INDIRECT part and just have the Array as well as no need to have so many sheets with data just need to have data for one sheet pulled.

Thanks in advance..

[e4excel]

Dear Forum,

Would it be possible to just guide me for now till I can upload a Dummy Worksheet as due to the sensitive nature of the contents I am unable to do it right now..but would be doing that soon...

I have two Main Template Sheets where the Information has to be referred from Different Account No Sheets which have the same format..
These Two Templates have different Structures but they refer the same Account Sheets to generate Reports ..
For generating Reports in these two Template Sheets there were extra columns which had calculations to help generate the report.
In the ear;ier style I had these extra columns in each Account No Sheet and it worked fine when the data was less but as the Data in ever-increasing there's a need to break the Sheets in to Different Files but before I venture on that , on a Trial Basis..
What I have done is just Maintain a Single Dummy Sheet which will store the contents of the Selected Account No..alongwith other columns which were critical to get the information in the two Main Template Sheets..
Instead of using an INDIRECT function to refer to different Account nos from Different Sheets, with those extra columns I refer to a single Dummy Sheet always so need to have the INDIRECT anymore but the data in the DUMMY sheet needs to be influenced by the selection in the Template1 and Template2 one at a time.

Now with my limited VBA skills, what I did..
I have used a Variable called SelectedSheetName in the Sub Routine called Pull_Info which gets the value of the SheetName or the Account No selected in Template1.

But now when I want to use the same for Template2, how DO I do it?

Earlier I had these extra Columns in each Account Sheet which were making the file very heavy and thus very slow, now after changing it to one sheet the size of the File is reduced drastically, however the problem is that both the Template Sheets need to refer to the "Dump" sheet which is done by selecting the Account Nos Drop-Down.

Selected in the Template1 and Template2 influence the data in the Dummy Sheet then how do I make it Toggle?

Regards
e4excel