How to reference or redirect a formula in a base row?

[Paul Schrum]

I am an intermediate Excel end user.

Is there a way to enter a cell formula once, then have other cells in
lower rows (but the same column) refer to the "head" cell's formula as
if it were in the same row?

Sorry this description is so convoluted, but I this is the best I can
think of. Let me restate for clarity. I have non-tabular
information in rows 1-3. Row 4 contains column labels. Rows 5 and
lower contain data (in some columns) and formulas (in other columns).
For any given column, I would like to be able to enter the formula
once in Row 5. Then enter some other function in Rows 6 and lower
which would redirect to the formula in Row 5, except row numbers for
relative cell references are updated as if I had done a copy/paster
operation. If this function were named referenceFormula(), then cells
A5 through A7 would look like this:


Cell_ID: Formula As Entered: Actual Formual Behavior:
A5 =B5*$A$2+C5 =B5*$A$2+C5
A6 =referenceFormula(5) =B6*$A$2+C6
A7 =referenceFormula(5) =B7*$A$2+C7

The great advantage of this is that I could make a change to a formula
in the "base row" and not have to copy/paste or autofill the change
back down to all the other rows.

Thanks in advance to anyone who responds.

- Paul Schrum

 

[Gary''s Student]

Say we are potentially changing the fomula in cell A5 and want the formulas
in cells A6 thru A20 to follow suit. Rather then using copy/paste, Let's let
a macro do it automatically!! :

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set a5 = Range("A5")
Set a6_a20 = Range("A6:A20")
If Intersect(a5, t) Is Nothing Then Exit Sub
Application.EnableEvents = False
a5.Copy a6_a20
Application.EnableEvents = True
End Sub


Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

 

[Paul Schrum]

Mr. Student,

Thanks for your help and the time you took to generate your answer.

I was hoping that someone would have a built-in function or some other
prepackaged, native way to do this. But with the MVP's of the group
remaining silent, I take that to mean that such a feature does not
exist.

- Paul Schrum

 

[pshepard]

Hi Paul,

If you change the range to a table, when you change the first formula, all
of the formulas below it will change to the same format.

Hope this helps.
Peggy