Long story short I am just filling out a spreadsheet to track hours worked on a project. Row 4 calculates the time spent for each entry with a simple formula (example: =B3-B2) where the row 2 number is start time row 3 number is end time.
My calculation works fine.
What I need help with is that I don't want to have to type a formula manually into every single cell in row 4 as I fill out the spreadsheet, that is tedious and annoying as hell.
There has to be a way to use a function or variable that returns the column the cell is in so if I am entering this into B4 the calc as above would be B3-B2 but I want a variable that will return the B for me so that when I paste the same exact formula into column c, d, e on and on it returns column c, d, e saving me a bunch of typing in the calculation over time.
So essentially the calc would be (ColumnVariable[Row3] – ColumnVariable[Row2])=my output I would simply get by typing b3-b2 or c3-c2 or d3-d2 ect.
I just need to know what that column variable is so I can save myself a bunch of headache. I just haven't been able to find the right search terms to get the answer for this.
Best Answer
I put some random numbers in rows 2 and 3 (instead of times). In cell B4 I wrote this formula: "=B3-B2". To copy the formula to the cells to the right of it, but change the column letter, I just did this:
Note that the totals in row 4 are different. It's the cell value above it, minus the cell value above that. Also note that in the last picture, the formula in cell E4 is "=E3-E2".
You can drag that formula as many columns to the right as you want.