#SPILL error using named ranges in dynamic formulae

Hi, I am using name ranges.
When projecting numbers I would like to take the previous year number and multiplying them to the respective name range.
However, upon autofill, it does not let me change the cell reference of the year number, so it is just being calculated from one specific year, and not changing to the previous year with each calculation.
ie. I would like to reference the previous column.

Please can someone help me?

G11 is: =F11*(1+RevGrowth), which is correct
H11 should be: =G11*(1+RevGrowth) but shows =F11*(1+RevGrowth)
I11 should be: =H11*(1+RevGrowth) but shows =F11*(1+RevGrowth)
Likewise,
G12 is: =G11*COGSMargin, which is correct
H12 should be: H11*COGSMargin but shows G11*COGSMargin
I12 should be: I11*COGSMargin but shows G11*COGSMargin

I try editing H11, I11, H12 and I12 however the inputs are greyed out. When editing, I get a #SPILL error.

Please can anyone help?

Please review the description you gave us while looking at your file. I assume you are looking at the sheet "Answer 4.1". The formulas on that sheet look correct to me. The formulas in G11:I11 each take the prior year's actuals or projection, and project growth of RevGrowth percent.

G11 is: =F11*(1+RevGrowth) so far, so good

H11 should be: =F12*(1+RevGrowth) but shows =F11*(1+RevGrowth) actually it shows =G11*(1+@RevGrowth2). Why do you think it should be anything different?

J11 should be: =F13*(1+RevGrowth) but shows =F11*(1+RevGrowth) There is nothing in J11. However, I11 shows =H11*(1+@RevGrowth2). Why do you think it should be anything different?

In your description of what "should be" you are talking about referencing the next row, but that is wrong. You want to reference the previous column, which is what the formulas do.

When you copy or fill cells, relative references to cell keep the same relative distance. That is, if you have a formula in G11 that refers to F11, it is referring to the cell one column to the left in the same row. If you copy that formula to H11, it will refer to G11, one column to the left in the same row.

Apologies - I meant to write that the worksheet on Question 4.1.

Yes, I would like to reference the previous column,


G11 is correct,
however H11 and I11 do not reference the previous column.
When I try to change H11, I11 etc. to reference the previous column, I get a #SPILL error.

Please could you advise on how to fix this?

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. As you are new here, I have done it for you today.)

Please update your forum profile: your Excel version is MS365 (not the release number thereof). Thanks.

The file you attached in post #3 has nothing in G11. It looks like you edited post #1 and uploaded a different file there, so let's go with that.

RevGrowth is an array. When you multiply by an array, you get an array as a result. In this case, each formula generates an array with three values that will go into that cell and the next two cells. So each cell's result overlaps the next cell's results, hence the SPILL error.

To return a single corresponding value from the array instead of the whole array, use a @ in front of the range name:

Values as displayed

	G	H	I
9	Projections
10	31-Dec 17	31-Dec 18	31-Dec 19
11	5,400.0	6,588.0	8,169.1

Underlying formulas

	G	H	I
9	Projections
10	=EOMONTH(F10,12)	=EOMONTH(G10,12)	=EOMONTH(H10,12)
11	=F11*(1+@RevGrowth)	=G11*(1+@RevGrowth)	=H11*(1+@RevGrowth)

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.