In the Edit Links dialog, select all workbooks listed. =INDEX(‘C:\Reports\ActGL.xls’!DataMonth,$B11,E$10)Ĭhoose Edit, Links. When you have this problem, you’ll usually see a path name within the INDEX function, something like this: One reason could be that the reference is to a workbook that isn’t open. There are several likely reasons for this. If Excel gives you an error when you try to go to INDEX(whatever), you know that your INDEX isn’t returning a good reference. Press Ctrl+V to paste the copied text into the Reference edit box in the Goto dialogĪfter you successfully test the reference, you can press F5 then Enter to return to the original location in your workbook.Press Ctrl+C to copy that piece to your clipboard.Select the INDEX(whatever) piece of the formula in your formula bar.Suppose you have a formula that includes: INDEX(whatever) If you want to see where the INDEX is referencing, here’s what you do: If you have a reference in a formula, the easiest way I’ve found to test it is to tell Excel to go to the range that the formula is referencing. Testing Formula ReferencesĪn Excel reference is like a hyperlink to a range in an Excel worksheet. So let’s take a look at how to debug the references and numbers. INDEX’s next two arguments are numbers, which you can test.INDEX’s first argument must be a reference, which you can test.INDEX returns a reference, which you can test.INDEX often is an easier function to debug than most other functions. Most of the time, that is, the function is used like this: And usually, there’s only one area, which allows us to ignore the area_num argument. =INDEX(reference, row_num, column_num, area_num) The INDEX function can use either of two sets of arguments: Probably the best way to answer this question is to explain how to debug your INDEX formulas. Why does = INDEX(…) sometimes work and sometimes not….
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |