Forum Discussion

Eng_Noah's avatar
Eng_Noah
Brass Contributor
Feb 19, 2024
Solved

Start spilling results of dynamice range one row below

Hello, reader!

 

I am trying to become better familiarized with Excel's new dynamic ranges having switched over from Google Sheets's ARRAYFORMULA function. I have provided a sample below of what I am trying to do. I have two columns, Thing 1 and Thing Two. I have named their ranges. Finally, the multiplication between both numbers are returned in the Return column.

 

For this example, I have the following formula within cell C2:
=Thing_1 * Thing_Two

 

 

My concern is that if someone were to accidentally delete that cell or row, for whatever reason, I'd lose the formula and thus would ruin my sheet. What I'd like to do is to be able to have all my actual values start in row 3, but have the formula in C2. Then, I'd like to have it start spilling in the 3rd row rather than the 2nd row.

 

Below is an example of what happens.

 

 

Finally, my question:

How would I go about having the dynamic array start displaying the results in the same rows as the rest of the data, but with the formula being entered in the second row?

 

PS: I can't use Excel's tables nor pivots for this.

Resources