Column() question

[[email protected]]

How can I convert the alpha designation of a column to the number
for example, if I know that the column is E then the column number is 5
etc.

basically what I want to do is this:

=column()-column(E1) so that I can subtract column 5 from the current
column
That formula works as is. However, what I want to do is to put the
column letter in row 2 and have the formula calcuate the difference.

for example, if the current column is G then =column()-column(E1)
evaluates to 3 (which I use in another formula)
if I put E into row2 of column G, is there a way to stuff the "E1" in
column()? Thus if I change the value in G2 to F, then the formula
would be =column()-column(F1) (which evaluates to 2)

Possible?

Glen

[Zack Barresse]

Hi there Glen,

Check out the INDIRECT function. An example would look like this ....

=COLUMN()-COLUMN(INDIRECT(E2&"2"))

HTH

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM


<[email protected]> wrote in message
news:[email protected]...
> How can I convert the alpha designation of a column to the number
> for example, if I know that the column is E then the column number is 5
> etc.
>
> basically what I want to do is this:
>
> =column()-column(E1) so that I can subtract column 5 from the current
> column
> That formula works as is. However, what I want to do is to put the
> column letter in row 2 and have the formula calcuate the difference.
>
> for example, if the current column is G then =column()-column(E1)
> evaluates to 3 (which I use in another formula)
> if I put E into row2 of column G, is there a way to stuff the "E1" in
> column()? Thus if I change the value in G2 to F, then the formula
> would be =column()-column(F1) (which evaluates to 2)
>
> Possible?
>
> Glen
>

[Peo Sjoblom]

=COLUMN()-COLUMN(INDIRECT(G2&":"&G2))


--

Regards,

Peo Sjoblom


<[email protected]> wrote in message
news:[email protected]...
> How can I convert the alpha designation of a column to the number
> for example, if I know that the column is E then the column number is 5
> etc.
>
> basically what I want to do is this:
>
> =column()-column(E1) so that I can subtract column 5 from the current
> column
> That formula works as is. However, what I want to do is to put the
> column letter in row 2 and have the formula calcuate the difference.
>
> for example, if the current column is G then =column()-column(E1)
> evaluates to 3 (which I use in another formula)
> if I put E into row2 of column G, is there a way to stuff the "E1" in
> column()? Thus if I change the value in G2 to F, then the formula
> would be =column()-column(F1) (which evaluates to 2)
>
> Possible?
>
> Glen
>

[[email protected]]

Works like a champ! Thanks.

Glen