Can I create a 'MASTER' formula for use throughout a workbook?

[lisamcravey]

I have (what I think is a!) pretty complicated spreadsheet of formulas, which I've saved as a blank spreadsheet to use repeatedly for different datasets.

The spreadsheet has identical formulas filling 3 of every 4 rows, and references different data depending on the row number. The other 1 of every 4 rows also share a formula, but I need them set up grouped as a 4, so it basically follows the format below:

ROW1: formula 1
ROW2: formula 2
ROW3: formula 2
ROW4: formula 2
ROW5: formula 1
ROW6: formula 2
ROW7: formula 2
ROW8: formula 2

....etc.

My issue is, I do change the formula on the blank sheet quite a bit, as I update my processes and refine workflows / output etc, and because of the way the sheet is set up, I can't copy paste the formulas throughout the workbook without individually skipping the odd row that doesn't follow the same formula.

SO, my thought was, I could have two 'master' cells at the top of the sheet which contain the main formulas, and then each cell could reference the formula in those to calculate?

i.e. Cell A1 might read "=B2+C2+D2" as a master formula, which is easily changed, and then cell E2='FORMULAofA1'+1, so E1 would use a formula in cell A1 as part of it's calculation. This means if I wanted to add A2 to the formula, I could just change cell A1 to "=A2+B2+C2+D2" and everything else would update?

This would mean if I wanted to change the formulas in my sheet I would only have to amend it in a couple of cells rather than getting into a fankle with the full sheet.

Is this possible? And if so, can anyone help with how to achieve this?

I'm using Microsoft Excel 2010.

Thanks,

Lisa

 

[Darren Bartrup]

Hi and welcome to the board,

Sounds like this should work already -

In cell A1 you've got =B2+C2+D2 which will give a result. Any reference to cell A1 will look at the result.... so =$A$1+1 will always give the result of A1 plus 1.
If you use absolute references so that your formula always looks at the correct cell no matter how you move it around - $A$1 always references cell A1, on the other hand A1 doesn't - if you copy your formula elsewhere your formula will change to.

MS Excel: Relative vs Absolute referencing in Excel 2003/XP/2000/97 should give a better explanation.

 

[hateme28]

you can reference the formula, so if you have a1 contain "=SUM(B2,C2,D2)" you can have E1 contain "=SUM(A1,E2) see table below:

is this what you're looking for?

a	b	c	d	e
18				20
	10	5	3	2

<TBODY>
</TBODY>

 

[lisamcravey]

Hi, thanks for your reply!

Unfortunately no - apologies, I'm not explaining myself very well. I'll try and do a very simplistic demo example below...

I basically want a couple of base formula cells at the top of my sheet, (I don't need them to actually calculate anything, just to hold a 'master' formula,) which I can use as a master to allow me to edit the formulas in the remainder of the sheet without having to edit every cell? I can't reference them directly as you suggest above, because, as I've stupidly not shown in my demo, I need the calculation to change per row depending on the data in that row, so while my master formula below references Row 4, which is correct for Row 4, I really need it to change per row depending on row number.

Right now I have each cell in the G column holding a full formula referencing it's own row, but it means that if I want to edit the formula I have to individually change every line, because I can't copy/paste down the column because of the changes in formula every 3 lines. I want to be able to edit the main calculation of the formula once, and have it automatically edit each of the other formulas in the sheet?

 

[RonB1111]

Here's a formula that does allow you to copy/paste down. Using the data in your 2nd post, enter this formula in G3 and then copy down:

=IF(MOD(ROW()-3,4)=0,"Your1stFormula","Your2ndFormula")

You'll notice that rows 3,7,11,etc give "Your1stFormula" and all other rows "Your2ndFormula". Now in G3, replace the phrase "Your1stFormula" with whatever formula you want for rows 3,7,11, etc, and then the same for "Your2ndFormula". Now copy cell G3 down. Then whenever you want to change either formula, you simply make the change in G3 and copy down.

If your real data begins in some row other than row 3, change the 3 in the formula to whatever row your data begins. If you want to use the 1st formula every, say 6th row instead of every 4th row, change the 4 to 6.

 

[lisamcravey]

Ideal! Thanks!!!

 

[RonB1111]

You're welcome.

 

[mikerickson]

Have you looked at named formulas. Since names obey the rules of absolute/relative addressing, they can be used to calculate differnet parts of a worksheet.

Select C1

Define a name. Name: mySum RefersTo: =Sheet1!A1+Sheet1!B1

Then put =mySum in C1. Then drag down, note that the value returned is the sum of the two cells to the left of the cell holding the formula.

Rather than edit these master formulas on a sheet, you could edit them in the Name Manager.

 

[c0barnes]

Have you looked at named formulas. Since names obey the rules of absolute/relative addressing, they can be used to calculate differnet parts of a worksheet.

Select C1

Define a name. Name: mySum RefersTo: =Sheet1!A1+Sheet1!B1

Then put =mySum in C1. Then drag down, note that the value returned is the sum of the two cells to the left of the cell holding the formula.

Rather than edit these master formulas on a sheet, you could edit them in the Name Manager.

This is so close to what I want! The problem with this clever trick is that it requires fiddling with Name Manager, and isn't obvious to an average decent Excel user. I've been trying to find a way to do exactly what this does, but have the master formula live in a cell. The underlying problem is I often make calculation sheets with formulas copied to the right for multiple columns, and I can't be 100% sure each row has been copied right after each update. I've played with array formulas and Table() stuff, but that suffers from the same trouble of being tough for average decent Excel user to pick up right away.

 

[mikerickson]

You could do something like
Put the string '=A1+B1 in cell C1.
Define a Name: MyFormula RefersTo: =EVALUATE(Sheet1!$C$1)
put =myFormula in D1 and the sum is shown.

The problem is that the relative addresses won't adjust as you drag down.

A work-around for that would be:
In E1, put the formula =SUBSTITUTE(C1, "A1", "INDEX(A:A, ROW(), 1)")
In F1, put the formula =SUBSTITUTE(E1, "B1", "INDEX(B:B,ROW(), 1)")

Then Name: myFormula2 RefersTo: =EVALUATE(Sheet1!$F$1)

will adjust as you drag down.

Since E1 and F1 can be off sheet (or in a hidden sheet), the work-around will be invisible to the user.