Need assistance with quering remote workbook

I work in a sales environment and our sales months always run from the 22nd through the 21st of the following month.

Agents use a sales tracker that shows their metrics and goals over the duration of the sales month. Each day is tracked on a seperate sheet in the workbook and each sheet calculates stats for its respective date; and a month to date sheet shows overall stats.

I have a seperate workbook that links back to the trackers of all of my agents and pulls entered data in realtime. The problem I have is that the tracker has been designed in such a way that the agent deletes the sheets corresponding to days they do not work(to allow for proper calculation of the month to date stats). Everything on my "2nd" workbook works great until it tries to pull from a date that has been deleted.

All of the agent trackers are stored on a sharepoint server and when the trackers are queried for updates they work fine, but if it queries for a non-existent date, none of the formulas will update and it tries to make me resolve the missing data first. If I am running both spreadsheets on my local PC i can get around the issue by using IFERROR before all the formulas. it works perfect, it just skips the bad data. but if I am trying to pull the info from the trackers when saved on sharepoint IFERROR does not seem to work.

I am at a loss at this point. I need to find a way to check if the sheet exists on the agents tracker before my workbook queries for it.

any ideas?

Hi there,

Can you post samples of your workbook and your agent's workbook here (or even workbooks with dummy data) so that we can have a better idea of what's (not?) happening?

Regards,

Greg M

I was not able to include the orignal docs that I am working with due to proprietary information that is stored in them(even in the blank templates). I created a set that function off the same principal and have the same "qwerks" I am having on my documents.

The agent tracker is the document that holds the agents sales data, the supervisor tracker is the document that is pulling from the agent tracker to fill out a series of graphs.

If they are both open on the same system, they will function properly. If the agent document is kept on a remote location(sharepoint), the supervisor doc will function fine unless the agent removes one of the sheets(which is required for the docment to calculate another series of formulas). they were fine even when removed, as long as both the agent and supervisor docs are opened locally, but if the agent doc is stored on the sharepoint(or any remote location) then the missing sheets will halt the supervisor document.

also, i have attempted using IFERROR to resolve this, but still get the same problem....

At this moment no. So I would say, unless it complicates things, that I might do that in the future. But for the time being they are separate documents for each agent

Hi again,

Our last posts crossed each other as they were being sent

Did you see the question I added in my previous post?

Regards,

Greg M

Sorry, didn't see the second post. on any given month, the agent tracker starts at day 22 and increment up 31,then continues from the first of the month through the 21st. the unused days during the sales month are deleted by the agent.

so in short, yes a maximum of 31 in the agent workbook.
the supervisor workbook would have an amount equal to the number of agents, which is at this point 13. It may also have an overall stats page.but for the sake of this particular situation we will say it does not

Hi again,

As I can't replicate your server situation at this end, it makes it very difficult to test that a solution which works at my end will work at your end also.

How does the following sound as a workaround?

At present, Agents delete worksheets which correspond to days on which they haven't worked. Ok - that means (a) they have to right-click on the worksheet tab and select "Delete", (b) they have to click "Delete" on the resulting prompt, and (c) your Supervisor workbook no longer updates correctly because of the missing worksheet(s).

Would it be simpler to provide a "Delete this worksheet" button on each Agent worksheet - this would not actually delete the worksheet, but would hide it, thereby giving the Agent the impression that the worksheet had in fact been deleted, but leaving it in place so that the Supervisor workbook would not have the problem associated with a missing Agent worksheet?

The VBA code associated with such a solution would be very simple and I can implement it for you if the above workaround would be suitable for your situation.

Regards,

Greg M

I had originally thought of that, the problem I run into is that the agent workbook was originally designed by another employee.he is not willing to allow enhancements or other designs to the original workbook, and While I can alter the copy my team is working with, the workbook changes monthly do too changes in the agents metrics.

the other problem that I have, is that a few the ages of the team are "allergic to technology". Any type of change in the agent tracker that would alter their existing process would be a nightmare. It sounds silly, but you have no idea lol...

Hi again,

Ok - don't worry - I've met enough "technophobes" to know what you're talking about!

The basic problem then becomes one of detecting missing worksheets.

I'm assuming that under normal circumstances the Agent workbooks remain closed on the server while the Supervisor workbook interrogates them via the workbook links. Would it be acceptable to provide an "Open Agent Workbook" button on each worksheet in the Supervisor workbook? This button would open the associated Agent workbook, check that workbook for missing worksheets, update the formulas in the Supervisor workbook accordingly, and then close the Agent workbook.

How does the above sound?

Regards,

Greg M

That actually poses a potential answer. There's a good chance of the agent workbook actually be opened when it's being assessed by the supervisor workbook, but at the same time if I could put a link or button within the supervisor workbook that opens the agent workbook, the server issue it out as read only which would be fine for the situation.

So I guess the next question then would be, how can I have the supervisor workbook assess missing sheets and update the formulas?

Ok - thanks for that.

I'll play around with that possibility for a little while and post something here as soon as I have it ready. It might be a couple of hours, because I've got a few chores to do at this end

Regards,

Greg M

Ok. Thank you for your help

Hi again,

Another quick question - would you consider letting me have a "formulas-only" copy of your Supervisor workbook? You can delete all of the data from it - I'm interested in only the formulas.

I'll send you a Private Message with my email address. If you feel that you can't let me have a copy there's no problem - I'll work with what you've posted here already.

Best regards,

Greg M

Absolutely. That workbook doesn't have anything secret in it. Lol. I will send it to you when I get to work