I have a number of spreadsheets, with over 100 columns in each. I am
peforming a data mapping exercise and have to create a word document with a
table that has 3 columns. The first column in the table needs to be the
column ref from the excel spread sheet ie A, B etc. If I insert an new row
above the existing data in excel how can I easily add the column name (A, B)
to each row. I have tried using series fill and the CELL function, without
much luck.

Please advise

=SUBSTITUTE((LEFT(ADDRESS(1,COLUMN()),3)),"$","")


will return A in column A
will return AA in column AA
etc.
--
Gary''s Student - gsnu200728


"Richard" wrote:

I have a number of spreadsheets, with over 100 columns in each. I am
peforming a data mapping exercise and have to create a word document with a
table that has 3 columns. The first column in the table needs to be the
column ref from the excel spread sheet ie A, B etc. If I insert an new row
above the existing data in excel how can I easily add the column name (A, B)
to each row. I have tried using series fill and the CELL function, without
much luck.

Please advise

have also found that =MID(ADDRESS(1,COLUMN()),2,1) works

"Richard" wrote:

I have a number of spreadsheets, with over 100 columns in each. I am
peforming a data mapping exercise and have to create a word document with a
table that has 3 columns. The first column in the table needs to be the
column ref from the excel spread sheet ie A, B etc. If I insert an new row
above the existing data in excel how can I easily add the column name (A, B)
to each row. I have tried using series fill and the CELL function, without
much luck.

Please advise

Hi Richard

If you have over 100 columns as you say in your original post, then this
formula will be inaccurate once you get
past column 26, column "Z"
Your formula will return "A" instead of "AA"

Better to stick with Gary's suggestion.

--
Regards

Roger Govier


"Richard" wrote in message
...
have also found that =MID(ADDRESS(1,COLUMN()),2,1) works

"Richard" wrote:

I have a number of spreadsheets, with over 100 columns in each. I am
peforming a data mapping exercise and have to create a word document
with a
table that has 3 columns. The first column in the table needs to be
the
column ref from the excel spread sheet ie A, B etc. If I insert an
new row
above the existing data in excel how can I easily add the column name
(A, B)
to each row. I have tried using series fill and the CELL function,
without
much luck.

Please advise

A tiny warning.

A modification may be needed if you transition to Excel 2007 and have col
ids of three characters.
--
Gary''s Student - gsnu200728


"Richard" wrote:

have also found that =MID(ADDRESS(1,COLUMN()),2,1) works

"Richard" wrote:

I have a number of spreadsheets, with over 100 columns in each. I am
peforming a data mapping exercise and have to create a word document with a
table that has 3 columns. The first column in the table needs to be the
column ref from the excel spread sheet ie A, B etc. If I insert an new row
above the existing data in excel how can I easily add the column name (A, B)
to each row. I have tried using series fill and the CELL function, without
much luck.

Please advise