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

Hi Victoria,

=INDIRECT(B6&1)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Victor Delta" wrote in message
...
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

=INDIRECT(B6 & 1)
--
HTH...

Jim Thomlinson


"Victor Delta" wrote:

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

Try this:

=INDIRECT(B6&1)

--
Biff
Microsoft Excel MVP


"Victor Delta" wrote in message
...
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

"T. Valko" wrote in message
...
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

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.


--
Biff
Microsoft Excel MVP


"Victor Delta" wrote in message
...
"T. Valko" wrote in message
...
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" wrote in message
...
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.

Biff

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

V

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Victor Delta" wrote in message
...
"T. Valko" wrote in message
...
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.

Biff

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

V