Dynamic (?) formula
Hello all,
I do not know if dynamic is the right word because I did not find anything applicable when searching the newsgroup... In column A I have some data in form of numbers. In cell B1 I would like to have a formula. The formula should be dependent on what is entered in B2. For example if I enter 'sum' in B2, the B1 formula should look like '=sum(A:A)' If I then change B2 to 'count' then the B1 formula should be '=count(A:A)' The solution sould also work on something like worksheet names being part of a formula... Rgds, Thore |
It's not as easy as you'd think, and the best method
would be via VBA. In fact, I think Chip Pearson posted UDF that achieves what you're looking for. However, you could use: =SUBTOTAL(VLOOKUP(LOWER(B2), {"average",1;"count",2;"max",4;"min",5;"sum",9},2, 0),INDIR ECT(C2)) where B2 contains one of the 5 functions found in the formula and C2 contains a column reference like "A:A" (no quotes). HTH Jason Atlanta, GA -----Original Message----- Hello all, I do not know if dynamic is the right word because I did not find anything applicable when searching the newsgroup... In column A I have some data in form of numbers. In cell B1 I would like to have a formula. The formula should be dependent on what is entered in B2. For example if I enter 'sum' in B2, the B1 formula should look like '=sum(A:A)' If I then change B2 to 'count' then the B1 formula should be '=count(A:A)' The solution sould also work on something like worksheet names being part of a formula... Rgds, Thore . |
Hi Jason,
thanks for the reply. I was trying to avoid VBA, but do you have the link available (no idea what UDF stands for). Unfortunately the solution you proposed does not work because I do not know in advance what I will use later on. The whole thing is (in a first step) supposed to work only as a quick "Ctrl-h replace" version. Thanks anyway. "Jason Morin" wrote: It's not as easy as you'd think, and the best method would be via VBA. In fact, I think Chip Pearson posted UDF that achieves what you're looking for. However, you could use: =SUBTOTAL(VLOOKUP(LOWER(B2), {"average",1;"count",2;"max",4;"min",5;"sum",9},2, 0),INDIR ECT(C2)) where B2 contains one of the 5 functions found in the formula and C2 contains a column reference like "A:A" (no quotes). HTH Jason Atlanta, GA -----Original Message----- Hello all, I do not know if dynamic is the right word because I did not find anything applicable when searching the newsgroup... In column A I have some data in form of numbers. In cell B1 I would like to have a formula. The formula should be dependent on what is entered in B2. For example if I enter 'sum' in B2, the B1 formula should look like '=sum(A:A)' If I then change B2 to 'count' then the B1 formula should be '=count(A:A)' The solution sould also work on something like worksheet names being part of a formula... Rgds, Thore . |
All times are GMT +1. The time now is 05:07 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com