2

there used to be a function called evaluate that would evaluate the contents of a cell. However, that function doesn't seem to exist any longer. This is the problem I am looking to solve (this is supposed to run in batch):

A1 contains a concatenate function that creates the string ='C:\users\example\[ref.xlsx]'Sheet!D5. This string is supposed to be a reference to a cell in another workbook. However, Excel considers the equal sign a string and therefore, doesn't evaulate the function.

I know the most common workarounds:

  1. Copy and special paste only the values to another cell, then replace "=" with "=" and the cell evaluates automatically (not an option, because requires manual processing and can't be used in batch)
  2. Write a macro that evaluates the cells (not an option, because the batch is not compatible with macro enabled workbooks)
  3. Create a UDF that evaluates the cell per evaluate macro (also requires macro enabled workbook, thus not an option)

Macros are not an option. Manual processes that can't be automated per function also are not an option. I somehow need excel to show me that contents of the reference cell. Thanks for your help!!!

Mpdegn

mpdegn
  • 51
  • 10
  • This is not a programming question,it is better suited to SuperUser – brettdj Feb 10 '13 at 01:35
  • Why would you just close my question? This is a great example of administration spam. Do you have nothing better to do? The scope of my question may as well have changed through the course of providing answers to include vba/macro programming. Your administrative efforts are counter productive. – mpdegn Feb 10 '13 at 12:34
  • Read my comment. The scope may as well have changed. What if I changed my mind and wanted to include macro programming now that there is no suitable answer to my question? Do I really need to post another question? And what if I wanted some more input? Writing complex formulas could be considered programming, too. Stop being so anal.... you're not doing anyone a favor. – mpdegn Feb 10 '13 at 13:01
  • Actually I was doing you a favour. You are more likely to receive an answer to this type of question at Super User. – brettdj Feb 10 '13 at 21:12
  • No problem. My close vote was for migration to Super User, I was a little surprised to see this simply closed instead - I can understand your frustration. – brettdj Feb 10 '13 at 22:02

1 Answers1

2

There's one old question related to the very same problem: formula referencing other workbooks.

Unfortunately, as per answers there, as well as info from Excel Help:

the only way to make INDIRECT function work for the external workbooks (and that's the only function that may parse calculated references) - is to have these workbooks opened.

So it seems that using pure Excel your task is not possible.

Community
  • 1
  • 1
Ksenia
  • 497
  • 5
  • 14
  • Ksenia, thanks for your response. Though you're right, I was hoping for a different answer. Keeping the workbook open is not an option. If we had to include some very light macro, where do you see it best fit? Creating a UDF that uses the Evaluate statement or creating a macro that opens the other workbook? – mpdegn Feb 10 '13 at 11:54
  • @mpdegn I'd recommend you creating UDF which opens the workbook in the background and takes the values from it. This question is closed, so consider to submit a new one with your efforts. Good luck! – Ksenia Feb 10 '13 at 17:12