Indexing a formula

  • Thread starter Thread starter John K
  • Start date Start date
J

John K

I have a list of values in a column in cells A1:A15

I've written a formula that accesses the number in cell A1.

I want to be able to copy the formula to various areas on
the worksheet. But....each time I copy it, I want it to
reference the next number in the list. For instance, I
want to copy the formula with reference to the value in
cell A2, then A3, etc....

How do I index the location of the next value I want?

Thanx
 
You would definitely need a macro for that.
And an exact description of whether you are
doing this one cell at time, whether you can
use multiple areas and do it all at once. How
persistent should the next starting point be.

Hopefully all you want in the formula is a reference
to a single cell which increments. Excel 2000
will treat multiple areas in the selection. Excel 95
will not. Don't know about Excel 97. This will not
be persistent, the A1 is hardcoded, so you would
restart each time you run the macro.

Sub PopulateAreas_A1()
dim i as long
dim cell as range
For each cell in selection
cell.formula = "=" & range("A1").offset(i,0).address(0,0)
i = i + 1
next cell
end sub

to test:
populate A1:A40 with data such as each cells own address.
=ADDRESS(ROW(),COLUMN(),4)
or using MarkCells macro from my join.htm webpage.
http://www.mvps.org/dmcritchie/excel/join.htm#markcells
or place A1 into the cell A1 and fill down to A40.
select ranges B1:B5, C6:C10, D11:H11, E16:D40
and run the macro.

Since the question obviously needs programming, and this
is not the programming newsgroup, I expect
you will need instructions to install / use a macro.
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Not what you asked for, but I think what Don may have
been thinking of is Chip Pearson's addition to context menu
to copy the same formula regardless of placement. You also
need code each time you bring up Excel see references.

Sub CopyFormula()
'Chip Pearson, microsoft.public.excel.worksheet.functions, 2000/05/02
'http://google.com/groups?hl=en&newwindow=1&th=4831aec5cbe19367&rnum=1
'http://google.com/groups?as_umsgid=OWeRetUjBHA.2212@tkmsftngp05
Dim x As New DataObject
x.SetText ActiveCell.Formula
x.PutInClipboard
End Sub
 
Back
Top