https://www.betaarchive.com/wiki/index.php?title=Microsoft_KB_Archive/102918&feed=atom&action=history
Microsoft KB Archive/102918 - Revision history
2024-03-29T07:49:26Z
Revision history for this page on the wiki
MediaWiki 1.39.3
https://www.betaarchive.com/wiki/index.php?title=Microsoft_KB_Archive/102918&diff=349015&oldid=prev
X010: Text replacement - "&" to "&"
2020-07-21T11:24:40Z
<p>Text replacement - "&" to "&"</p>
<table style="background-color: #fff; color: #202122;" data-mw="interface">
<col class="diff-marker" />
<col class="diff-content" />
<col class="diff-marker" />
<col class="diff-content" />
<tr class="diff-title" lang="en-GB">
<td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">← Older revision</td>
<td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">Revision as of 11:24, 21 July 2020</td>
</tr><tr><td colspan="2" class="diff-lineno" id="mw-diff-left-l27">Line 27:</td>
<td colspan="2" class="diff-lineno">Line 27:</td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>you can use this formula:</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>you can use this formula:</div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td></tr>
<tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div><pre> =SUM(INDIRECT(A1&<del style="font-weight: bold; text-decoration: none;">amp;</del>"Sheet1"&<del style="font-weight: bold; text-decoration: none;">amp;</del>B1),INDIRECT(A1&<del style="font-weight: bold; text-decoration: none;">amp;</del>"Sheet2"&<del style="font-weight: bold; text-decoration: none;">amp;</del>B1)</div></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><pre> =SUM(INDIRECT(A1&"Sheet1"&B1),INDIRECT(A1&"Sheet2"&B1)</div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div></pre></div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div></pre></div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>== MORE INFORMATION ==</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>== MORE INFORMATION ==</div></td></tr>
</table>
X010
https://www.betaarchive.com/wiki/index.php?title=Microsoft_KB_Archive/102918&diff=262404&oldid=prev
X010: Text replacement - """ to """
2020-07-20T08:27:37Z
<p>Text replacement - """ to """</p>
<table style="background-color: #fff; color: #202122;" data-mw="interface">
<col class="diff-marker" />
<col class="diff-content" />
<col class="diff-marker" />
<col class="diff-content" />
<tr class="diff-title" lang="en-GB">
<td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">← Older revision</td>
<td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">Revision as of 08:27, 20 July 2020</td>
</tr><tr><td colspan="2" class="diff-lineno" id="mw-diff-left-l15">Line 15:</td>
<td colspan="2" class="diff-lineno">Line 15:</td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>The INDIRECT() function in Microsoft Excel returns a #REF! error value when the ref_text argument contains a 3-D reference, as in the following example:</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>The INDIRECT() function in Microsoft Excel returns a #REF! error value when the ref_text argument contains a 3-D reference, as in the following example:</div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td></tr>
<tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div><pre> =SUM(INDIRECT(<del style="font-weight: bold; text-decoration: none;">&quot;</del>[BOOK1]Sheet1:Sheet2!A1<del style="font-weight: bold; text-decoration: none;">&quot;</del>))</div></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><pre> =SUM(INDIRECT(<ins style="font-weight: bold; text-decoration: none;">"</ins>[BOOK1]Sheet1:Sheet2!A1<ins style="font-weight: bold; text-decoration: none;">"</ins>))</div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div></pre></div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div></pre></div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>If the ref_text argument contains a single bound or unbound sheet name, it will be evaluated correctly. To use INDIRECT() to evaluate multiple sheet references, break up the sheet references as follows:</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>If the ref_text argument contains a single bound or unbound sheet name, it will be evaluated correctly. To use INDIRECT() to evaluate multiple sheet references, break up the sheet references as follows:</div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td></tr>
<tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div><pre> =SUM(INDIRECT(<del style="font-weight: bold; text-decoration: none;">&quot;</del>[BOOK1]Sheet1!A1<del style="font-weight: bold; text-decoration: none;">&quot;</del>),INDIRECT(<del style="font-weight: bold; text-decoration: none;">&quot;</del>[BOOK1]Sheet2!A1<del style="font-weight: bold; text-decoration: none;">&quot;</del>))</div></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><pre> =SUM(INDIRECT(<ins style="font-weight: bold; text-decoration: none;">"</ins>[BOOK1]Sheet1!A1<ins style="font-weight: bold; text-decoration: none;">"</ins>),INDIRECT(<ins style="font-weight: bold; text-decoration: none;">"</ins>[BOOK1]Sheet2!A1<ins style="font-weight: bold; text-decoration: none;">"</ins>))</div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div></pre></div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div></pre></div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>TIP: You can store the workbook name and the cell reference or range in separate cells and concatenate these cells with the sheet names. This technique is especially useful if you have more than a few sheets. For example, if you have the following on the sheet in which you're entering the INDIRECT() function</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>TIP: You can store the workbook name and the cell reference or range in separate cells and concatenate these cells with the sheet names. This technique is especially useful if you have more than a few sheets. For example, if you have the following on the sheet in which you're entering the INDIRECT() function</div></td></tr>
<tr><td colspan="2" class="diff-lineno" id="mw-diff-left-l27">Line 27:</td>
<td colspan="2" class="diff-lineno">Line 27:</td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>you can use this formula:</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>you can use this formula:</div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td></tr>
<tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div><pre> =SUM(INDIRECT(A1&amp;<del style="font-weight: bold; text-decoration: none;">&quot;</del>Sheet1<del style="font-weight: bold; text-decoration: none;">&quot;</del>&amp;B1),INDIRECT(A1&amp;<del style="font-weight: bold; text-decoration: none;">&quot;</del>Sheet2<del style="font-weight: bold; text-decoration: none;">&quot;</del>&amp;B1)</div></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><pre> =SUM(INDIRECT(A1&amp;<ins style="font-weight: bold; text-decoration: none;">"</ins>Sheet1<ins style="font-weight: bold; text-decoration: none;">"</ins>&amp;B1),INDIRECT(A1&amp;<ins style="font-weight: bold; text-decoration: none;">"</ins>Sheet2<ins style="font-weight: bold; text-decoration: none;">"</ins>&amp;B1)</div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div></pre></div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div></pre></div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>== MORE INFORMATION ==</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>== MORE INFORMATION ==</div></td></tr>
<tr><td colspan="2" class="diff-lineno" id="mw-diff-left-l33">Line 33:</td>
<td colspan="2" class="diff-lineno">Line 33:</td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>The INDIRECT() function is used to return a value in a cell that is indicated by the ref_text argument. This is either a reference to a cell containing a reference or a reference expressed as a text string. This reference can be an external reference; however, if it is an external reference, the external sheet must be open for the function to be evaluated correctly (otherwise it returns a #REF! error value).</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>The INDIRECT() function is used to return a value in a cell that is indicated by the ref_text argument. This is either a reference to a cell containing a reference or a reference expressed as a text string. This reference can be an external reference; however, if it is an external reference, the external sheet must be open for the function to be evaluated correctly (otherwise it returns a #REF! error value).</div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td></tr>
<tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div>INDIRECT() is useful when you have portions of a reference in separate cells, for example, a column letter in one cell and a row number in another. When you concatenate the two with the INDIRECT() function, it evaluates the cell reference and returns the value in the indicated cell. It is also useful for keeping a reference static. When the ref_text argument is a text string such as <del style="font-weight: bold; text-decoration: none;">&quot;</del>Sheet1!A1<del style="font-weight: bold; text-decoration: none;">&quot;</del>, INDIRECT() won't be updated if you move or delete Sheet1!A1; instead, it will return the new value in that cell.</div></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div>INDIRECT() is useful when you have portions of a reference in separate cells, for example, a column letter in one cell and a row number in another. When you concatenate the two with the INDIRECT() function, it evaluates the cell reference and returns the value in the indicated cell. It is also useful for keeping a reference static. When the ref_text argument is a text string such as <ins style="font-weight: bold; text-decoration: none;">"</ins>Sheet1!A1<ins style="font-weight: bold; text-decoration: none;">"</ins>, INDIRECT() won't be updated if you move or delete Sheet1!A1; instead, it will return the new value in that cell.</div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>== REFERENCES ==</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>== REFERENCES ==</div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td></tr>
<tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div><del style="font-weight: bold; text-decoration: none;">&quot;</del>Online Help,<del style="font-weight: bold; text-decoration: none;">&quot; </del>version 5.0 <del style="font-weight: bold; text-decoration: none;">&quot;</del>Function Reference,<del style="font-weight: bold; text-decoration: none;">&quot; </del>version 4.0, page 238-239</div></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">"</ins>Online Help,<ins style="font-weight: bold; text-decoration: none;">" </ins>version 5.0 <ins style="font-weight: bold; text-decoration: none;">"</ins>Function Reference,<ins style="font-weight: bold; text-decoration: none;">" </ins>version 4.0, page 238-239</div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>{|</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>{|</div></td></tr>
<tr><td colspan="2" class="diff-lineno" id="mw-diff-left-l51">Line 51:</td>
<td colspan="2" class="diff-lineno">Line 51:</td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div><br /></div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div><br /></div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div><br /></div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div><br /></div></td></tr>
<tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div>THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED <del style="font-weight: bold; text-decoration: none;">&quot;</del>AS IS<del style="font-weight: bold; text-decoration: none;">&quot; </del>WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.</div></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div>THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED <ins style="font-weight: bold; text-decoration: none;">"</ins>AS IS<ins style="font-weight: bold; text-decoration: none;">" </ins>WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.</div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div></blockquote></div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div></blockquote></div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div><span>Last reviewed: September 12, 1996</span><br /></div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div><span>Last reviewed: September 12, 1996</span><br /></div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>[[../cpyright|©1997 Microsoft Corporation. All rights reserved. Legal Notices]].<br /></div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>[[../cpyright|©1997 Microsoft Corporation. All rights reserved. Legal Notices]].<br /></div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>|}</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>|}</div></td></tr>
</table>
X010
https://www.betaarchive.com/wiki/index.php?title=Microsoft_KB_Archive/102918&diff=47408&oldid=prev
3155ffGd: importing KB archive
2020-07-18T15:02:56Z
<p>importing KB archive</p>
<p><b>New page</b></p><div>= Microsoft Knowledge Base =<br />
<br />
== Excel: INDIRECT() Returns #REF! with 3-D Reference ==<br />
<br />
<span>Last reviewed: September 12, 1996<br /><br />
Article ID: Q102918</span><br />
<br />
The information in this article applies to:<br />
<br />
* Microsoft Excel for Windows, versions 4.0, 4.0a, 5.0<br />
* Microsoft Excel for the Macintosh, version 4.0<br />
<br />
== SUMMARY ==<br />
<br />
The INDIRECT() function in Microsoft Excel returns a #REF! error value when the ref_text argument contains a 3-D reference, as in the following example:<br />
<br />
<pre> =SUM(INDIRECT(&quot;[BOOK1]Sheet1:Sheet2!A1&quot;))<br />
</pre><br />
If the ref_text argument contains a single bound or unbound sheet name, it will be evaluated correctly. To use INDIRECT() to evaluate multiple sheet references, break up the sheet references as follows:<br />
<br />
<pre> =SUM(INDIRECT(&quot;[BOOK1]Sheet1!A1&quot;),INDIRECT(&quot;[BOOK1]Sheet2!A1&quot;))<br />
</pre><br />
TIP: You can store the workbook name and the cell reference or range in separate cells and concatenate these cells with the sheet names. This technique is especially useful if you have more than a few sheets. For example, if you have the following on the sheet in which you're entering the INDIRECT() function<br />
<br />
<pre> A1: [BOOK1] B1: !A1<br />
</pre><br />
you can use this formula:<br />
<br />
<pre> =SUM(INDIRECT(A1&amp;&quot;Sheet1&quot;&amp;B1),INDIRECT(A1&amp;&quot;Sheet2&quot;&amp;B1)<br />
</pre><br />
== MORE INFORMATION ==<br />
<br />
The INDIRECT() function is used to return a value in a cell that is indicated by the ref_text argument. This is either a reference to a cell containing a reference or a reference expressed as a text string. This reference can be an external reference; however, if it is an external reference, the external sheet must be open for the function to be evaluated correctly (otherwise it returns a #REF! error value).<br />
<br />
INDIRECT() is useful when you have portions of a reference in separate cells, for example, a column letter in one cell and a row number in another. When you concatenate the two with the INDIRECT() function, it evaluates the cell reference and returns the value in the indicated cell. It is also useful for keeping a reference static. When the ref_text argument is a text string such as &quot;Sheet1!A1&quot;, INDIRECT() won't be updated if you move or delete Sheet1!A1; instead, it will return the new value in that cell.<br />
<br />
== REFERENCES ==<br />
<br />
&quot;Online Help,&quot; version 5.0 &quot;Function Reference,&quot; version 4.0, page 238-239<br />
<br />
{|<br />
|width="100%"|<br />
<br />
-----<br />
<br />
<blockquote><span style="font-family:verdana,arial,helvetica; font-size:8pt"> KBCategory: kbusage<br /><br />
KBSubcategory: </span><br />
<br />
Additional reference words: 4.0 4.00 4.0a 4.00a 5.0 5.00<br />
<br />
<br /><br />
<br /><br />
THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED &quot;AS IS&quot; WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.<br />
</blockquote><br />
<span>Last reviewed: September 12, 1996</span><br /><br />
[[../cpyright|©1997 Microsoft Corporation. All rights reserved. Legal Notices]].<br /><br />
|}</div>
3155ffGd