I made use of a function to do this a while back. If you put the following in a standard module:
Code:
Function SHEETOFFSET(offset)
Application.Volatile
SHEETOFFSET = Sheets(Application.Caller.Parent.Index _
+ offset).Name
End Function
Now, on the first sheet, if your first sheet is going in row 1 then down, use the following formula:
=IF(ISERR(sheetoffset(ROW()-1)=TRUE),"",sheetoffset(ROW()-1))
and copy down
If it's in row 2:
=IF(ISERR(sheetoffset(ROW()-2)=TRUE),"",sheetoffset(ROW()-2))
and copy down
etc......It will show "" if there's no more sheets to show.
Incidentally, if you want to copy accross instead of down switch "row" with "column" in the worksheet formulas.
A different way to pluck a duck.
_________________
Cheers,<font size=+2><font color="red">
Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-04-26 15:57