Relative vs. absolute cell values

[wmjenner]

I get the concept of absolute and relative formulas. But what I can'
seem to make happen is this: Here's the scenario:

A B C D E
F
1 10 20 30 40 =d1
1/10/04
2 50 60 70 80 =if(f2=f1,e1+d2,d2)
1/10/04
3 90 100 110 120 =if(f3=f2,e2+d3,d3)
1/10/04
4 130 140 150 160 =if(f4=f3,e3+d4,d4)
1/10/04


What the says is to cumulate the totals for all dates that are th
same. As soon as I change the date in column F, it starts over. Thes
are weekly production "buckets." The problem arises when I want t
move the data around. Let's say I want to move A22 and put it abov
what is now row 3 (by cutting and inserting cut cells). When I d
that, the formulas in column E also change, throwing everything off.
have tried with and without dollar signs, I've tried using the R1C
method, but no matter what I do, Excel tries to make the formulas matc
where I've moved the data. I thought "absolute" meant that it alway
refers to the same cell whether you move it or not but apparently I'
missing something. Thanks for any help!

Bil

 

[wmjenner]

Oops! Didn't realize the formatting would be so messed up. The first
column (1,2,3,4,5) is the row numbers, Column A goes with the numbers
10,50,90 etc. The formulas belong in Column E and the dates are in
Column F. Sorry for the mess!

 

[Dave Peterson]

The absolute addresses have more to do when you copy the cell and paste
somewhere else. Either the reference will adjust to point at the same relative
cell or it'll stay the same--if you used $a$1.

(and if you used $A1 or A$1, parts of the reference would adjust and portions
wouldn't.)

But it kind of sounds like you want your formulas to always point at a certain
range--no matter if you move/copy/delete those cells.

If that's what you meant, take a look at the =indirect() function.

This formula will always point at: F3, F2, E2, D3--no matter what happens.

=IF(INDIRECT("F3")=INDIRECT("F2"),INDIRECT("E2")+INDIRECT("D3"),INDIRECT("D3"))

 

[wmjenner]

That's exactly what I want. Thanks for the response. Now I have a new
formula in my arsenal!