Excel formula help please?
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 |
Excel formula help please?
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 |
Excel formula help please?
=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 |
Excel formula help please?
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 |
Excel formula help please?
"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 |
Excel formula help please?
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 |
Excel formula help please?
"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 |
Excel formula help please?
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 |
All times are GMT +1. The time now is 03:55 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com