Referencing value in a closed Excel workbook using INDIRECT?

by

There is definitively no way to do this with standard formulas. However, a crazy sort of answer can be found here. It still avoids VBA, and it will allow you to get your result dynamically.

  1. First, make the formula that will generate your formula, but don’t add the = at the beginning!

  2. Let us pretend that you have created this formula in cell B2 of Sheet1, and you would like the formula to be evaluated in column c.

  3. Now, go to the Formulas tab, and choose “Define Name”. Give it the name myResult (or whatever you choose), and under Refers To, write =evaluate(Sheet1!$B2) (note the $)

  4. Finally, go to C2, and write =myResult. Drag down, and… voila!

More Related Contents:

  1. Want to concatenate multiple row data in in cell along with its values if present. Emp Id should not come if no data available infromt of it
  2. How to convert to percentage [closed]
  3. Excel Number Separation [closed]
  4. I have an Excel data sheet that I would like to extract data from into a new sheet
  5. Excel VBA – How to add dynamic array formula
  6. Getting unique values in Excel by using formulas only
  7. Return empty cell from formula in Excel
  8. Excel: last character/string match in a string
  9. Simple Pivot Table to Count Unique Values
  10. Difference between date and time w/out work week Excel
  11. How can I perform a reverse string search in Excel without using VBA?
  12. Excel UNIQUE Across Columns
  13. Filling any empty cells with the value above
  14. How to create a string or formula containing double quotes in Excel?
  15. Excel Formula for Inventory
  16. Remove leading or trailing spaces in an entire column of data
  17. Count Unique values with a condition
  18. Excel Date to String conversion
  19. Excel: Formulas for converting data among column / row / matrix
  20. Count unique values in Excel
  21. Excel: Searching for multiple terms in a cell
  22. Three Dimensional Lookup Using INDEX/MATCH
  23. How to list all possible combinations of the values in three columns in excel?
  24. Excel – extracting top 5 values
  25. How to split texts from dynamic range?
  26. Excel: Search for a list of strings within a particular string using array formulas?
  27. Convert from US to UK format
  28. How to roundup a number to the closest ten?
  29. Convert an Array Formula’s Text Results into a Usable Format
  30. Can’t pass a range to a lambda using Let and Makearray functions

Leave a Comment