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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Daniel - 07 Feb 2008 16:45 GMT

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

Reply to this Message

Pete_UK - 07 Feb 2008 17:06 GMT

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

> Random data set below for example. Using 1 column and many rows:
> A
[quoted text clipped - 35 lines]
> Thanks,
> Daniel

Reply to this Message

Daniel - 08 Feb 2008 15:01 GMT

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

> 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
[quoted text clipped - 43 lines]
> > Thanks,
> > Daniel

Reply to this Message