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

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

Or, you could use R1C1 format, so that you are only dealing with
numbers.

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

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

=INDIRECT("R1C" & Column(),False)
will give you the same result though ADDRESS is a better and more flexible
solution.

"veryeavy" wrote:

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

You're welcome, Matt - thanks for feeding back.

I hope you enjoy your sunshine - it's freezing here, literally !!

Pete

On Jan 6, 1:52*am, veryeavy
wrote:
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()))

On Mon, 5 Jan 2009 17:19:01 -0800, veryeavy
wrote:

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.

It makes it clear that you don't WANT to "play around with dollar signs" but
not clear why =A$1 wouldn't work.

--ron

I did 15 winters in the UK so I am not surprised!!

"Pete_UK" wrote:

You're welcome, Matt - thanks for feeding back.

I hope you enjoy your sunshine - it's freezing here, literally !!

Pete

On Jan 6, 1:52 am, veryeavy
wrote:
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()))

Crikey - I've learnt a bit today - thanks Sheeloo, and Pete_UK also, for this
alternate method.

"Sheeloo" wrote:

=INDIRECT("R1C" & Column(),False)
will give you the same result though ADDRESS is a better and more flexible
solution.

"veryeavy" wrote:

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

I want to be able to copy this formula down and across.

"Ron Rosenfeld" wrote:

On Mon, 5 Jan 2009 17:19:01 -0800, veryeavy
wrote:

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.

It makes it clear that you don't WANT to "play around with dollar signs" but
not clear why =A$1 wouldn't work.

--ron

For the record my final fomula is this:

=IF(ISERROR(VLOOKUP(CONCATENATE(INDIRECT(ADDRESS(1 ,COLUMN())),$A2),JanPSGLData!$A:$J,10,0)),0,VLOOKU P(CONCATENATE(INDIRECT(ADDRESS(1,COLUMN())),$A2),J anPSGLData!$A:$J,10,0))

If this can in any way be "cleaned up" while still being able to be copied
downwards and sideways I will incorporate those enhancements.

Cheers All,

Matt

"Ron Rosenfeld" wrote:

On Mon, 5 Jan 2009 17:19:01 -0800, veryeavy
wrote:

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.

It makes it clear that you don't WANT to "play around with dollar signs" but
not clear why =A$1 wouldn't work.

--ron

And? What makes it impossible to copy Ron's formula down and across?
The only difference is that your formula can be moved, but you want to copy,
right?


"veryeavy" wrote in message
...
I want to be able to copy this formula down and across.

"Ron Rosenfeld" wrote:

On Mon, 5 Jan 2009 17:19:01 -0800, veryeavy
wrote:

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.

It makes it clear that you don't WANT to "play around with dollar signs"
but
not clear why =A$1 wouldn't work.

--ron

Yes my previous answer may well have been rubbish - maybe because each of my
column headers is a different date - is that the reason?

I am struggling today and apologise if I have wasted anyone's time - I
mainly try to condense my questions to only ask about the bit that I believe
I am stuck on but realise that sometimes describing the full scenario might
be more enlightening all around.

Cheers,

Matt

"Joerg Mochikun" wrote:

And? What makes it impossible to copy Ron's formula down and across?
The only difference is that your formula can be moved, but you want to copy,
right?


"veryeavy" wrote in message
...
I want to be able to copy this formula down and across.

"Ron Rosenfeld" wrote:

On Mon, 5 Jan 2009 17:19:01 -0800, veryeavy
wrote:

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.

It makes it clear that you don't WANT to "play around with dollar signs"
but
not clear why =A$1 wouldn't work.

--ron

On Mon, 5 Jan 2009 19:01:01 -0800, veryeavy
wrote:

I want to be able to copy this formula down and across.

If you copy it across it will refer to the successive columns:

A2: =A$1
B2: =B$1

If you copy it down it will refer to the first row, as you described:

A3: =A$1
B3: =B$1
--ron

On Mon, 5 Jan 2009 19:31:00 -0800, veryeavy
wrote:

For the record my final fomula is this:

=IF(ISERROR(VLOOKUP(CONCATENATE(INDIRECT(ADDRESS( 1,COLUMN())),$A2),JanPSGLData!$A:$J,10,0)),0,VLOOK UP(CONCATENATE(INDIRECT(ADDRESS(1,COLUMN())),$A2), JanPSGLData!$A:$J,10,0))

If this can in any way be "cleaned up" while still being able to be copied
downwards and sideways I will incorporate those enhancements.

Cheers All,

Matt

Try:

=IF(ISERROR(VLOOKUP(CONCATENATE(A$1,$A2),JanPSGLDa ta!$A:$J,10,0)),0,VLOOKUP(CONCATENATE(A$1,$A2),Jan PSGLData!$A:$J,10,0))

--ron

Thanks Ron,

Looking at it now in the cold hard light of my third day back at work after
my summer holiday I have no idea whatsoever why I thought I needed to
overcomplicated this so much. Sunstroke maybe?

I have incorporated your formula in the final workbook.

It was very tempting to leave the complicated version in my workbook and I
will keep this up my sleeve if I ever want to muddy the waters in the future!

Best Regards,

Matt

"Ron Rosenfeld" wrote:

On Mon, 5 Jan 2009 19:31:00 -0800, veryeavy
wrote:

For the record my final fomula is this:

=IF(ISERROR(VLOOKUP(CONCATENATE(INDIRECT(ADDRESS( 1,COLUMN())),$A2),JanPSGLData!$A:$J,10,0)),0,VLOOK UP(CONCATENATE(INDIRECT(ADDRESS(1,COLUMN())),$A2), JanPSGLData!$A:$J,10,0))

If this can in any way be "cleaned up" while still being able to be copied
downwards and sideways I will incorporate those enhancements.

Cheers All,

Matt

Try:

=IF(ISERROR(VLOOKUP(CONCATENATE(A$1,$A2),JanPSGLDa ta!$A:$J,10,0)),0,VLOOKUP(CONCATENATE(A$1,$A2),Jan PSGLData!$A:$J,10,0))

--ron

On Tue, 6 Jan 2009 14:26:00 -0800, veryeavy
wrote:

Thanks Ron,

Looking at it now in the cold hard light of my third day back at work after
my summer holiday I have no idea whatsoever why I thought I needed to
overcomplicated this so much. Sunstroke maybe?

I have incorporated your formula in the final workbook.

It was very tempting to leave the complicated version in my workbook and I
will keep this up my sleeve if I ever want to muddy the waters in the future!

Best Regards,

Matt


I've occasionally done the same thing -- an overly complicated solution to
something that can be solved quite simply.

But glad to help. Thanks for the feedback.
--ron