converting a text string into a formula?

[mangier]

I would like to automatically convert a text string into a formula's
range address which will allow me to avoid using database functions
and a criterial table to summarize data between two dates in a list.

I have used concatenation and a Match function to generate a text
string which represents a range address. Now I want to take that text
string and insert it into a Sum or Average formula and have it act
like a range address and not a text string. How can I do that.

Example:

A1 = 06/01/04
..
..
..
A30 = 06/30/04
B31 = B

A32 = 06/04/04 (the user defined start date)
B32 = 06/24/04 (the user defined end date)

C32 = B31&Match(A32,A1:A30,0)&":"&B31&Match(B32,A1:A30,0)

This creates a text string in C32 of "B4:B24" which I want to insert
into a Sum or Average formula. Is there any way to do this?

Also, is there a more straight forward way to get the address of a
cell based on its contents rather than the approach I used?

 

[Biff]

Hi mangier!

You got the hard part already figured out! Just use a
reference to C32 inside the INDIRECT() function:

=SUM(INDIRECT(C32))
=AVERAGE(INDIRECT(C32))

The way that you crafted the reference string is about as
good as it can be done based on the criteria you have.

Biff

 

[Biff]

I forgot to mention:

Or you can wrap you string formula inside the INDIRECT()
function:

=SUM(INDIRECT(B31&MATCH(A32,A1:A30,0)&":"&B31&MATCH
(B32,A1:A30,0)))

Biff

 

[Aladin Akyurek]

=SUM(INDEX($B$2:$B$30,MATCH(A32,$A$2:$A$30)+(LOOKUP(A32,$A$2:$A$30)<>A32)):I
NDEX($B$2:$B$30,MATCH(B32,$A$2:$A$30,1)))