Emailing Excel file with OFFSET, INDEX, MATCH without VALUE#! error

[TomYC]

I recently replaced the VLOOKUP functions in a document that I share by email with colleagues, with OFFSET, INDEX & MATCH functions. The recipients informed me that their data contained VALUE#! errors. Of course when I have the source files open on my computer all the data is displayed. I could, and did, in this case, save a copy of the file and break the links, but I would rather be able to have users open the file, and perhaps choose not to update the links, but still get whatever data items I included.
What is the best procedure?
Tnx,
Tom

 

[SkipVought]

Hi,

Copy the cells containing the formulas and then Edit > Paste Special -- VALUES

Has NOTHING to do with the functions you mentioned. If the user opens the workbook and has calculation set to AUTOMATIC, then ANY function will try to calculate and any unresolved link will indicate an error, such as #VALUE.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[TomYC]

I was perhaps not clear in my original question.
When I email a copy of this file, containing only cell references to different worksheets, as well as VLOOKUP references to different worksheets, the file appears correct, and no updating is required. When I replaced some of the VLOOKUP functions with a combination of OFFSET,INDEX & MATCH functions, referencing the same worksheets, the values do not appear unless the referenced worksheet is open, which in the case of an emailed document, doesn't fly.
And while I could, after completing this report, do the select/copy all and paste special values operation, I would rather not have to; so I was wondering if certain cell referencing functions perform better in this circumstance!
Tom

 

[SkipVought]

As a practice, it is never a good idea to send a workbook with external unresolvable links.

It seems that then VLOOKUP attempts to update and you CANCEL, the original values remain.

However, OFFSET does not behave that way and returns a #VALUE error even if your CANCEL the attempted update.

Bottom line: DON'T.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}