PDA

View Full Version : Trouble with pulling data from offline workbooks



kiisaaa
10-24-2016, 05:44 AM
Hi,

I am developing a workbook reporting the main KPI's of our business.

I am going to use the excel reports from the different managers located on different dropbox locations to form this 'master KPI-workbook'. There's several different excel sheets, and even some monthly reports of sales etc in individual workbooks.

The masterbook contains a sheet with a 'presentation' of the KPI's for each month.
Further, does it contain "sales input, research input, department 3 input" and so on.

My plan is to make all the managers copy their reports once a month in a shared folder, where the masterbook is located, so it's easier to pull the requested data from all the reports.


I want to link these individual sheets up to the different excel-reports form the different departments. My sheets needs to pull data from either one, two or even five/ten excel sheets, all depending on which department I am gathering data from.


I am trying to use an extended indirect furmla to pull data from our sales reports,
=INDIRECT.EXT("'["&K1&".xlsx]Sheet1'!f16") - where K1 contains "Results Oktober 2016".
I get the same issue as with the regular indirect forumla, it returns #REF if the workbook is closed.. I have tried other UDFs, but I still get the same issue, and I do not know why..

Any tips on how to easy pull the requested data, without having to have all the workbooks opened at the same time?

:-)

SamT
10-24-2016, 07:29 PM
I am going to use the excel reports from the different managers located on different dropbox locations to form this 'master KPI-workbook'.
My plan is to make all the managers copy their reports once a month in a shared folder, where the masterbook is located,

So you have to start with Dropboxes, and hopefully later, use a Shared Folder on a In-house Server.

I would save all the various manager's reports into a local folder, then in the Master book, I would never use formulas that referenced a cell in a different Book. Transfer the Values themselves as needed. Otherwise some manager will someday complain, "I can't read the CPA report on my Android Go Box BlueFang Wristwatch while flying over the Atlantic."

kiisaaa
10-25-2016, 12:33 AM
Thanks for the reply!

The KPI report are only for me and the CEO to keep track of the performance.

They already have different reports, and I want to pull the different data from them.
The idea is to make them save a copy of their report in a specific dropbox folder once a month, and I was hoping that with an indirect formula / pull function, I was able to extract the data automatically based on the workbook names. But I do have to have the sheets open for the numbers to show..

Or if anyone has any other good suggestion on how to make this as effective as possible. It would be so time consuming to enter all the numbers manually.