0

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.

  • 2
    It sounds like you are referring to relative cell referencing. By default if you copy and paste a formula from one cell to another the cell references in the formula update unless the cells are referenced as absolute. For instance if you didn't want the column letter to change it should be entered in the formula as $B2 instead of B2. Let me know if this answers your question and I will move this from a comment into an Answer. – Folk Jan 20 '17 at 23:42
3

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:

  1. Select the cell with the formula in it, cell B4.
  2. Click-and-hold on the lower right corner of the cell (not the center).
  3. Drag to the right a few columns.
  4. "Unclick" (let my finger off the mouse).
  5. The formula is automatically copied into each cell in the range, but with a different column letter in each cell.

enter image description here

enter image description here

enter image description here

enter image description here

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.

  • Auto-fill in spreadsheets is one of the greatest inventions ever. I use it constantly. – ale Jan 21 '17 at 13:27
  • @ale Agreed! I use it constantly too. That and "auto sum" for a range. – BrettFromLA Jan 21 '17 at 18:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.