Many thanks Pete - several months of Sundays would have elapsed before I
stumbled on this.

In case there are any interested bystanders the formula now looks like:

=INDIRECT(ADDRESS(1,COLUMN()))

"Pete_UK" wrote:

Take a look at the ADDRESS function in Excel Help.

Hope this helps.

Pete

On Jan 6, 1:19 am, veryeavy
wrote:
Hi All and Happy New Year,

I want to write a formula like:

=INDIRECT(COLUMN()&"1")

but the obvious drawback is that column returns a numeric and not an alpha
reference.

The reason I want this is have a formula that can be copied down and across
that refers to the top cell in each column to which is copied.

Hopefully my header line makes it clear that playing around with "$" signs
is not the answer.

I could put in a table with 1 to 26 and A to Z in it and run a Vlookup over
it but that just seems too OTT.

Probably missing the bleeding obvious again - that and 2.5 weeks off with
primo SI NZ sunshine (and beer) rotting my brain.

Cheers,

Matt