Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #11   Report Post  
Old July 21st 11, 02:11 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2011
Posts: 587
Default Changing function name in formula

hi,


Le 2011-07-21 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   Report Post  
Old July 22nd 11, 12:51 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 162
Default 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   Report Post  
Old July 22nd 11, 01:56 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2011
Posts: 587
Default 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   Report Post  
Old July 23rd 11, 12:01 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 162
Default 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   Report Post  
Old July 23rd 11, 02:22 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2011
Posts: 587
Default 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   Report Post  
Old July 23rd 11, 09:52 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 162
Default 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 hyper-threading could
be useful.

Thanks for the in-depth support.



- Ronald K.
  #17   Report Post  
Old July 24th 11, 05:53 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2011
Posts: 587
Default 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   Report Post  
Old July 24th 11, 08:40 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 162
Default Changing function name in formula

Isabelle,

Thanks a bunch.



- Ronald K.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Changing the range for averages with out changing the formula. JessLRC Excel Worksheet Functions 0 April 20th 10 03:10 PM
Worksheet Function Keeps Changing, need help Jay Excel Worksheet Functions 2 March 18th 08 02:11 PM
NOW() function that is un-changing Cheese Excel Discussion (Misc queries) 7 March 19th 07 12:51 AM
Changing other fields using IF Function!? Neo1 Excel Worksheet Functions 4 January 17th 06 09:09 AM
Can I use TODAY Function in formula without it changing the next . Tucson Guy Excel Discussion (Misc queries) 1 December 19th 04 09:47 AM


All times are GMT +1. The time now is 11:54 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright ©2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017