Evaluate the meaning of a string in Excel.

October 22, 2009 12:24 PM

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!

posted by nickerbocker to computers & internet (10 comments total)

I believe you want INDIRECT().
posted by odinsdream at 12:32 PM on October 22, 2009

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?
posted by nickerbocker at 12:52 PM on October 22, 2009

Not sure it's possible without using VBScript
posted by I_pity_the_fool at 1:08 PM on October 22, 2009

Hmm, yeah. If you create a function in Excel like the one this guy did (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. Have a look.
posted by I_pity_the_fool at 1:34 PM on October 22, 2009

Sorry. Misread your question. :(
posted by I_pity_the_fool at 1:51 PM on October 22, 2009

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.
posted by mhum at 2:34 PM on October 22, 2009

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!
posted by nickerbocker at 2:39 PM on October 22, 2009

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".
posted by mhum at 2:52 PM on October 22, 2009

In Excel 2003, I think the path is through Tools->Options.
posted by mhum at 2:53 PM on October 22, 2009

mhum: That's correct. Tools > Options > Calculation (tab) then make sure Calculation is set to Automatic there instead of Manual.
posted by odinsdream at 10:42 AM on October 23, 2009