Pulling Data from External Worksheets

[blucajun]

I'm setting up a master worksheet to pull data from various external
workbooks (some of which aren't yet populated). I am using the following
formula to obtain the external data:

='C:\Data Files\Brandi -SALES\Expenses\ExpenseReports\[DR01.xls]Revised
Expense Report'!D38

I've also copied the formula to adjacent cells to pull from any subsequent
files which would be named DR02, DR03, and DR04. The problem is, these files
aren't in the ExpenseReports folder yet (aren't created yet) and it's giving
me the #REF! error in the cell and errors in my total columns (obviously). I
should also mention that some of the files may not ever be created. DR01
would be the first expense report in the month, DR02 the second, etc. Some
months, there may be only one while other months there may be four.

With that said, is there any way to modify the forumla to say -go look in
the ExpenseReport folder for a worksheet named DR02 and if it's there, get
the data in cell D38; otherwise leave this cell blank (or hide the error
message).
I've tried using conditional formatting to change the font for =#REF! to
white but it didn't work (I presume because #REF! isn't considered "data" in
the cell). Anyway, my objective is to keep the spreadsheet from showing
errors all over the page while the data is auto populated throughout the
year. Is that even possible?

Any help is appreciated!

 

[Jim Thomlinson]

What you are wanting to do is VERY dangerous. Linking workbooks is
problematic at the best of times since people tend to move the books and or
change the format and structure of the books not realizing that the changes
cause errors in the linked workbook. Your only saving grace when that happens
is that you often get errors showing up in the links. Now you are asking to
hide the errors so that you will never know if the links are corrupted in
some way... I would be inclined to rethink the solution.

In short what you are doing will lead to reports that might be right... they
might be wrong... and you will never know for sure which it is.

 

[Ron de Bruin]

Maybe you can use this macro to create the links for you when you need the data
http://www.rondebruin.nl/summary2.htm

 

[blucajun]

Well, I do know what the potential problems are. However, because I will be
the only one using the files and know how it is set up (where the files have
to be stored and how they have to be named), the risk is minimal. I'm trying
to streamline the process of tracking a slew of sales reps' expenses
throughout the year. As it is now, I have to manually type the data from
each individual report into the master tracking spreadsheet. Not only is it
time consuming, but manual entry = potential for errors (I do make typos).

So, I've structured the expense report so that there will be no need for
modification (and it's also protected). Now I'd like to be able to save each
incoming expense report into the specified folder with a certain naming
format so that the tracking spreadsheet knows where to to get the data. This
will save me hours of manual entry.

I do understand and appreciate your words of caution against doing this.
Would there be any other (more appropriate) way to accomplish this task?

Thanks!

What you are wanting to do is VERY dangerous. Linking workbooks is
problematic at the best of times since people tend to move the books and or
change the format and structure of the books not realizing that the changes
cause errors in the linked workbook. Your only saving grace when that happens
is that you often get errors showing up in the links. Now you are asking to
hide the errors so that you will never know if the links are corrupted in
some way... I would be inclined to rethink the solution.

In short what you are doing will lead to reports that might be right... they
might be wrong... and you will never know for sure which it is.
--
HTH...

Jim Thomlinson

I'm setting up a master worksheet to pull data from various external
workbooks (some of which aren't yet populated). I am using the following
formula to obtain the external data:

='C:\Data Files\Brandi -SALES\Expenses\ExpenseReports\[DR01.xls]Revised
Expense Report'!D38

I've also copied the formula to adjacent cells to pull from any subsequent
files which would be named DR02, DR03, and DR04. The problem is, these files
aren't in the ExpenseReports folder yet (aren't created yet) and it's giving
me the #REF! error in the cell and errors in my total columns (obviously). I
should also mention that some of the files may not ever be created. DR01
would be the first expense report in the month, DR02 the second, etc. Some
months, there may be only one while other months there may be four.

With that said, is there any way to modify the forumla to say -go look in
the ExpenseReport folder for a worksheet named DR02 and if it's there, get
the data in cell D38; otherwise leave this cell blank (or hide the error
message).
I've tried using conditional formatting to change the font for =#REF! to
white but it didn't work (I presume because #REF! isn't considered "data" in
the cell). Anyway, my objective is to keep the spreadsheet from showing
errors all over the page while the data is auto populated throughout the
year. Is that even possible?

Any help is appreciated!

 

[Jim Thomlinson]

Take a look at the link Ron posted. If I was doing it I would be inclined to
set it up as a database but I understand that is not everyone's cup of tea.

The gist of my post is that there is an inherant risk in ignoring error
messages. When you ignore anticipated errors there is also the risk that you
will ignore errors that are not anticipted and you end up with invalid
results. I can not even remember the last time I used the IsError function in
a spreadsheet as it is just too catch all.
--
HTH...

Jim Thomlinson

Well, I do know what the potential problems are. However, because I will be
the only one using the files and know how it is set up (where the files have
to be stored and how they have to be named), the risk is minimal. I'm trying
to streamline the process of tracking a slew of sales reps' expenses
throughout the year. As it is now, I have to manually type the data from
each individual report into the master tracking spreadsheet. Not only is it
time consuming, but manual entry = potential for errors (I do make typos).

So, I've structured the expense report so that there will be no need for
modification (and it's also protected). Now I'd like to be able to save each
incoming expense report into the specified folder with a certain naming
format so that the tracking spreadsheet knows where to to get the data. This
will save me hours of manual entry.

I do understand and appreciate your words of caution against doing this.
Would there be any other (more appropriate) way to accomplish this task?

Thanks!

What you are wanting to do is VERY dangerous. Linking workbooks is
problematic at the best of times since people tend to move the books and or
change the format and structure of the books not realizing that the changes
cause errors in the linked workbook. Your only saving grace when that happens
is that you often get errors showing up in the links. Now you are asking to
hide the errors so that you will never know if the links are corrupted in
some way... I would be inclined to rethink the solution.

In short what you are doing will lead to reports that might be right... they
might be wrong... and you will never know for sure which it is.
--
HTH...

Jim Thomlinson

I'm setting up a master worksheet to pull data from various external
workbooks (some of which aren't yet populated). I am using the following
formula to obtain the external data:

='C:\Data Files\Brandi -SALES\Expenses\ExpenseReports\[DR01.xls]Revised
Expense Report'!D38

I've also copied the formula to adjacent cells to pull from any subsequent
files which would be named DR02, DR03, and DR04. The problem is, these files
aren't in the ExpenseReports folder yet (aren't created yet) and it's giving
me the #REF! error in the cell and errors in my total columns (obviously). I
should also mention that some of the files may not ever be created. DR01
would be the first expense report in the month, DR02 the second, etc. Some
months, there may be only one while other months there may be four.

With that said, is there any way to modify the forumla to say -go look in
the ExpenseReport folder for a worksheet named DR02 and if it's there, get
the data in cell D38; otherwise leave this cell blank (or hide the error
message).
I've tried using conditional formatting to change the font for =#REF! to
white but it didn't work (I presume because #REF! isn't considered "data" in
the cell). Anyway, my objective is to keep the spreadsheet from showing
errors all over the page while the data is auto populated throughout the
year. Is that even possible?

Any help is appreciated!