changing worksheet reference name when copying down cells
If your worksheets are named as Week1, Week2 etc; try the below and copy down
=INDIRECT("'Sheet" & ROW(A1) & "'!A1")
If a sheet do not exist this will return an error; to handle that use the
below version
=IF(ISREF(INDIRECT("'Sheet" & ROW(A1) & "'!A1")),
INDIRECT("'Sheet" & ROW(A1) & "'!A1"),"")
--
Jacob (MVP - Excel)
"ferne" wrote:
I have a yearly summary worksheet by week. Each week i add the weekly report
to the workbook. I need a formula on the summary sheet that will change the
worksheet name when I copy it down. Is there any way to do this? Would send
a copy of my workbook but nowhere to attach it.
--
ferne
|