Using Match command with remote files

[shinydiamond]

I've been using the match command in my cell calculations where VLOOKUP
just won't work.

The problem is that if the external spreadsheet that I'm refering to is
not open, match returns a #VALUE error until I open the spreadsheet.

It seems as though it's trying to recalculate. VLOOKUP doesn't do
this.

I need to be able to email my spreadsheets off to people who don't have
access to the source file, so I need the values in these cells to be
frozen.

Is there a way to do this without doing a copy-paste values?? (so I
don't loose my Match formulas)?

It seems like this is an undocumented "feature" of the match command.

Thanks for any help

 

[Dave Peterson]

I _think_ this is it, but I don't have different versions of excel to test.

Do share the workbooks between different versions of excel?

I'm guessing that you developed in an earlier version and the recipients opened
the file with a newer version.

Excel will recalculate if it sees that the workbook just opened was created in
an earlier version.

=index() is a volatile function. This means it reevaluates when excel
recalculates. =vlookup() doesn't. So that _might_ explain the difference.

If you're all using the same version of excel, maybe you could start a new
workbook and copy all the information over and see if that helps. (Test it on a
smaller version, so you don't waste your time if I'm wrong.)

 

[ryanb.]

I have never had this issue and I do have external links in many of the
worksheets I use. I tried to recreate your problem, but my sheet worked
everytime (I emailed to my home account that does not have access to the
data file). I know this is not the excel answer you were looking for, but
have you considered distributing your reports as a .PDF? That is how I do
it because not all of our salesmen in the field have excel. PDF readers are
free and it makes it more difficult to change a report after it leaves your
desk. It also takes care of the formatting issues that vary from printer to
printer. Works great, and would take care of your problem.

Sorry I can not be of more help on this,

ryanb.

I _think_ this is it, but I don't have different versions of excel to test.

Do share the workbooks between different versions of excel?

I'm guessing that you developed in an earlier version and the recipients opened
the file with a newer version.

Excel will recalculate if it sees that the workbook just opened was created in
an earlier version.

=index() is a volatile function. This means it reevaluates when excel
recalculates. =vlookup() doesn't. So that _might_ explain the difference.

If you're all using the same version of excel, maybe you could start a new
workbook and copy all the information over and see if that helps. (Test it on a
smaller version, so you don't waste your time if I'm wrong.)

 

[Dave Peterson]

Or if you like the idea of just sending the data (no formulas), you could send a
copy of the workbook with the formulas removed.

Select all your sheets,
Select all the cells (ctrl-A)
Edit|copy
edit|pastespecial Values
ungroup the worksheets

Save it as a new name--so you don't lose the formulas for your own self.

I have never had this issue and I do have external links in many of the
worksheets I use. I tried to recreate your problem, but my sheet worked
everytime (I emailed to my home account that does not have access to the
data file). I know this is not the excel answer you were looking for, but
have you considered distributing your reports as a .PDF? That is how I do
it because not all of our salesmen in the field have excel. PDF readers are
free and it makes it more difficult to change a report after it leaves your
desk. It also takes care of the formatting issues that vary from printer to
printer. Works great, and would take care of your problem.

Sorry I can not be of more help on this,

ryanb.

 

[Dave Peterson]

You may want to look into giving your sales people without excel a copy of the
the excel viewer. It's also free and may make your life a little easier in some
situations.

http://office.microsoft.com/downloads/2000/xlviewer.aspx

I have never had this issue and I do have external links in many of the
worksheets I use. I tried to recreate your problem, but my sheet worked
everytime (I emailed to my home account that does not have access to the
data file). I know this is not the excel answer you were looking for, but
have you considered distributing your reports as a .PDF? That is how I do
it because not all of our salesmen in the field have excel. PDF readers are
free and it makes it more difficult to change a report after it leaves your
desk. It also takes care of the formatting issues that vary from printer to
printer. Works great, and would take care of your problem.

Sorry I can not be of more help on this,

ryanb.