Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.
I am trying to use the 'COLUMN' function to give me the column number for a cell. The problem is that the cell coordinates I want to use are entered as coordinates within another cell.
For example, the coordinates I want to use are Q4, but these coordinates are entered into cell AW4, therefore when I use the column function for that cell it gives the column number 49, not 17 as I require.
Any suggestions for how to overcome this?
Thanks.
Re: use cell entry for column function, not cell coordinates
I'm little confused
COLUMN(AW4) will give 49 as this is 49 column from left
You can use
=INDIRECT("AW4")
but why not simple =AW4
Can you post an example workbook?
Re: use cell entry for column function, not cell coordinates
Sorry that may not have been adequately explained. I know that the column function gives the number of the cell from the left, but what I'm trying to do is find a function that will give me the column number according to coordinates that are listed in a cell, not using the coordinates of the cell itself. For example, if we use the picture below:- Sorry the picture wont upload, but from the description below I'm sure you can get the picture.
In column M, I have listed 3 different sets of coordinates which each relate to a cell somewhere within that row. Now lets imagine I would like to use the column function to find the number of the column for coordinates 'E5' (i.e. I would like it to generate the number 5). However, when I use the COLUMN function, =COLUMN(M1) gives me 13, not 5. So what I would like to do is find a way to make the column function recognise the coordinate E1 (even though it's listed in M1).
Thanks.
Re: use cell entry for column function, not cell coordinates
..In column M, I have listed 3 different sets of coordinates..
3 sets in one column?
Are they separeted? in one cell?
Quote from khr;680808...l somewhere within that row..
In which row?
Re: use cell entry for column function, not cell coordinates
| A | B | C | D | E | F | G | H | I | J | K | L | M | |
| 1 | 343 | 87 | 45 | 3 | 97 | 34 | 675 | 654 | 8 | 67 | 876 | 877 | E1 |
| 2 | 76 | 98 | 3 | 768 | 98 | 67 | 45 | 789 | 34 | 45 | 54 | 89 | F2 |
| 3 | 654 | 65 | 76 | 23 | 12 | 76 | 345 | 765 | 987 | 5 | 45 | 54 | J3 |
Ok so referring to the above table. In column M I have listed 3 sets of coordinates which each relate to a cell somewhere in the row. If we imagine that I would like to use the column function to find the number of the column for coordinates 'E1' that are listed in cell M1.Using the column function for cell M1 gives: =COLUMN(M1) giving an answer of 13 (i.e. the 13th column from the left). However what I would like to do is use the contents of M1 (i.e. E1) in the COLUMN function i.e. =COLUMN(E1) giving me an answer of 5.
Sorry about the confusion, I hope that makes it more clear. Help will be greatly appreciated!
Re: use cell entry for column function, not cell coordinates
[bfn]=COLUMN(INDIRECT(M1))[/bfn]
Returns 5... but I probably misunderstand.
Re: use cell entry for column function, not cell coordinates
It works, that was nice and simple. Thank you very much for all your help, and yes sorry the initial explanations were confusing.
Don’t have an account yet? Register yourself now and be a part of our community!
