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
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
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:
- How I can find string in excel with vba?Best solution by Stack Overflow
- How to count number of occurrences of pattern within a string?Best solution by Stack Overflow
- How to replace a string inside of a Word Document?Best solution by Stack Overflow
- How can I convert a string number to a number in Perl?Best solution by Stack Overflow
- What is the symbolic meaning of A Mockingbird in "To Kill A Mockingbird?Best solution by English Language and Usage
Just Added Q & A:
- How many active mobile subscribers are there in China?Best solution by Quora
- How to find the right vacation?Best solution by bookit.com
- How To Make Your Own Primer?Best solution by thekrazycouponlady.com
- How do you get the domain & range?Best solution by ChaCha
- How do you open pop up blockers?Best solution by Yahoo! Answers
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.