Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002 Formula: Urgent Conditional Formula Required Right Away - if possible | Excel Discussion (Misc queries) | |||
Build excel formula using field values as text in the formula | Excel Worksheet Functions | |||
Excel 2002 formula displayed not value formula option not checked | Excel Worksheet Functions | |||
i edit a formula (excel) then it displays formula not answer | Excel Discussion (Misc queries) | |||
Converting an Excel formula to an Access query formula | Excel Discussion (Misc queries) |