How do you have a formula range be determined by the location ofanother cell?

[Daniel]

Random data set below for example. Using 1 column and many rows:
A
1) 40
2) 32
3) 42
4) 35
5) 12
....
no data in between
....
25) 39
26) 31
27) 25
28) 14
29) 09

This data will be manipulated by formulas that work on a fixed ($)
range (ie, $A$1:$A$5) at some column to the left. I will be coping
down these formulas and just updating the data in column A. The issue
is that with all the ranges being fixed when I copy down the rows the
ranges are not shifted down. If I removed the $'s everything will
work, but what I'm trying to do requires the fixed ranges (and for my
learning as well). I can find the address of the new range with
address(), but I can't apply it to the formula as it results as a text
in double quotes.
Example:
in cell A24 i can type in the following code:
=address(row(A25),column(A25),1) => $A$25
then the formulas doing stuff I would like to use this address:
=dosomethingwithfixedrange(A24:XX)

Reiteration: I want to find the cell address of a particular cell and
use that address as part of a fixed range in a different cell. Like
telling the formula where to start a range...

Any help will be MOST useful.

Thanks,
Daniel

 

[Pete_UK]

I don't fully understand your post, but have a look at the INDIRECT
function in XL Help. This allows you to build up a cell address or
range as if it were a string.

Hope this helps.

Pete

 

[Daniel]

Let me try explaining differently..

I have formulas dependent on Row numbers (ie, fixed ranges). However,
there are purposeful spaces being data sets and by spaces I mean blank
rows.

As I move down to lower rows (higher numbers) i want to copy my
formulas down. However, because they use fixed ranges, they don't
move down. I want to use the address() or some other technique to
update the fixed ranges...

Example:
A1 B1
1) 3 COUNT($B$1:$B$5)
2) 5
3) 3
4)
5) 1
....
10) 1 COUNT($B$1:$B$5)
11)
12)
13) 9
14) 8

When I copy down the formula in B1 the fixed range remains the same of
course. However, I want to keep the copied formula (in B10) to be
fixed, which means the ranges need to be updated. I want to do this
automatically, instead of manually.

Thanks,
Daniel