Excel Indirect Gives Ref Error

be down. Please try the request again. Your cache administrator is webmaster. Generated Thu, 13 Oct 2016 19:42:40 GMT by s_ac5 (squid/3.5.20)

here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about a1-style reference hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges

The Source Currently Evaluates To An Error Data Validation Indirect

Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each excel indirect closed workbook other. Join them; it only takes a minute: Sign up Excel INDIRECT returns with REF error up vote -1 down vote favorite I don't understand why my INDIRECT function does not work. I'm dynamically changing D2 value from a http://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_other/indirect-function-ref-error/c3ea9877-1dad-45d2-a442-81c4b8b210ab list. =INDIRECT("'G:\Production\OPH 2015\"&D2&"'!"&"G4") where D2 = [Spreadsheet.xslx]Sheet1 G4 would be the value I want the formula to return with. The same formula on its own returns the value 72.2% and looks like this: ='G:\Production\OPH 2015\[Spreadsheet.xlsx]Sheet1'!G4 excel excel-indirect share|improve this question edited Nov 4 '15 at 14:25 asked Nov 4 '15 at 14:18 Steven 58110 Thanks, I've tried that already, same result unfortunately. –Steven Nov 4 '15 at 14:38 add a comment| 2 Answers 2 active oldest http://stackoverflow.com/questions/33524174/excel-indirect-returns-with-ref-error votes up vote 1 down vote accepted It isn't your formula that is incorrect, the INDIRECT formula will return #REF! if the workbook is closed. I haven't tried this, but I believe this add-in will solve your problem: http://www.ashishmathur.com/tag/indirect-ext/ share|improve this answer answered Nov 4 '15 at 14:56 mike7mike 301217 Thank you. Add-in isn't a solution, I would need it working elsewhere than my computer unfortunately. Would there be another way to reference closed spreadsheets dynamically? –Steven Nov 4 '15 at 15:03 Unfortunately not. Straight from the horse's mouth - support.microsoft.com/en-us/kb/151323 –mike7mike Nov 4 '15 at 15:05 add a comment| Did you find this question interesting? Try our newsletter Sign up for our newsletter and get our top new questions delivered to your inbox (see an example). Subscribed! Success! Please click the link in the confirmation email to activate your subscription. up vote 0 down vote Q: "I don't understand why my INDIRECT function does not work" A: From the MS Office Support Article for INDIRECT(): If ref_text refers to another workbook (an external reference), the other workbook must be open. If the source workbook is not open, INDIRECT returns the #REF! error value. share|improve this answer answered Nov 4 '15 at 15:04 Macro Man 11.9k21646 Thank you for your reply, guys also pointed that out. –Steven Nov 4 '15 at 15:14 add a comm

To: Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2016 for Mac, Excel for Mac 2011, Excel Online, Excel for iPad, Excel for iPhone, Excel for Android tablets, https://support.office.com/en-us/article/INDIRECT-function-474b3a3a-8a26-4f44-b491-92b6306fa261 Excel Starter, Excel Mobile, Excel for Android phones, Less Applies To: Excel https://www.ablebits.com/office-addins-blog/2015/02/10/excel-indirect-function/ 2016 , Excel 2013 , Excel 2010 , Excel 2007 , Excel 2016 for Mac , Excel for Mac 2011 , Excel Online , Excel for iPad , Excel for iPhone , Excel for Android tablets , Excel Starter , Excel Mobile , Excel for Android phones , excel indirect More... Which version do I have? More... This article describes the formula syntax and usage of the INDIRECT function in Microsoft Excel. Description Returns the reference specified by a text string. References are immediately evaluated to display their contents. Use INDIRECT when you want to change the reference to a cell within a formula without changing the formula itself. Syntax excel indirect gives INDIRECT(ref_text, [a1]) The INDIRECT function syntax has the following arguments: Ref_text    Required. A reference to a cell that contains an A1-style reference, an R1C1-style reference, a name defined as a reference, or a reference to a cell as a text string. If ref_text is not a valid cell reference, INDIRECT returns the #REF! error value. If ref_text refers to another workbook (an external reference), the other workbook must be open. If the source workbook is not open, INDIRECT returns the #REF! error value.Note    External references are not supported in Excel Web App. If ref_text refers to a cell range outside the row limit of 1,048,576 or the column limit of 16,384 (XFD), INDIRECT returns a #REF! error.Note    This behavior is different from Excel versions earlier than Microsoft Office Excel 2007, which ignore the exceeded limit and return a value. A1    Optional. A logical value that specifies what type of reference is contained in the cell ref_text. If a1 is TRUE or omitted, ref_text is interpreted as an A1-style reference. If a1 is FALSE, ref_text is interpreted as an R1C1-style

that demonstrate how to use INDIRECT in Excel. A great lot of functions exist in Microsoft Excel, some being easy-to-understand, other requiring a long learning curve, and the former being used more often than the latter. And yet, Excel INDIRECT is one of the kind. This Excel function does not perform any calculations, nor does it evaluate any conditions or logical tests. Well then, what is the INDIRECT function in Excel and what do I use it for? This is a very good question and hopefully you will get a comprehensive answer in a few minutes when you've finished reading this tutorial. Excel INDIRECT function - syntax and basic uses Excel INDIRECT formula examples Creating indirect references from cell values References from cell values and text INDIRECT with named ranges Creating an Excel dynamic reference to another sheet Making a dynamic reference to another workbook Locking a cell reference INDIRECT with other functions (ROW, ADDRESS, VLOOKUP) INDIRECT & Excel Data Validation Possible errors and issues in INDIRECT formulas Excel INDIRECT function - syntax and basic uses As its name suggests, Excel INDIRECT is used to indirectly reference cells, ranges, other sheets or workbooks. In other words, the INDIRECT function lets you create an Excel dynamic cell reference or range reference rather than "hard coding" those references into your Excel formulas. As a result, you can change a cell reference within a formula without changing the formula itself. Moreover, these indirect references won't change when some new rows or columns are inserted in the worksheet or when you delete any existing ones. All this may be easier to understand from an example. However, to be able to write a formula, even the simplest one, you need to know the function's arguments, right? So, let's have a quick look at Excel INDIRECT syntax first. INDIRECT function arguments The INDIRECT function has only two arguments, the first is obligatory while the second is optional: INDIR