Master Formulae linked for Multiple Worksheets

[Renaissance]

Good Afternoon Everyone!

It's been a hot minute since I've been present here, but I'm looking for guidance on how I might accomplish what I'm sure is a simple solution, but can't figure out an approach that works.

In short, I want to create a column of formulae on a Master tab, that I either index-match or similar into other tabs. The trick is to get the formula copied over so that it grabs corresponding cells and not the cells listed in the Master formula. The reason behind is that if I need to make a change to the formulae all I have to do is adjust the Master which feeds all other tabs.

Example: I want a simple formula in C4 of the Master tab "=C$3*D4" where D4 is a static rate (can adjust at any time) and C$3 will become the C$3:Z$3 of the corresponding Report tab, which is where I will put actual numbers. Right now I put in an indirect(index-match) but it's giving me a #REF! error which I understand and am certain there is a way to pull in the formula and apply to current range, but am having a brain fart.

Master (Note: I only put in 50 to test formula, which would normally be blank/useless):

Master Formulae Link - Sample .xlsx
	A	B	C	D
1
2		Category	Formula	Rate
3		Volume	50
4		Purple	2.6	5.2%
5		Brown	2.15	4.3%
6		Orange	1	2.0%
7		Navy	0.5	1.0%
8		Teal	3	6.0%
9		Maroon	4	8.0%
Master

Cell Formulas
Range	Formula
C4:C9	C4	=C$3*D4

Report:

Master Formulae Link - Sample .xlsx
	A	B	C	D	E	F	G	H	I
1
2		Category	1/1/2021	2/1/2021	3/1/2021	4/1/2021	5/1/2021	6/1/2021	7/1/2021
3		Volume	45	50	55	60	65	70	75
4		Purple	#REF!
5		Brown
6		Orange
7		Navy
8		Teal
9		Maroon
10		Total
Report

Cell Formulas
Range	Formula
C4	C4	=INDIRECT(INDEX(Master!$B$2:$C$9,MATCH(Report!$B4,Master!$B$2:$B$9,0),2))

Thank you in advance for any time spent reading and/or providing ideas. It's always something simple, and I hope doesn't take much time out of anyone's day.

~Rena

 

[Tivakaran]

Hi. I didnt understand your question. Master cell is the core of the file it seems. If it reads from other sheets, then you need to setup that sheets first.

The #REF ; you need to remove the INDIRECT & start with INDEX instead

 

[Renaissance]

Thank you, Tivakaran, for your reply. The hardest part to asking this question (and most Excel questions) is asking the correct one, and to that extent I think I could probably rephrase it.

I understand where you're coming from as well as how to prevent the #REF error.

Let me rephrase: I'm looking to create a formula on one tab that has both a named reference or static cell (to a % in this example) while also having a relative reference. Then, I could either copy or reference this formula across a row (in this case both row 4) and it would multiply the static cell (5.2%) to the cells relative to where the formula was being copied. In this case, on the Report tab we would see the formula multiply the 5.2% to each corresponding cell within row 4 :

• C4 would multiply 5.2% by 45
• D4 would multiply 5.2% by 50
• E4 would multiply 5.2% by 55, and so forth

Now, I could write a simple formula in C4 like "=C3*INDEX(Master!$B$2:$C$9,MATCH(Report!$B4,Master!$B$2:$B$9,0),2)" and it will work just fine, but if I ever need to adjust C4 or others in the row, I have to manually change one and copy across. I'd like to avoid that by having a "master" cell on a separate tab that I change once and all cells referencing it can be left alone.

I'm starting with a simple example because at the core I feel it is simple and I'm missing something very small/simple, but after I figure this step out (or someone points me in the right direction). I'm just stuck at what I believe is so simple that I'm overthinking it. Thank you again for your help and I hope everyone is having a great weekend! I'm so close to the solution I can taste it....

 

[Tivakaran]

Hm..How about you use =VLOOKUP(B4,Master!$B$4:$D$9,3,0)*C$3 instead in Report C4? then you could copy it to new sheet and it would copied to new sheet?