SOLVED

change a cell reference

%3CLINGO-SUB%20id%3D%22lingo-sub-1617638%22%20slang%3D%22en-US%22%3Echange%20a%20cell%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1617638%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20night!%3CBR%20%2F%3EI%20have%20a%20formula%20in%20cell%20H3%2C%20%3D%20CONCAT%20(G3%3A%20G10).%20There%20are%208%20cells%20that%20will%20be%20concatenated%20(G3%3B%20G4%3B%20G5%3B%20G6%3B%20G7%3B%20G8%3B%20G9%3B%20G10).%20But%20I%20would%20like%20to%20vary%20the%20number%20of%20cells%20to%20be%20concatenated%20by%20changing%20this%20amount%20by%20typing%20in%20any%20cell.%20For%20example%20I%20would%20put%20the%20number%208%20in%20cell%20G1%20and%20the%20formula%20would%20concatenate%20from%20G3%20to%20G10%2C%20but%20if%20I%20put%2010%20in%20cell%20G1%20the%20formula%20would%20concatenate%20from%20G3%20to%20G12.%20Does%20anyone%20know%20the%20solution%3F%3CBR%20%2F%3EThank%20you.%3CBR%20%2F%3EAparecido.%3CBR%20%2F%3EBrazil%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1617638%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-1617720%22%20slang%3D%22en-US%22%3ERe%3A%20change%20a%20cell%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1617720%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F774009%22%20target%3D%22_blank%22%3E%40Cidoapis%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHello%20I%20hope%20I%20can%20assist%20you.%26nbsp%3B%3C%2FP%3E%3CP%3E%3DCONCAT(OFFSET(G2%2C0%2C0%2CG1%2C1))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20the%20answer%20is%20what%20you%20are%20looking%20for%2C%20don't%20be%20shy%20on%20hit%20the%20like%20button.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1617748%22%20slang%3D%22en-US%22%3ERe%3A%20change%20a%20cell%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1617748%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F767933%22%20target%3D%22_blank%22%3E%40Juliano-Petrukio%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3CBR%20%2F%3EYour%20answer%20solved%20a%20friend's%20problem.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1618486%22%20slang%3D%22en-US%22%3ERe%3A%20change%20a%20cell%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1618486%22%20slang%3D%22en-US%22%3EGood%20morning!%3CBR%20%2F%3EFor%20Excell%20in%20Portuguese%20from%20Brazil%2C%20the%20formula%20was%20as%20follows%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3DCONCAT(DESLOC(G3%3B0%3B0%3B%24G%241%3B1))%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Good night!
I have a formula in cell H3, = CONCAT (G3: G10). There are 8 cells that will be concatenated (G3; G4; G5; G6; G7; G8; G9; G10). But I would like to vary the number of cells to be concatenated by changing this amount by typing in any cell. For example I would put the number 8 in cell G1 and the formula would concatenate from G3 to G10, but if I put 10 in cell G1 the formula would concatenate from G3 to G12. Does anyone know the solution?
Thank you.
Aparecido.
Brazil

3 Replies
Highlighted
Best Response confirmed by Cidoapis (New Contributor)
Solution

@Cidoapis 

Hello I hope I can assist you. 

=CONCAT(OFFSET(G2,0,0,G1,1))

 

If the answer is what you are looking for, don't be shy on hit the like button. 

Highlighted

@Juliano-Petrukio 

Thank you!
Your answer solved a friend's problem.

Highlighted
Good morning!
For Excell in Portuguese from Brazil, the formula was as follows:

=CONCAT(DESLOC(G3;0;0;$G$1;1))