Converting RC cell refs to cell range refs

  • Thread starter Thread starter David Ralph
  • Start date Start date
D

David Ralph

Is there a function built into Excel that returns the alphabetic column
reference if you provide the numeric column reference and visa versa?

Currently I am using a seperate hidden sheet listing all the alphabetic
column references down column A.
Then returning the value for Range("A & v_column_no). This is obviously not
ideal but has worked for the short term.


Many thanks

Dave Ralph
 
David Ralph said:
Is there a function built into Excel that returns the alphabetic column
reference if you provide the numeric column reference and visa versa?

Currently I am using a seperate hidden sheet listing all the alphabetic
column references down column A.
Then returning the value for Range("A & v_column_no). This is obviously not
ideal but has worked for the short term.


Many thanks

Dave Ralph
Click to expand...

In most situations, it is better NOT to go via the column letter. You can
build a reference to a single cell, or a range of cells, directly from row
and column numbers using the OFFSET function. For example, if you have the
number 4 (meaning row 4) in A1 and the number 5 (meaning column E) in B1,
you build the cell reference E4 using
=OFFSET($A$1,A1-1,B1-1)
The full syntax is
OFFSET(reference,rows,cols,height,width)

If you have difficulties, post back with details of what you are trying to
achieve.
 
With your numeric code reference in A1

=LEFT(ADDRESS(1,A1,2),1+(A1>26))

from a previous post of Chip Pearson's
 
Back
Top