Something other than Absolute, Relative or Mixed references

[veryeavy]

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

 

[Pete_UK]

Take a look at the ADDRESS function in Excel Help.

Hope this helps.

Pete

 

[Pete_UK]

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

Hope this helps.

Pete

 

[veryeavy]

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()))

 

[Sheeloo]

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

 

[Pete_UK]

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

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

Pete

 

[Ron Rosenfeld]

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

 

[veryeavy]

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

 

[veryeavy]

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

 

[veryeavy]

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

 

[veryeavy]

For the record my final fomula is this:

=IF(ISERROR(VLOOKUP(CONCATENATE(INDIRECT(ADDRESS(1,COLUMN())),$A2),JanPSGLData!$A:$J,10,0)),0,VLOOKUP(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

 

[Joerg Mochikun]

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]

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

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

For the record my final fomula is this:

=IF(ISERROR(VLOOKUP(CONCATENATE(INDIRECT(ADDRESS(1,COLUMN())),$A2),JanPSGLData!$A:$J,10,0)),0,VLOOKUP(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),JanPSGLData!$A:$J,10,0)),0,VLOOKUP(CONCATENATE(A$1,$A2),JanPSGLData!$A:$J,10,0))

--ron

 

[veryeavy]

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]

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