CODEWITHSUNDEEP
excelexcel-formula
Eric4867
Eric4867

Reputation: 11

Cell Formula to link to another cell and use that formula

I'd like to create a sheet that has a master formula. From there, I'd like to have a number of lower cells below use the same cell formula but apply it at it's relative position.

For example, in B1 i would write = a1+1.

In b2, I'd like it to apply the b1 formula, so I'd type in a formula to link to b1, but what I really want is the code in that cell, and to have it be relative, so the output would be a2+1.

In b3, id reference b1 but have it return a3+1.

In b4, id reference b1 but have it return a4+1

In b5, id reference b1 but have it return a5+1

This is a very simplified version of what I need. the benefit of doing this is I can change the formula in B1 and have it cascade to all the cells below it. In reality, I can't just drag down because I'm using a template for financial statements on a per market basis. we have dozens of markets and its a pain in the rear to copy/paste them down each time I want to make a simple formula change.

Thanks for any comments. I'd like to do this without VBA if possible. With VBA it gets easy.

Upvotes: 1

Views: 4224

Answers (1)

teylyn
teylyn

Reputation: 35915

Have you considered Named Formulas? If you use relative references in named formulas, then they will be adjusted if the named formula is used in another cell.

For example: Select cell B2, click Formulas > Name Manager > New, enter a name like B2Formula and let it refer to 

=Sheet1!A1+1

No $ signs in the cell reference!!!

Now enter this formula into B2:

=B2Formula

Copy the formula down.

enter image description here

Now you decide you need a different formula in B2. So edit the named formula and change it to 

=Sheet1!A1*2

and look at the sheet! You only edited one formula, but all the values in column B have adjusted to that new formula

enter image description here

Named Formulas are VERY powerful, especially when used with relative references. Remember that the references will be relative to the cell that is selected when the named formula is defined. Also, when you need to change a named formula with a relative reference, you need to select the same original cell before editing the named formula.

Upvotes: 4

Related Questions