using INDIRECT with COLUMN fails.. I know why

[MDBJ]

I had a huge spreadsheet that I assembled by the end of the summer.

at one point, it pulled data from one worksheet to the next using an
INDIRECT combined with ROW()


now- at the end of the summer, I'm re-writing it- but for display purposes,
I find it works better vertically where before it was horizontal.

I'm stuck, I know where..

before, my indirect("sheetname!"&$A1&ROW()) worked fine if A1 had a letter

so I might get an address of "sheetname!d1"

now, my indirect("sheetname!"&colum()&a$1) fails-- as column() returns a
number not letter...

now I get an address of "sheetname!14"

where the first '1' of the 14 should be the letter a-followed by row 4

get this? am I splaining it well?
how might I get "from this same column" into indirect as a letter?

 

[Dave Peterson]

The second argument of the =indirect() function is a true/false that tells excel
that you want to specify A1 reference style (True or omitted)--or that you want
to specify R1C1 reference style (False).

I'm not quite sure what you're doing, but maybe you could use something like:

=INDIRECT("'sheetname'!"&"R"&ROW()&"C"&A1,FALSE)

where A1 contains a number.

 

[MDBJ]

Thanks... I was trying that-- it kept failing.. I figured a method out,
and it's just darn ugly
some of them were negative offsets- and those kept erroring out, I found
this finaly.

INDIRECT("mysheet'!"&"r["&$A5-ROW()&"]"&"c"&COLUMN(),FALSE)

which is just ugly.. but inserts the required brackets to cover me for the
negatives.....AAACK!

I hate writing stuff like this, as when I go to review it later I have no
idea how it worked and have to 'relearn' what I wrote again...

 

[ShaneDevenshire]

Hi,

If you show us your current data layout and what you want we mght be able to
come up with something better.


--
Thanks,
Shane Devenshire

Thanks... I was trying that-- it kept failing.. I figured a method out,
and it's just darn ugly
some of them were negative offsets- and those kept erroring out, I found
this finaly.

INDIRECT("mysheet'!"&"r["&$A5-ROW()&"]"&"c"&COLUMN(),FALSE)

which is just ugly.. but inserts the required brackets to cover me for the
negatives.....AAACK!

I hate writing stuff like this, as when I go to review it later I have no
idea how it worked and have to 'relearn' what I wrote again...

 

[Roger Govier]

Hi

You could set up a formula as a defined name, which returns the column
letter, then use this name in your indirect formula.
Insert>Name>Define>
Name Colm
Refers to =SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")

Then your formula would be
= indirect("sheetname!"&Colm&a$1)

 

[Harlan Grove]

I had a huge spreadsheet that I assembled by the end of the summer. ....
now, my indirect("sheetname!"&colum()&a$1) fails-- . . .

....

So why use INDIRECT? If sheetname is the name of another worksheet in
the same file, and A1 is necessarily a row number, simpler and more
efficient to use INDEX.

INDEX('sheetname'!$1:$65536,A$1,COLUMN())