SOLVED

How to create formula for cell Value from cell in other sheet by input row Number ?

%3CLINGO-SUB%20id%3D%22lingo-sub-3339300%22%20slang%3D%22en-US%22%3EHow%20to%20create%20formula%20for%20cell%20Value%20from%20cell%20in%20other%20sheet%20by%20input%20row%20Number%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3339300%22%20slang%3D%22en-US%22%3E%3CP%3EI%20want%20user%20only%20in%20put%20row%20number%20to%20start%20to%20Calculate%20data%20-%26gt%3B%20How%20to%20create%20formula%20for%20cell%20Value%20from%20cell%20in%20other%20sheet%20by%20input%20only%20row%20Number%20%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESheet%20A%20%3A%20Have%20data%20in%20many%20rows%3C%2FP%3E%3CP%3ERow%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20A%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20B%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BC%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BD%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20E%3C%2FP%3E%3CP%3E1%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20XXX%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%207%3C%2FP%3E%3CP%3E2%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BDDD%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B3.5%3C%2FP%3E%3CP%3E3%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BBBB%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B2%3C%2FP%3E%3CP%3E4%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20JJJ%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B1.33%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20...%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20...%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eat%20Sheet%20B%20%3A%20Want%20user%20in%20put%20only%20Row%20number%20to%20be%20Value%20from%20row%20sheet%20A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20user%3C%2FP%3E%3CP%3ESheet%20%3D%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20A%3C%2FP%3E%3CP%3ERow%20%3D%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20xx%20%26lt%3B-%20User%26nbsp%3B%20Input%20only%20row%20number%26nbsp%3B%20to%20Start%20for%20sum%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAt%20sheet%20B%3C%2FP%3E%3CP%3Ecell%20C10%20%3DSUM(C11%3AC999)%3C%2FP%3E%3CP%3Ecell%20C11%26nbsp%3BStart%20Value%26nbsp%3B%3C%2FP%3E%3CP%3Eat%20cell%20C11%20Start%20Value%20from%20Sheet%20A%3C%2FP%3E%3CP%3E%3DA!Bxx%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26lt%3B-%20How%20to%20create%20formulas%20or%20Function%20like%20this%20requirement%20%3F%3C%2FP%3E%3CP%3E%3DA!B(row%20xx%2B1)%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%26lt%3B-%20How%20to%20create%20formulas%20or%20Function%20like%20this%20requirement%20%3F%3C%2FP%3E%3CP%3E%3DA!B(row%20xx%2B2)%3C%2FP%3E%3CP%3E%3DA!B(row%20xx%2B3)%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B...%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B...%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20very%20much%3C%2FP%3E%3CP%3E%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3339300%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-3339335%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20create%20formula%20for%20cell%20Value%20from%20cell%20in%20other%20sheet%20by%20input%20row%20Number%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3339335%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1384081%22%20target%3D%22_blank%22%3E%40SomchaiSomjui2011%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DSUM(INDIRECT(%22'%22%26amp%3BC1%26amp%3B%22'!%22%26amp%3B%22C%22%26amp%3BD1%26amp%3B%22%3A%22%26amp%3B%22C%22%26amp%3BE1))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EIs%20this%20what%20you%20are%20looking%20for%3F%20In%20sheet%20A%20you%20can%20dynamically%20enter%20the%20row%20number%20in%20cells%20D1%20and%20E1.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3339441%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20create%20formula%20for%20cell%20Value%20from%20cell%20in%20other%20sheet%20by%20input%20row%20Number%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3339441%22%20slang%3D%22en-US%22%3EYes%20Thank%20you%20very%20much%20-%26gt%3B%20I%20can%20do%20it%20now%20by%20change%20some%20formula%20like%20this%3CBR%20%2F%3E'%3DINDIRECT(%24D%241%26amp%3B%22!%22%26amp%3B%22B%22%26amp%3B%24D%242)%3CBR%20%2F%3E%24D%241%20%3D%20Sheet%20A%3CBR%20%2F%3EB%20%3D%20Column%20B%3CBR%20%2F%3ED2%20%3D%20Start%20row%3CBR%20%2F%3E%3CBR%20%2F%3Eand%20next%20row%20i%20change%20Formula%20to%3CBR%20%2F%3E'%3DINDIRECT(%24D%241%26amp%3B%22!%22%26amp%3B%22B%22%26amp%3B%24D%242%2BB12)%20-%26gt%3B%20for%20Copy%20to%20999%20row%3CBR%20%2F%3EB12%20%3D%20Column%20B%20row%2012%20i'm%20runing%201%20%2C%202%20%2C%203%20%2C%204%2C%20to%20next%20row%3CBR%20%2F%3ENow%20i%20can%20do%20it%3CBR%20%2F%3EThank%20you%20very%20much%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3CBR%20%2F%3E%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FLINGO-BODY%3E
New Contributor

I want user only in put row number to start to Calculate data -> How to create formula for cell Value from cell in other sheet by input only row Number ?

 

Sheet A : Have data in many rows

Row      A              B             C           D          E

1          XXX          7

2         DDD         3.5

3         BBB           2

4          JJJ             1.33

            ...

            ...

            ...

 

at Sheet B : Want user in put only Row number to be Value from row sheet A 

 

For user

Sheet =      A

Row =        xx <- User  Input only row number  to Start for sum

 

 

At sheet B

cell C10 =SUM(C11:C999)

cell C11 Start Value 

at cell C11 Start Value from Sheet A

=A!Bxx                 <- How to create formulas or Function like this requirement ?

=A!B(row xx+1)    <- How to create formulas or Function like this requirement ?

=A!B(row xx+2)

=A!B(row xx+3)

   ...

   ...

   ...

 

Thank you very much

2 Replies
best response confirmed by SomchaiSomjui2011 (New Contributor)
Solution

@SomchaiSomjui2011 

=SUM(INDIRECT("'"&C1&"'!"&"C"&D1&":"&"C"&E1))

Is this what you are looking for? In sheet A you can dynamically enter the row number in cells D1 and E1. 

Yes Thank you very much -> I can do it now by change some formula like this
'=INDIRECT($D$1&"!"&"B"&$D$2)
$D$1 = Sheet A
B = Column B
D2 = Start row

and next row i change Formula to
'=INDIRECT($D$1&"!"&"B"&$D$2+B12) -> for Copy to 999 row
B12 = Column B row 12 i'm runing 1 , 2 , 3 , 4, to next row
Now i can do it
Thank you very much