Results 1 to 7 of 7 Thread: #REF! If I try to automate this using B3=INDEX(INDIRECT("'\\SERVER\Path\To\File\[Workbook.xlsx]Sheet'!$A$"&B2&":$SD$"&B2), MATCH($A4,'\\SERVER\Path\To\File\[Workbook.xlsx]Sheet1'!$A$2:$SD$2,0)), I get a #REF error. so I want to use cell A1 to type a date (Nov 09 in this case) and have cell V21 go to 'I:\DOD\Intervals\auto\[Nov 09.xlsx]Daily DOD'!$F21 and give me that data. Join them; it only takes a minute: Sign up INDIRECT inside INDEX function giving #REF error up vote 1 down vote favorite I'm have an excel workbook Spreadsheet.xlsx in which B2=MATCH(B1,'\\SERVER\Path\To\File\[Workbook.xlsx]Sheet1'!$A$1:$A$500,0)

However, the problem I'm having is that when I enter "Other" I want the cell in Column B to able to input my own information. in my example, the data is shown in cell V21 in my 'daily data' spreadsheet. Compare Excel | Excel Templates | DownloaderXL Pro Finance Templates & Add-In Bundle | NeuroXL Predictor | Construction Estimating Merge Excel The Easy Way | Trading Add-ins For Excel Dik Reply Tina says: June 9, 2016 at 9:22 pm Ablebits.com i love you too much in-fact you've made me like and understand excel so much thank u.

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 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 The second formula in cell H31 is =INDIRECT(ADDRESS(H11,H12)). This idea can be extended: the value indirectly referred to does not need to be in the same worksheet (or even workbook) as follows.

In the following example, the formula: =INDIRECT("B"&C2) returns a value from cell B2 based on the following logical chain: The INDIRECT function concatenates the elements in the ref_text argument - text I have an issue and I'd be so thankful if you could help me. INDIRECT however is a volatile function, i.e. Now I have issue where formulas turn to reference if rows are deleted after Table Merge in tab that is reference.

Please try the request again. asked 3 years ago viewed 15437 times active 3 years ago Related 0Automatically keeping two excel data tables in-sync (w/out VBA)0Excel error “This workbook contains Excel 4.0 macros or Excel 5.0 Reply Svetlana Cheusheva says: February 28, 2015 at 10:44 am Hello Mike, I am sorry it's hard to determine the cause of the problem without seeing your data. Also, yes it does work when Workbook.xlsx is open alongside, and then doesn't when it is closed.

Eagle-eyed readers may note that worksheet names without spaces do not need apostrophes. To illustrate the difference, please do the following: Enter any value in any cell, say, number 20 in cell A1. Reply Tal mizo says: May 19, 2015 at 12:58 pm i want to indirect to another sheet but show the rows that have specific criteria Reply sandy says: June 3, 2015 Does the recent news of "ten times more galaxies" imply that there is correspondingly less dark matter?

error value. [a1] This is optional (hence the square brackets) and represents a logical value that specifies what type of reference is contained in the cell ref_text. As the result, you get the following formula: INDIRECT("'" & $A$1 & "'!" & B1) Also, please pay attention that if you are copying the formula into multiple cells, you have If no number comes after the letter, then you are referring to the same row or column. Typo normally. #VALUE!

UPDATE heap table -> Deadlocks on RID Which option did Harry Potter pick for the knight bus? B3 indexes a range of values $A$8:$SD$8 corresponding to the row number from B2 in Sheet1 in Workbook.xlsx, and returns the values matched to a date $A4 in the range $A$2:$SD$2. However, using the R1C1 (row / column approach where the third row fourth column would be (3,4)) has benefits too. Using this formula will maintain the integrity of the referencing as required (please try it for yourself in the attached Example file).

INDIRECT and ROW functions Quite often, the ROW function is used in Excel to return an array of values. When this method is used, I often use INDIRECT in conjunction with the ADDRESS function. And here are a few more formula examples that may prove useful: VLOOKUP and INDIRECT - how to dynamically pull data from different sheets. The reference is to cell B2, which contains the value 1.333. 1.333 '=INDIRECT(A3) Value of the reference in cell A3.

There are three key issues with INDIRECT: INDIRECT encourages the use of hard code in formulae. How do computers remember where they store things? Not the answer you're looking for? 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

Reply Svetlana Cheusheva says: June 3, 2015 at 9:47 am Hi Sandy, Of course, it's CTRL+SHIFT+ENTER. Any help would be appreciated. error with INDIRECT formula Excel Training / Excel Dashboards Reports If this is your first visit, be sure to check out the FAQ by clicking the link above. The solution is to use the INDIRECT function, like this: =SUM(INDIRECT("A2:A5")) Since Excel perceives "A1:A5" as a mere text string rather than a range reference, it won't make any changes when

If I put an equal sign in front of that ='[Longterm Budget.xlsx]2012'!$F$21 ...it returns the desired reference. As a result, when copying a formula between two different Excel locales, you may get the error message "We found a problem with this formula…" because the List separator used in Reply Post a comment Click here to cancel reply. This is the reason why Excel users rarely consider using R1C1 as an alternative referencing approach.

In the F1 cell, is where I would like for this value to appear. Is it possible to have a planet unsuitable for agriculture? "Rollbacked" or "rolled back" the edit? The point is, however, the value in cell H11 can be changed so that the formula suddenly links to a completely different worksheet. Let says your sheet name is "XYZ".

Near Earth vs Newtonian gravitational potential How to calculate time to empty EvenSt-ring C ode - g ol!f What kind of bicycle clamps are these? This Excel function does not perform any calculations, nor does it evaluate any conditions or logical tests. Latest attempt: =INDIRECT("'"&A1&"'!"D10) Reply haneefa says: April 25, 2015 at 4:51 am i have a doubt in ms excel. 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

Digital Diversity Tell company that I went to interview but interviewer did not respect start time How do I help minimize interruptions during group meetings as a student? How do you say "root beer"? error. 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.

To prevent this from happening, nest INDIRECT in the ROW function and your array formula will always remain correct, no matter how many rows are inserted or deleted: =AVERAGE(SMALL(A1:A10,ROW(INDIRECT("1:3")))) Here's a If your Indirect formula refers to another Excel workbook or worksheet, that other workbook / spreadsheet must be open, otherwise INDIRECT returns the #REF! Use INDIRECT when you want to change the reference to a cell within a formula without changing the formula itself. IN any sheet put A1- XYZ Then use indirect like INDIRECT(A1&"!"&"a2:b3")....

Is there any job that can't be automated? This formula is difficult to read; do note the use of the vital apostrophes (‘). would you please help me to do it. I have seen all sorts of weird and wonderful formulae to perform this common requirement, but INDIRECT is by far one of the simplest approaches available.

and #VALUER error with INDIRECT formula Dave, Ok No problem Natalie Excel Video Tutorials / Excel Dashboards Reports Reply With Quote Quick Navigation Excel General Top Site Areas Settings Private Messages Here is what I have so far: =MATCH(TRUE,INDIRECT($A3&"!'1'!F1:F4000">7.5,0), Any help would be greatly appreciated excel function excel-formula excel-indirect share|improve this question edited Sep 3 '13 at 9:00 hjpotter92 44.4k146191 asked Aug Is there any alternative to the "sed -i" command in Solaris?