Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to reference or redirect a formula in a base row?
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to reference or redirect a formula in a base row?
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 -- Gary''s Student - gsnu2007k "Paul Schrum" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to reference or redirect a formula in a base row?
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 On Aug 14, 11:55*am, Gary''s Student wrote: 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!! : [. . .] -- Gary''s Student - gsnu2007k |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to reference or redirect a formula in a base row?
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 "Paul Schrum" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to redirect in VBA? | Excel Discussion (Misc queries) | |||
Changing Cell Reference in a macro on global base | Excel Discussion (Misc queries) | |||
how do redirect | Charts and Charting in Excel | |||
Designating a workbook reference base on a cells contents | Excel Worksheet Functions | |||
redirect from worksheet | Excel Discussion (Misc queries) |