SOLVED

Referencing a Row Number

%3CLINGO-SUB%20id%3D%22lingo-sub-2918037%22%20slang%3D%22en-US%22%3EReferencing%20a%20Row%20Number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2918037%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20use%20the%20text%20data%20entered%20on%20Sheet%201%20and%20re-use%20it%20as%20text%20in%20Sheet2.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EText%20in%20Sheet1%2C%20Row%202%20is%20rearranged%20to%20a%20nice%20formatted%20display%20on%20Sheet%202.%20I%20can%20do%20this%20by%20entering%26nbsp%3B%3C%2FP%3E%3CP%3E%3D'Sheet1'!B2%20and%20as%20needed%20for%20other%20fields.%26nbsp%3B%20Using%20this%20for%20data%20in%20A2%2C%20B2%2C%20C2%2C%20D2%2C%20E2%2C%20F2.%3C%2FP%3E%3CP%3EI%20am%20using%20CONCATENATE%20to%20put%20text%20strings%20together%20in%20some%20cells.%3C%2FP%3E%3CP%3EI%20would%20like%20to%20have%20a%20Cell%20that%20I%20can%20put%20a%20number%20in%2C%20and%20that%20number%20represents%20the%20Row%20Number%20in%20Sheet%201.%26nbsp%3B%20%26nbsp%3BFor%20Example%20-%20put%20a%204%20in%20the%20Cell%20%26amp%3B%20then%20the%20Sheet%202%20fields%20use%20data%20from%20Row%204%20on%20Sheet1.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3D'Sheet1'!B2%20and%20as%20needed%20for%20other%20fields.%26nbsp%3B%20Using%20this%20for%20data%20in%20A2%2C%20B2%2C%20C2%2C%20D2%2C%20E2%2C%20F2.%3C%2FP%3E%3CP%3EEnter%20Number%204%26nbsp%3B%20and%20all%20the%20data%20matches%20basically%20becomes%3C%2FP%3E%3CP%3E%3D'Sheet1'!B4%20and%20uses%20data%20from%20A4%2C%20B4%2C%20C4%2C%20D4%2C%20E4%2C%20F4.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2918037%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2918202%22%20slang%3D%22en-US%22%3ERe%3A%20Referencing%20a%20Row%20Number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2918202%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1204272%22%20target%3D%22_blank%22%3E%40MikeD760%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESee%20the%20attached%20version.%20The%20row%20number%20is%20entered%20in%20F1%20(you%20can%20move%20it%20of%20course)%20and%20the%20formulas%20use%20INDIRECT%20to%20refer%20to%20the%20value%20of%20F1%20as%20row%20number.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2918267%22%20slang%3D%22en-US%22%3ERe%3A%20Referencing%20a%20Row%20Number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2918267%22%20slang%3D%22en-US%22%3EThank%20you%20so%20much!%20This%20has%20been%20driving%20me%20crazy.%20I%20didn't%20get%20the%20syntax%20right%20when%20I%20tried%20it.%3CBR%20%2F%3EThanks%20again.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2918281%22%20slang%3D%22en-US%22%3ERe%3A%20Referencing%20a%20Row%20Number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2918281%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1204272%22%20target%3D%22_blank%22%3E%40MikeD760%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20an%20alternative.%20INDIRECT%20is%20one%20way%2C%20as%20has%20been%20shown.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20choose%20to%20use%20VLOOKUP..%20As%20with%20the%20other%20response%2C%20you%20can%20enter%20a%20row%20number%20here%20(which%20doubles%20as%20an%20account%20number%20on%20your%20%22Account%20Details%22%20sheet)%2C%20entering%20it%20in%20cell%20F1.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20only%20did%20the%20first%2011%20rows%20on%20%22Bin%20Cards%22%20figuring%20you%20can%20do%20the%20rest%20following%20the%20example.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I am trying to use the text data entered on Sheet 1 and re-use it as text in Sheet2.

 

Text in Sheet1, Row 2 is rearranged to a nice formatted display on Sheet 2. I can do this by entering 

='Sheet1'!B2 and as needed for other fields.  Using this for data in A2, B2, C2, D2, E2, F2.

I am using CONCATENATE to put text strings together in some cells.

I would like to have a Cell that I can put a number in, and that number represents the Row Number in Sheet 1.   For Example - put a 4 in the Cell & then the Sheet 2 fields use data from Row 4 on Sheet1.

 

='Sheet1'!B2 and as needed for other fields.  Using this for data in A2, B2, C2, D2, E2, F2.

Enter Number 4  and all the data matches basically becomes

='Sheet1'!B4 and uses data from A4, B4, C4, D4, E4, F4.

 

 

 

6 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

@MikeD760 

See the attached version. The row number is entered in F1 (you can move it of course) and the formulas use INDIRECT to refer to the value of F1 as row number.

Thank you so much! This has been driving me crazy. I didn't get the syntax right when I tried it.
Thanks again.

@MikeD760 

 

Here's an alternative. INDIRECT is one way, as has been shown.

 

I would choose to use VLOOKUP.. As with the other response, you can enter a row number here (which doubles as an account number on your "Account Details" sheet), entering it in cell F1.

 

I only did the first 11 rows on "Bin Cards" figuring you can do the rest following the example.

@MikeD760 

Yet another option: using INDEX.

Thanks! I was having a hard time getting the syntax just right. Appreciate your help.
Thanks for this option without the " " quotes. Appreciate your help.