Creating a Cell Reference in Excel using a formula.

[tomc72]

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

[#2]

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

[#3]

@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

[#4 · Accepted solution]

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

[#5]

@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

[#6]

@tomc72 You're welcome!

[#7]

@Riny_van_Eekelen 

Thank You for this info. I'd like to ask you if you could help me in my idea.

=MATCH(Lookup value,Lookup Array'!$H6:$AG6,0)

What I want is to make the syntax 6 dynamic depending on a certain condition on another cell. Is there a way to make this number in the cell reference inside a formula dynamic?

[#8]

@ENGMAAKA Let's say that the number 6 is cell A1, then you can try this:

=MATCH(A3,INDIRECT("'Lookup Array'!$H"&A1&":$AG"&A1),0)

[#9]

@Riny_van_Eekelen 

Thank you, very much. Great!

[#10]

@Riny_van_Eekelen 

It worked perfect. I should learn more about INDIRECT Function.