Absolute column but reference current row

Closed
Driving me crazy - Feb 6, 2012 at 03:24 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 - Feb 9, 2012 at 09:36 AM
Hello,

Here is my formula i need it to auto fill for a column which stays that same but changes depending which row.

Column T is time spent
Column U is Total time with a circular reference set to 1 iteration
=IF(CELL("address")="$T$2",T2+U2,U2)

This formula does what i need it to do but it wont auto fill for the other rows I understand that i need to take out the dollar sign before the 2 in order for it to keep same column but change row number, I did that but it still did not autofill - instead it looks like this when i auto fill
=IF(CELL("address")="$T2",T3+U3,U3)

The $T2 obviously needs to be $T3. All the other values change like they should but the other one does not.
Also if i take out the second dollar sign in the first formula - it is broken and doesnt work (it adds up time spent ie. 1:00 in column T and adds it to the total in column U)



Can someone please help?
Thank you so much!



1 reply

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 544
Feb 7, 2012 at 10:23 AM
Hi Driving me crazy,

Only cell references will change when autofilled.
When you place double quotes around a cell reference it becomes a value.

Besides that I don't understand what you are trying to do.
Your statement will only be true when formula is placed in cell T2, but then T2 doesn't have a value. So T2+U2 and U2 will have the same result.

I'm probably missing something, please fill me in.

Best regards,
Trowa
0
Driving me crazy
Feb 7, 2012 at 11:09 AM
Thank you so much for trying to help me.

the formula is placed in cell U2, I want it to Autofill down (U3, U4....) as you stated T2 doesnt start out with any thing in it. Please see below

T U
Time Spent Total Time
1:00 1:00:00
0:00:00
0:00:00
0:00:00
0:00:00

When you enter a time in column T like 1:00 (1 hour) it keeps a running total in column U.

This formula does that for me
=IF(CELL("address")="$T$2",T2+U2,U2)

But it wont allow me to auto fill down.

If i remove the double quotes it does not work.

if i remove the second dollar sign it does not work

The autofill looks like this
=IF(CELL("address")="$T$2",T3+U3,U3)
where everything changes except the $T$2 - which needs to change to $T$3

I need to autofill for about 200 rows if i want the formula to work i can manually enter each formula for each row but i also have a macro that takes all completed rows and moves to another sheet. this causes the row numbers to change which if it autofilled would be ok, but since i have to manually enter the formula for each row, when the completed ones are removed it does not change the $T$2 part of the formula.

I hope i explained that in a way you could understand what i am trying to do
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 544
Feb 9, 2012 at 09:36 AM
Hi Driving me crazy,

I'm still trying to figure out what you want with the formula you are using.
=IF(CELL("address")="$T$2",T2+U2,U2) placed in U2.
CELL("address") will be $U$2.
$U$2=$T$2 will always be false.
Therefore result will always be U2.

You said:
When you enter a time in column T like 1:00 (1 hour) it keeps a running total in column U.
What I understand from that is the following.
Column(T) Column(U)
1:00 1:00
3:00 4:00
2:00 6:00

Coulmn(T) is entered, Column(U) is calculated.
Formula U2: =T2
Formula U3: =SUM($T$2:T3)
Drag this last formula down.

Are we on the same page?

Best regards,
Trowa
0