Excel formula help please?

[Victor Delta]

Hi

Can anyone help me with this please? I'm usually ok finding the right
functions to do what I want in Excel but, so far, this one has eluded me. I
suspect there may be a simple answer...?

I have a cell in a sheet, say B6, which contains a column address, say AE.

All I am trying to so, is create a formula in another cell (say D9) which
will yield the contents of cell AE1 - i.e. the cell in row 1 and the column
indicated by the contents of B6.

Any suggestions please?

Many thanks,

V

 

[Niek Otten]

Hi Victoria,

=INDIRECT(B6&1)

 

[Jim Thomlinson]

=INDIRECT(B6 & 1)

 

[T. Valko]

Try this:

=INDIRECT(B6&1)

 

[Victor Delta]

Try this:

=INDIRECT(B6&1)

Many thanks to all three of you for providing the solution so quickly.

I had tried the INDIRECT function earlier on but just couldn't get to the
simple formula you gave! Presumably the '&' in the formula is providing the
concatenate function?

V

 

[T. Valko]

Presumably the '&' in the formula is providing

the concatenate function?

Yes

B6 = AE (column designation)

We have the column now we need to include a row number to make it a valid
cell reference.

=INDIRECT(B6&1) =
=AE & row_number
=AE1

You might think that you could simply do this:

=B6&1

But if you tried that you found out Excel won't accept that as a valid cell
ref.. It's just a TEXT string. That's where the INDIRECT function comes into
play. It converts *TEXT representations* of references into valid
references.

 

[Victor Delta]

Yes

B6 = AE (column designation)

We have the column now we need to include a row number to make it a valid
cell reference.

=INDIRECT(B6&1) =
=AE & row_number
=AE1

You might think that you could simply do this:

=B6&1

But if you tried that you found out Excel won't accept that as a valid
cell ref.. It's just a TEXT string. That's where the INDIRECT function
comes into play. It converts *TEXT representations* of references into
valid references.

Biff

Many thanks for the helpful explanation. I've learned something!

V

 

[T. Valko]

You're welcome. Thanks for the feedback!