Relative - Constant Dilemma

%3CLINGO-SUB%20id%3D%22lingo-sub-1601181%22%20slang%3D%22en-US%22%3ERelative%20-%20Constant%20Dilemma%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1601181%22%20slang%3D%22en-US%22%3E%3CP%3EHmmm...%20a%20little%20bit%20hard%20to%20explain%20here...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo...%20if%20I%20have%20something%20like%20%3DFORECAST.ETS(%24C100%2C%24B%241%3A%24B%2499%2C%24C%241%3A%24C%2499)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20be%20able%20to%20COPY%20PASTE%20(one%20column%20over%20and%20one%20row%20down)%20and%20have%20it%20change%20to...%26nbsp%3B%3C%2FP%3E%3CP%3E%3DFORECAST.ETS(%3CFONT%20color%3D%22%23000000%22%3E%24C101%3C%2FFONT%3E%2C%3CU%3E%3CFONT%20color%3D%22%23000000%22%3E%24B%242%3A%24B%24100%2C%24C%242%3A%24C%24100%3C%2FFONT%3E%3C%2FU%3E)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20%24C101%20part%20works%20just%20fine.%20I%20just%20don't%20know%20how%20to%20make%20the%20other%20stuff%20change%20as%20such.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20greatly%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETom%20Kidd%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1601181%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1601698%22%20slang%3D%22en-US%22%3ERe%3A%20Relative%20-%20Constant%20Dilemma%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1601698%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F765622%22%20target%3D%22_blank%22%3E%40Tom_Kidd%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20would%20be%3A%3C%2FP%3E%3CPRE%3E%3CSPAN%3E%3DFORECAST.ETS(%24C100%2C%24B1%3A%24B99%2C%24C1%3A%24C99)%3C%2FSPAN%3E%3C%2FPRE%3E%3CP%3EAs%20a%20note%2C%20the%20'%24'%20symbol%20in%20front%20of%20a%20Column%20or%20Row%20%22locks%22%20that%20row%20in%20place%20as%20the%20formula%20is%20filled%20to%20adjacent%20cells.%20The%20reference%20%24B1%20locks%20the%20column%20B%20while%20B%241%20locks%20the%20row%201.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1602781%22%20slang%3D%22en-US%22%3ERe%3A%20Relative%20-%20Constant%20Dilemma%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1602781%22%20slang%3D%22en-US%22%3EThank%20you%2C%20but%20that%20doesn't%20work%20in%20my%20particular%20situation.%20I%20think%20I%20am%20not%20describing%20the%20situation%20very%20well...%3CBR%20%2F%3E%3CBR%20%2F%3ESo%20understand%20everything%20you%20stated%20in%20your%20reply.%20That%20all%20makes%20sense%2C%20and%20I%20use%20these%20various%20combinations%20all%20the%20time.%3CBR%20%2F%3E%3CBR%20%2F%3ESo%20I'll%20try%20and%20rephrase%20the%20situation%20again%20to%20be%20more%20clear.%3CBR%20%2F%3E%3CBR%20%2F%3ESo...%20I%20have%20%3DFORECAST.ETS(%24C100%2C%24B%241%3A%24B%2499%2C%24C%241%3A%24C%2499)%3CBR%20%2F%3E%3CBR%20%2F%3EI%20use%20this%20to%20predict%20the%20next%2015%20values.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20want%20to%20be%20able%20to%20COPY%20PASTE%20to%20the%20next%20Column%20and%20down%20one%20row...%20and%20have%20it%20be...%3CBR%20%2F%3E%3CBR%20%2F%3E%3DFORECAST.ETS(%24C101%2C%24B2%242%3A%24B%24100%2C%24C%24%3A%24C%24100)%3CBR%20%2F%3E%3CBR%20%2F%3EMaybe%20an%20OFFSET%20Function%3F%20Maybe%20I%20have%20to%20get%20into%20Programming%3F%20Any%20guidance%20would%20be%20greatly%20appreciated.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1612045%22%20slang%3D%22en-US%22%3ERe%3A%20Relative%20-%20Constant%20Dilemma%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1612045%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F765622%22%20target%3D%22_blank%22%3E%40Tom_Kidd%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20not%20the%20ideal%20formula%20but%20perhaps%20this%20is%20what%20you're%20looking%20for...%3C%2FP%3E%3CPRE%3E%3DFORECAST.ETS(%24C100%2CINDIRECT(CONCAT(%22%24B%24%22%26amp%3BROWS(%24A%241%3AA1)%26amp%3B%22%3A%24B%24%22%26amp%3BROWS(%24A%241%3AA99)))%2CINDIRECT(CONCAT(%22%24C%24%22%26amp%3BROWS(%24A%241%3AA1)%26amp%3B%22%3A%24C%24%22%26amp%3BROWS(%24A%241%3AA99))))%3C%2FPRE%3E%3CP%3EIt%20uses%20a%20combination%20of%20INDIRECT()%20and%20CONCAT()%20functions%20to%20preserve%20the%20absolute%20references%20while%20acting%20as%20relative%20references%20when%20copied%20to%20another%20column%2Frow.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

3 Replies

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. 

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.

@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.