Find solution
How can I evaluate an equation within a String?

Evaluate the meaning of a string in Excel.

  • How do I store a string in one cell in Excel and interpret it as a (or for a) function in another cell? More info inside. Simplified example: Lets say I have random numbers in A1 through A15. In B1 I have the string "A1:A15". In B2, I want it to perform the MAX() function on whatever range is specified in B1. So if I change the string is B1 to something else, say "A10:A15", the maximum calculated in B2 is also reprocessed for the different set of data. For some reason, I thought it would be something along the lines of: =MAX(EVAL(B1)) But that is not the case. I seem to remember doing something similar before. Thanks for your input!

  • Answer:

    I believe you want INDIRECT().

nickerbocker at Ask.Metafilter.Com Visit the source

Was this solution helpful to you?

Other answers

Hi INDIRECT() worked, but when I changed the value in my reference cell it didn't automatically change the cell with the INDIRECT() function in it. I had to select it and press enter to get a new result. Anyway around this?

nickerbocker

I_pity_the_fool

Hmm, yeah. If you create a function in Excel like http://www.freevbcode.com/ShowCode.asp?ID=1048 (you'll have to press Alt+F11, Insert - Module and paste his code in and then Debug and Compile VBA project), you'll have a function called ExcelEval which does what you want. MeMail me if you have trouble. Remember to save it in a macro-enabled workbook if you're using Excel 2007. It performs horribly though. http://www.rharris.ukfsn.org/eval.xlsm

I_pity_the_fool

Sorry. Misread your question. :(

I_pity_the_fool

Hi INDIRECT() worked, but when I changed the value in my reference cell it didn't automatically change the cell with the INDIRECT() function in it. I had to select it and press enter to get a new result. Anyway around this? Are you saying you want to edit the cell B1 and have the results in change in cell B2 as you're editing, before you press Enter? I don't think Excel really considers that the cell's value has changed until you press Enter.

mhum

No. I think I someone disabled auto-calculations in Excel. I want to change the value in B1 and once I hit enter B2 updates. Right now, I have to go into B2 and press enter again, to get it to recalculate if there is a change in B1. Or press F9 to recalculate the whole spreadsheet. Right now, I have to press F9 for anything I do and I'm not sure how to fix that. Any ideas? Thanks!

nickerbocker

Oh, I see. You've definitely got auto-calculate turned off. In Excel 2007, click on the round Windows logo in the upper-left and click on "Excel Options" at the very bottom. Click "Formulas" from the left-hand menu. Under "Workbook Calculation", pick "Automatic".

mhum

In Excel 2003, I think the path is through Tools->Options.

mhum

mhum: That's correct. Tools > Options > Calculation (tab) then make sure Calculation is set to Automatic there instead of Manual.

odinsdream

Related Q & A:

Just Added Q & A:

Find solution

For every problem there is a solution! Proved by Solucija.

  • Got an issue and looking for advice?

  • Ask Solucija to search every corner of the Web for help.

  • Get workable solutions and helpful tips in a moment.

Just ask Solucija about an issue you face and immediately get a list of ready solutions, answers and tips from other Internet users. We always provide the most suitable and complete answer to your question at the top, along with a few good alternatives below.