Home 
Search 
Today's Posts 
#11




Changing function name in formula
hi,
Le 20110721 00:51, kittronald a écrit : how could I achieve the initial solution ? i prepared a small example http://cjoint.com/?AGvpgCTJdUT And how did I earn the title of a Hungarian aunt ? i dont know what "Hungarian aunt " is  isabelle 
#12




Changing function name in formula
Isabelle,
Thanks for the help. For some reason I couldn't open that file  could you post the example in this group ? As for "neni" (hungarian aunt)  http://en.wiktionary.org/wiki/n%C3%A9ni  Ronald K. 
#13




Changing function name in formula
hi,
sorry for that, in my country "neni" means (no, none, never) i participate in this group via Netscape, so can you give me the web address where i can post the file  isabelle 
#14




Changing function name in formula
Isabelle,
Thanks for going through all that effort. I finally opened your spreadsheet  just had a problem with AV. Basically, you did what I was doing. But then I realized that I needed to be able to take that formula and fill it to the right and down several cells. However, with what you and I came up with, the function parameters are text and will not change when filling across or down. For example, ... =EvaluateString($B$1&"(B1:B2)") when selected and filled to the right ... will still be =EvaluateString($B$1&"(B1:B2)"). However, in order to make this type of function replacement usable, filling the formula to the right would have to change to =EvaluateString($B$1&"(C1:C2)"), etc. At this point, I'm considering creating a macro that does the following: 1) Select the first cell that contains the generic formula 2) Find and Replace the function in the first cell with a different function selected in a ComboBox  like you did in your spreadsheet. 3) Fill the first cell to the end of the first row 4) Fill the first row down to the last row This appears to be faster than using a UDF to evaluate text to non text and a mass Search and Replace. Of course, there might be a better way ...  Ronald K. 
#15




Changing function name in formula
hi ,
if ranges structure is always the same, the formula could be: =EvaluateString(B1&"("&ADRESSE(LIGNE()4;COLONNE())&":"&ADRESSE(LIGNE()2;COLONNE())&")") this gives possibility to copy to the right or down  isabelle 
#16




Changing function name in formula
Isabelle,
Vous êtes Français ? I'll give it a try and see if there's a performance difference between your formula and creating a macro that changes the formula in one cell and copies it right and down. Just so you know what I'm working with, potentially the total number of formulas like that will likely exceed 6,000,000 ... on just one worksheet ! And that's just modifying the formula  it takes another 20 minutes to calculate. Now I know how an eight core processor with hyperthreading could be useful. Thanks for the indepth support.  Ronald K. 
#17




Changing function name in formula
oops i forgot the translation, i'm in french family and also a french PC, sorry for this oversight
=EvaluateString(B1&"("&ADDRESS(ROW()4,COLUMN())&":"&ADDRESS(ROW()2,COLUMN())&")") but i think a macro would be much more faster  isabelle 
#18




Changing function name in formula
Isabelle,
Thanks a bunch.  Ronald K. 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Changing the range for averages with out changing the formula.  Excel Worksheet Functions  
Worksheet Function Keeps Changing, need help  Excel Worksheet Functions  
NOW() function that is unchanging  Excel Discussion (Misc queries)  
Changing other fields using IF Function!?  Excel Worksheet Functions  
Can I use TODAY Function in formula without it changing the next .  Excel Discussion (Misc queries) 