Excel Indirect Function Ref Error
Contents |
be down. Please try the request again. Your cache administrator is webmaster. Generated Sat, 15 Oct 2016 09:50:54 GMT by s_wx1131 (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 excel indirect function named range Stack Overflow the company Business Learn more about hiring developers or posting ads with
Excel Indirect Function Closed Workbook
us Super User Questions Tags Users Badges Unanswered Ask Question _ Super User is a question and answer site for computer enthusiasts excel indirect function to reference cell on different sheet and power users. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top http://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_other/indirect-function-ref-error/c3ea9877-1dad-45d2-a442-81c4b8b210ab INDIRECT gives a #REF! error for reference to external workbook where making a direct reference with an equal sign works. Why? up vote 2 down vote favorite I have a reference to another workbook that looks like this: =INDIRECT(ADDRESS(21,6,1,1,"[Longterm Budget.xlsx]2012")) The result of that INDIRECT argument, ADDRESS(21,$A$1,1,1,"[Longterm Budget.xlsx]2012"), is '[Longterm Budget.xlsx]2012'!$F$21. If I put an equal sign in front of that ='[Longterm Budget.xlsx]2012'!$F$21 ...it returns the desired reference. However, passing that same http://superuser.com/questions/535871/indirect-gives-a-ref-error-for-reference-to-external-workbook-where-making-a-d argument to INDIRECT like this =INDIRECT("'[Longterm Budget.xlsx]2012'!$F$21") ...results in a reference error. Any idea why? microsoft-excel share|improve this question asked Jan 14 '13 at 8:35 todorojo 155128 Apparently it has something to do with whether I have the externally referenced workbook opened at the same time. I'm not sure why the INDIRECT operator needs both opened simultaneously when the "=" operator does not. –todorojo Jan 14 '13 at 8:39 Peter has explained why INDIRECT gives you an error.......but can you explain why you are using INDIRECT here? I'd say you only need it if you want the reference to the workbook to be dynamic...... –barry houdini Jan 14 '13 at 11:41 The other notebook is arranged in a way that a direct reference would take a lot of hand-coding. Instead, I use ADDRESS to calculate the reference. My actual address argument is a bit more complicated than I've presented it here, but since I managed to identify the problem as a difference between the "=" operator and the INDIRECT operator, I simplified a bit for clarity. –todorojo Jan 14 '13 at 18:14 1 OK but then you could probably use INDEX function instead of INDIRECT which should work with a closed workbook, i.e. =INDEX('[Longterm Budget.xl
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, Excel https://support.office.com/en-us/article/INDIRECT-function-474b3a3a-8a26-4f44-b491-92b6306fa261 Starter, Excel Mobile, Excel for Android phones, Less Applies To: Excel 2016 , Excel 2013 , Excel 2010 , Excel 2007 , Excel 2016 for Mac , Excel for Mac 2011 http://www.excelforum.com/showthread.php?t=898377 , Excel Online , Excel for iPad , Excel for iPhone , Excel for Android tablets , Excel Starter , Excel Mobile , Excel for Android phones , More... Which excel indirect 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 INDIRECT(ref_text, [a1]) The excel indirect function 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 reference. Example Copy the example dat
Forum Microsoft Office Application Help - Excel Help forum Excel Formulas & Functions [SOLVED] Indirect function giving #REF! error To get replies by our experts at nominal charges, follow this link to buy points and post your thread in our Commercial Services forum! Here is the FAQ for this forum. + Reply to Thread Results 1 to 8 of 8 Indirect function giving #REF! error Thread Tools Show Printable Version Subscribe to this Thread… Rate This Thread Current Rating Excellent Good Average Bad Terrible Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode 02-08-2013,05:41 AM #1 mqdias View Profile View Forum Posts Forum Contributor Join Date 12-26-2006 Posts 187 Indirect function giving #REF! error Hi, I'm using the INDIRECT fucntion in a cell, reffering to another where is written the following: Please Login or Register to view this content. As you can see, the text points to another file and to a specific cell named "COMPANY". The problem is that when the file "Data.xls" is opened, the INDIRECT function gives me the correct value, but when i close it, the cell gives ne #REF! error. Is there any way of the cell with the INDIRECT function gives the correct value all the time and not only when the reffered file is opened? Thanks! Register To Reply 02-08-2013,05:45 AM #2 dilipandey View Profile View Forum Posts Visit Homepage Forum Expert Join Date 12-05-2011 Location Dubai, UAE MS-Off Ver 1997 - 2013 Posts 8,193 Re: Indirect function giving #REF! error Hi mqdis, Indirect function does'nt work on closed workbooks... Regards, DILIPandey