Incorporate Adjacent Cell in Formula w/o Naming Cell Address?

**cyberphonics** · 10-02-2009, 06:55 PM

Hi all! I searched and I keep finding something that's not quite what I'm looking to do so I think I'm missing something really simple.

Let's say that I want each cell in col b to be the sum of 5 plus whatever value is in the cell adjacent to it on the left (col).

I know that you can just write a formula in each cell like...

b1 will be =sum(a1+5)
b2 will be =sum(a2+5)

... but is there a shortcut so that you don't have to write out the actual cell address for each one?

Meaning - is there a predefined name or something that represents the cell to the left or right so you can just use that instead, allowing you to just copy paste the same formula all the way down the column? Something like...

b1 will be =sum(left+5)
b2 will be =sum(left+5)

... where "left" represents whatever value is in the cell to the left of that particular cell?

Does my question make any sense? Sorry if syntax is off at all I'm just trying to type this as quickly as possibly. Thanks!

**daddylonglegs** · 10-02-2009, 07:05 PM

You don't really need SUM function

=A1+5 will suffice

If you copy that formula down the column the referenced cell will adjust anyway e.g. it will automatically change to

=A2+5, =A3+5 etc.....Is that what you need?

**Special-K** · 10-02-2009, 07:05 PM

You only have to type the formula once, then use copy and paste to wherever you want it to go

**cyberphonics** · 10-02-2009, 07:16 PM

Thanks for the reply! That was actually just an example. I had sum in because I was originally writing out something different and got lazy so I just stopped and wrote addition instead.

What I'm actually doing is this. I have a date in the first column. In the second column, I want it to count how many months it's been since the date in the first column to whatever the current date is rounded up and yada yada yada, all this stuff.

It works fine, I just wanted to know if there was a way for me to not have to write in the literal address of the adjacent cells.

You said I can just copy and paste, but I don't get it. If I do that, wouldn't all of the things on the right be calculating values from the same one cell address as opposed to only the cell adjacent to it?

Meaning, if I were to just copy the formula and say...

b1 will be a1+5
b2 will be a1+5

... then wouldn't they all just be a1+5 as opposed to a1+5, a2+5, a3+5?

How would it know I want anything other than a1?

**daddylonglegs** · 10-02-2009, 07:19 PM

No, if you use exactly

=A1+5

in cell B1 then when you copy the formula down the column it will change row by row, so it will always reference the adjacent cell.

Conversely if you don't want the reference to change you can make it absolute with a $, e.g.

=A$1+5

If you copy that down it won't change

**cyberphonics** · 10-02-2009, 07:27 PM

Ok. Is there a special way to copy it or can I just ctrlc ctrlv it? Also, will it only do that if I fill in the first two myself so it recognizes that the pattern is from the adjacent cells or will it not matter?

**cyberphonics** · 10-02-2009, 07:42 PM

I tried and it didn't work. It's just returning what I said: calculating everything according to the first cell. It's not updating. Is it because it doesn't actually start with a cell address? Let me just write the whole thing out.

My date values are in column Q, starting at row 7. Like I said, what's in the R column is how many days it's been between whatever is in Q and today's date and then divided by 31 to get a rough, only approximate (cuz it does not need to be exact) idea of about how many months that might be. Then I just have that rounded off to no decimals for now to be changed later.

Here's what I wrote.

R7 =ROUND((DAYS360(Q7;TODAY()))/31;0)&" Month(s)"
R8 =ROUND((DAYS360(Q8;TODAY()))/31;0)&" Month(s)"

When I copy and paste that formula into R9, it does not update to say Q9, so am I copying and pasting it wrong or does it not work for formulas that don't begin with a cell address?

I'm new to the way Excel treats data in a cell by default and what happens when you move things around so this may be something very obvious to you but it's not to me so I know I can't be doing it right.

I thought the relative and absolute thing only applies when you're adding/deleting rows and cells so it knows whether or not to keep referencing the same cells?

**cyberphonics** · 10-02-2009, 08:09 PM

Nevermind, I figured it out. I put the formula in the first cell (R7). Then I clicked and held the little square at the bottom right of R7. I dragged it down over all the cells in the column that I wanted to transfer the formula to. When I released, all of the cells I highlighted were populated with the same formula only the reference to the adjacent cell was updated.

*phew*

"Copy down the column" and "copy and paste" is what confused me. I think "click and drag down the column" would have made more sense, because being new to Excel, I automatically associated the words "copy down" with "copy and paste all the way down" LOL I had no idea you could drag.

Thanks a bunch for your help! This saved me a ridiculous amount of time, believe me!

**daddylonglegs** · 10-03-2009, 08:34 AM

I suppose I normally use the phrase "copy down" but that certainly could be misinterpreted. "Fill down" is probably a better expression (there's a "Fill" option on the Edit menu if you wanted to do it that way).

If you have continuous data in the adjacent column, e.g. if you had dates from Q7 to Q1000, without blanks then you can more quickly populate R7:R1000 by just "double-clicking" the "fill-handle" (the "fill-handle is the black + you see when you put the cursor on the bottom right of the cell).

**cyberphonics** · 10-04-2009, 10:28 AM

Thanks a bunch for the info! I needed this figured out quickly, but now that it's taken care of and off where it needs to be, I'm definitely going to spend time looking at how Excel handles data, because stuff like what you just told me will definitely save time and make things more efficient for me