Newbie trying to learn to automate with generic all purpose functions

**Chartoonz** · 10-19-2011, 07:50 AM

I have been using excel for YEARS, but only at a very low level. Recently learned that with formulae and this VBA thing (yes, the VBA THING. I am THAT inexperienced) I could do more. So now I'm trying to learn.

I set up a column of numbers in A, let's say A1 = 1, A2 = 2, A3 = 3, A4 = 4 etc.
I want a GENERIC function to put in B's columns that will look left one column and add 10 to these numbers. How do I do that?

At first I would have set B1 to: "= A1+10"

But the A1 specification is bugging me. Is there a way to tell excel, wherever you are now, look one column over to the left and report THAT number and add 10 to it.

I tried with = OFFSET(A1, 0,0,1,1) + 10

But once again I need to specify the A1, and then A2, or change my offset values as I go down the chain.

Is there a way to do it generically so that I can copy a formula once and put it in each cell and get my desired result?

I'm sure experienced folk know how to get these results and I'm alos sure it's been posted here on the forums I am just so new to the nomenclature that I cannot find it with searches. Can anyone please point me in the right direction?

Thanks

ACC

**martindwilson** · 10-19-2011, 08:01 AM

b1=a1+10
drag the formula down and it will change to a2+10 and so on

**royUK** · 10-19-2011, 08:12 AM

I think you need

=OFFSET(A1,0,10) & drag down,

You don't need VBA

**Andrew-R** · 10-19-2011, 08:17 AM

Excel supports relative references for formula, if it's just specifying a base cell that bothers you.

If you look in the formula option there's a tick box for "R1C1 Formula". If you tick that then Excel will replace your coluimn letters with numbers and you can now put in the formula =RC[-1]+10 which means take the value of the cell on this row and one column to the left of this one and add 10 to it.