Hi,

CkBoy, to answer your question about the indirect function, I've put
together your attempt with Ioannis', try:

=INDIRECT("'sheet
2'!"&LEFT(ADDRESS(1,COLUMN(),4),IF(COLUMN()<27,1,2 )&ROW()))

NB: The single apostrphes on each side of the sheet name mean that the
formula will work if the sheet name has spaces in it.
The "sheet 2" can be replaced with the appropriate sheet name or if you
have a list of sheet names (eg the first one is in cell A2 of the active
sheet) try:

=INDIRECT("'"&A2&"'!"&LEFT(ADDRESS(1,COLUMN(),4),I F(COLUMN()<27,1,2)&ROW()))


btw, Stefi/Ioannis, yes, you're right it is interesting why the obvious
doesn't come up straight away - Thanks for showing me something new :-)

hth
Rob Brockett
NZ
always learning & the best way to learn is to experience...


--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=512119