Absolute column but reference current row

[Closed]
Report
-
Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
-
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

Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
486
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

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
Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
486
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