Workaround to retrieve data from closed workbooks by using formulas.

Workaround to retrieve data from closed workbooks by using formulas.

I have some formulas which help me get Data from different tabs based on the column names specified..
I am providing this information by using the Indirect function .
This works fantastic when I have all the Workbooks open, however when they are closed I get an Ref Error..Though Im able to use the Indirect function to give me an added advantage the purpose gets defeated when I end up opening so many Workbooks as there are about 15 such workbooks.
In my quest to get more information on workaround with closed workbooks I did some research and found that By installing the Add-In I can use the modified Indirect Function ..
INDIRECT.EXT( )
But this cannot be Installed on every person using this file.
Is there any other way by modifying the code in pure Excel that I can get this to work.
I would prefer a formula which helps me retrieving data from Closed workbooks as I don’t want others to also install any additional software or codes or Add-Ins once I have made the file..

As per my earlier thread in my quest of getting more info on retrieving data from closed workbooks ...

I came across this link..

http://nwexcelsolutions.com/advanced_function_page.htm

http://nwexcelsolutions.com/advanced...ook_like_this:

which throws light on working on closed worbooks with a formula...

But somehow I have not been able to use it ...

So can anyone please explain the same...

Query:-
The formula =SUM(OFFSET('[Closed Workbook.xls]Sheet A'!$B$1,,,MATCH(A1,'[Closed Workbook.xls]Sheet A'!$A$1:$A$10,0),)) will sum the values in B1:B10 depending on where the match in A1:A10 is. It works nicely if the workbook is opened but returns an error when it is closed, is it possible to use OFFSET when the other workbook is closed ?

Answer :-

Yes, a workaround can look like this:

=SUMPRODUCT((ROW(INDIRECT("B1:B10"))<=MATCH(A1,'[Closed Workbook.xls]Sheet A'!$A$1:$A$10,0))*IF(ISNUMBER
('[Closed Workbook.xls]Sheet A'!$B$1:$B$10),'[Closed Workbook.xls]Sheet A'!$B$1:$B$10))

If "Closed Workbook" is closed the above formula will still work whereas the original formula will return a #VALUE! error.

please someone Im going bonkers doing manual entry as cannot have all the Workbooks open and have to change the formula at every new column..

If the above does not work, is there any workaround using formulas..?

Hope this help you.
Regards
Archimede

http://www.j-walk.com/ss/excel/tips/tip82.htm

Hi
you can values from closed workbook with the formula

B1 ='D:my documents\apples\[closedworkbook.xls]Sheetname'!F9
will return the value in F9 of the sheetname from the closed book you specify located at the file path in the formula
ravi

I want to use Vlookup + Indirect

When Im using plain Vlookup, I need to change the Range in every column however by employing Indirect I simply write one formula which picks up the value or the Name of the tab mentioned in each column as the column heading..

This works fine and I avoid doing the rigmarole of changing theformula at each column for the Vlookup to go in a different Range for eacjh column, however this fails when I close the workbook..

Now I want to retain the benefits of the iNdirect function but also avoid Opening all the Workbooks..

I would appreciate this if there is a formula solution for the same and therefore put the link in the earlier thread..

Hi
How comfortable are you If a macro solution is offered to pull data from closed workbook?
Ravi

Originally Posted by ravishankar

Hi
How comfortable are you If a macro solution is offered to pull data from closed workbook?
Ravi

Dear Ravi,

I would appreciate if you could do that however the Macro I hope should be so robust not to have me modify anything later..

As i dont know anything about macros..

HI
if you give me the filepath, filenames, sheetnames and cell references (rows & columns) from where you want to extract the info. I can write the codes for you
ravi

Originally Posted by ravishankar

HI
if you give me the filepath, filenames, sheetnames and cell references (rows & columns) from where you want to extract the info. I can write the codes for you
ravi

Dear Ravi,

I wish I could add an attachment however the Data is extremely confidential and therefore would need to make a Sample file exclusviely for you...

I can explain what Im trying to do though..

I am maintaining an Attendance Tracker report which gets generated at every month end which has the information for all different departments, So this report is a collation of everyone's details.

And technically I would have 12 such Attendance trackers...


I need to maintain the Data for each Individual Department as well as for every month.


Lets Say i have a Department by the name Logistics..

In this file I would be having 12 tabs for every month and these tabs would be reffering to 12 Attendance trackers..

As the layout is the same for all the 12 Files, they would only differ by their Names and the data they contain..

Now I have made file for Logistics which has the same formulas in all the Sheets but they can still refer to Different files due to their tab names.

As mentioned earlier, the Every Tab would have the name of the Month , lets say- Mar 08, Apr 08, etc...

The naming convention for the Attendance tracker would be maintained in this fashion...

1. Attendance Tracker- Mar 08
2. Attendance Tracker- Apr 08

--------------------------------------------------------------------------

As the Tab name in Logistics would also be named as Mar 08, this would only refert to the data from the file - " Attendance Tracker- Mar 08 ".

this is done by using Vlookup + Indirect...

This information is to give you an Idea...

Hi
I have understood most of what you have said. I need to know How you want the summary / master workbook to look like / summarized . which col/rows you want pulled out and ....
Ravi