Ignore Broken Links?

[TesseractE]

I have a workbook that scrapes month-to-date information from individual daily sheets. I obviously can't use INDIRECT with VLOOKUP to make each day's link different, so I have to hard code them all. Other than having to have each one reflect a separate day, these are very basic VLOOKUPs I'm working with.

It works perfectly fine during the month, but when I try to make a new sheet at the beginning of a new month, I have to make 31 'dummy' files or when I do a text replacement of swapping 'July' for 'August' I'll have to stop and update the link twice a cell... for 31 rows... and 9 columns... on each of about 9 sheets.

Yeah. That sucked the first time.

So I was wondering if there's a way to make the sheet not care if the linked file is missing just long enough for me to do a find/replace at the beginning of a month. Perhaps you folks might even come up with a better way to accomplish this.

 

[pbornemeier]

Q1 Are all the daily sheets in the same workbook as the month-to-date worksheet? (i believe not)
Q2 What is the format for naming the worksheets?
Q3 If worksheets are in different workbooks, what is the format for naming the workbooks?
Q4 If worksheets are in different workbooks, are they all in the same directory? If not, provide more info.
Q5 What version of Excel are you using?

Please give a few examples of your hard-coded links.

Is there a reason not to use code, rather than formulas?

 

[TesseractE]

Q1 Are all the daily sheets in the same workbook as the month-to-date worksheet? (i believe not)

No, the daily workbooks are separate files. Each day needs a book with five sheets for all the recording metrics, so slimming it down to all be in one book wouldn't work.

Q2 What is the format for naming the worksheets?

The Sheets in my Monthly Summary book are named for the data they scrape from the Daily Books.

Q3 If worksheets are in different workbooks, what is the format for naming the workbooks?

'Call Elements mm-dd-yy.xlsm'

Q4 If worksheets are in different workbooks, are they all in the same directory? If not, provide more info.

The Daily Workbooks and the Monthly Summary are all in the same folder, yes.

Q5 What version of Excel are you using?

Excel 2007

Please give a few examples of your hard-coded links.

It's a simple VLOOKUP with a Match.

=VLOOKUP(B$1,'[Call Elements 08-10-12.xlsm]Total Productivity'!$B$6:$S$26,MATCH("Total # of Calls Reviewed",'[Call Elements 08-10-12.xlsm]Total Productivity'!$B$3:$S$3,0))

B$1 contains the name of the Agent for whom we're scraping the total number of calls reviewed.

Is there a reason not to use code, rather than formulas?

I'm not terribly great with code. I've used a few snippets of code here and there, but I'm much more handy with a formula than I am VB. I'd love to just ask for someone here to help with VB coding, but I need to be able to understand everything I put into these sheets, since my managers and their managers like to pop-quiz me on how the sheets work.

 

[pbornemeier]

Could you please provide a few more sample VLOOKUP formulas and include the cells where each is located. I want to understand how the formula changes between rows and columns. One of your original comments "...can't use INDIRECT with VLOOKUP to make each day's link different..." may not be entirely correct. The ROW() and COLUMN() functions can increment an INDIRECT reference as the formula is copied between rows and columns.

 

[TesseractE]

Hmmm... Interesting. I hadn't thought of that combination, but the issue is more with the fact that if I use INDIRECT in my VLOOKUP formula, I can't have it reference a closed workbook.

Sadly, I can't use the plugins at work, so I'll have to use a screencap.

Bringing in the formula I shared earlier:

=VLOOKUP(B$1,'[Daily Tracker 08-01-12.xlsm]Total Productivity'!$B$6:$S$26,MATCH("Total # of Calls Reviewed",'[Daily Tracker 08-01-12.xlsm]Total Productivity'!$B$3:$S$3,0))

This is looking for a specific value for 'Name 1' on the Daily Tracker book and bringing it into the Summary. On 08-01-12, she only had 1 call reviewed, but on 08-02-12, she had 11.

What I'd LIKE to do is something like this:

=VLOOKUP(B$1,"'[Daily Tracker "&$A2&".xlsm]Total Productivity'!$B$6:$S$26",MATCH("Total # of Calls Reviewed","'[Daily Tracker "&$A2&".xlsm]Total Productivity'!$B$3:$S$3",0))

(FYI: I know this wouldn't work as written, just giving it as an example.)

 

[pbornemeier]

http://www.mrexcel.com/forum/showthread.php?510342 mentions the free MOREFUNC addin which (among other things) provides more flexibility in retrieving data from closed files. It is currently hosted at: http://download.cnet.com/Morefunc/3000-2077_4-10423159.html Perhaps that could help.

If you do want to try a code option, links in this post discuss it: http://www.mrexcel.com/forum/showthread.php?617673

This is an interesting option. I don't think it will work in your case because it is slow and the amount of times you would need to use it:
http://numbermonger.com/2012/02/11/excel-pull-function-creating-dynamic-links-to-closed-workbooks/

Sorry I could not be more help. If you want to try a VBA solution, I can give it a shot.

 

[pbornemeier]

http://www.mrexcel.com/forum/showthread.php?510342 mentions the free MOREFUNC addin which (among other things) provides more flexibility in retrieving data from closed files. It is currently hosted at: http://download.cnet.com/Morefunc/3000-2077_4-10423159.html Perhaps that could help.

If you do want to try a code option, links in this post discuss it: http://www.mrexcel.com/forum/showthread.php?617673

This is an interesting option. I don't think it will work in your case because it is slow and the amount of times you would need to use it:
http://numbermonger.com/2012/02/11/excel-pull-function-creating-dynamic-links-to-closed-workbooks/

Sorry I could not be more help. If you want to try a VBA solution, I can give it a shot.