SOLVED

Creating a Cell Reference in Excel using a formula.

%3CLINGO-SUB%20id%3D%22lingo-sub-1935514%22%20slang%3D%22en-US%22%3ECreating%20a%20Cell%20Reference%20in%20Excel%20using%20a%20formula.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1935514%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20it%20possible%20to%20create%20a%20Cell%20Reference%20using%20a%20formula%3F%20For%20example%3A%20If%20I%20want%20the%20value%20in%20Cell%20B1%20to%20equal%20the%20value%20in%20Cell%20A1%2C%20can%20I%20put%20in%20Cell%20B1%20something%20like%2C%20%3D%22B%22%20%26amp%3B%201%20-%20(I%20know%20this%20e.g.%20formula%20doesn't%20work%2C%20but%20is%20there%20one%20that%20would%2C%20please%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETom%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1935514%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1935844%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20Cell%20Reference%20in%20Excel%20using%20a%20formula.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1935844%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F388625%22%20target%3D%22_blank%22%3E%40tomc72%3C%2FA%3E%26nbsp%3BJust%20enter%20%3CSTRONG%3E%3DA1%3C%2FSTRONG%3E%20in%20cell%20B1%2C%20or%20is%20this%20not%20what%20you%20want%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlternatively%2C%20you%20may%20want%20to%20look%20into%20the%20INDIRECT%20or%20ADDRESS%20functions.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1937628%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20Cell%20Reference%20in%20Excel%20using%20a%20formula.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1937628%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Riny%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20are%20right%2C%20that%20is%20not%20what%20I%20want%20%3Asmiling_face_with_smiling_eyes%3A.%20But%20I%C2%B4m%20very%20grateful%20for%20your%20mention%20of%20the%20Address%20function%20because%20I%20may%20be%20able%20to%20come%20up%20with%20a%20work-around%20by%20using%20it%20in%20conjunction%20with%20the%20Row%20function.%20I%20am%20going%20to%20have%20to%20play%20around%20with%20that.%3C%2FP%3E%3CP%3EHowever%2C%20regarding%20my%20core%20issue%2C%20perhaps%20I%20could%20explain%20myself%20better%3A%3C%2FP%3E%3CP%3ESay%20I%20have%20a%20randomly%20generated%20letter%20(say%20W)%20and%20a%20randomly%20generated%20number%20(say%2023).%20I%20need%20a%20formula%20in%20(say%20A1)%20that%20adds%20the%20W%20and%20the%2023%20together%20so%20that%20cell%20AI%20displays%20%3CSTRONG%3E%3CU%3Enot%3C%2FU%3E%3C%2FSTRONG%3E%20W23%2C%20but%20instead%20displays%20the%20%3CU%3E%3CSTRONG%3Econtent%26nbsp%3Bof%20cell%20W23%3C%2FSTRONG%3E.%3C%2FU%3E%3C%2FP%3E%3CP%3EIs%20that%20possible%3F%3C%2FP%3E%3CP%3EMany%20thanks%20for%20your%20help%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETom%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1937643%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20Cell%20Reference%20in%20Excel%20using%20a%20formula.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1937643%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F388625%22%20target%3D%22_blank%22%3E%40tomc72%3C%2FA%3E%26nbsp%3BOkay!%20Perhaps%20the%20picture%20below%20will%20point%20you%20in%20the%20right%20direction%20then.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202020-11-28%20at%2015.10.27.png%22%20style%3D%22width%3A%20327px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F236451iFE326E44BAA7DD2F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202020-11-28%20at%2015.10.27.png%22%20alt%3D%22Screenshot%202020-11-28%20at%2015.10.27.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

Is it possible to create a Cell Reference using a formula? For example: If I want the value in Cell B1 to equal the value in Cell A1, can I put in Cell B1 something like, ="B" & 1 - (I know this e.g. formula doesn't work, but is there one that would, please?

 

Many thanks,

 

Tom

5 Replies

@tomc72 Just enter =A1 in cell B1, or is this not what you want?

 

Alternatively, you may want to look into the INDIRECT or ADDRESS functions.

@Riny_van_Eekelen 

Hi Riny,

 

You are right, that is not what I want . But I´m very grateful for your mention of the Address function because I may be able to come up with a work-around by using it in conjunction with the Row function. I am going to have to play around with that.

However, regarding my core issue, perhaps I could explain myself better:

Say I have a randomly generated letter (say W) and a randomly generated number (say 23). I need a formula in (say A1) that adds the W and the 23 together so that cell AI displays not W23, but instead displays the content of cell W23.

Is that possible?

Many thanks for your help,

 

Tom

Best Response confirmed by tomc72 (Contributor)
Solution

@tomc72 Okay! Perhaps the picture below will point you in the right direction then.

 

Screenshot 2020-11-28 at 15.10.27.png

@Riny_van_Eekelen 

 

Hi Riny,

Yes, that´s a great help... I can see how I could work with that. Thank you very much.

Tom

@tomc72 You're welcome!