Relative - Constant Dilemma

[Tom_Kidd]

Hmmm... a little bit hard to explain here...

So... if I have something like =FORECAST.ETS($C100,$B$1:$B$99,$C$1:$C$99)

I want to be able to COPY PASTE (one column over and one row down) and have it change to... 

=FORECAST.ETS($C101,$B$2:$B$100,$C$2:$C$100)

The $C101 part works just fine. I just don't know how to make the other stuff change as such.

Any help would be greatly appreciated.

Thanks,

Tom Kidd

[#2]

Hello @Tom_Kidd,

That would be:

=FORECAST.ETS($C100,$B1:$B99,$C1:$C99)

As a note, the '$' symbol in front of a Column or Row "locks" that row in place as the formula is filled to adjacent cells. The reference $B1 locks the column B while B$1 locks the row 1. 

[#3]

Thank you, but that doesn't work in my particular situation. I think I am not describing the situation very well...

So understand everything you stated in your reply. That all makes sense, and I use these various combinations all the time.

So I'll try and rephrase the situation again to be more clear.

So... I have =FORECAST.ETS($C100,$B$1:$B$99,$C$1:$C$99)

I use this to predict the next 15 values.

I want to be able to COPY PASTE to the next Column and down one row... and have it be...

=FORECAST.ETS($C101,$B2$2:$B$100,$C$:$C$100)

Maybe an OFFSET Function? Maybe I have to get into Programming? Any guidance would be greatly appreciated.

[#4]

@Tom_Kidd 

This is not the ideal formula but perhaps this is what you're looking for...

=FORECAST.ETS($C100,INDIRECT(CONCAT("$B$"&ROWS($A$1:A1)&":$B$"&ROWS($A$1:A99))),INDIRECT(CONCAT("$C$"&ROWS($A$1:A1)&":$C$"&ROWS($A$1:A99))))

It uses a combination of INDIRECT() and CONCAT() functions to preserve the absolute references while acting as relative references when copied to another column/row.