changing worksheet reference name when copying down cells
 

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

changing worksheet reference name when copying down cells
 

Yes, list you worksheet names in any Column and use INDIRECT.



--
Regards
Dave Hawley
www.ozgrid.com
"ferne" wrote in message
...
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

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

changing worksheet reference name when copying down cells
 

Rename 'Sheet' to 'Week'

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

changing worksheet reference name when copying down cells
 

Thanks so much for your help. I have just started using more formulas but
couldn't put this together.

Thanks again!
--
ferne


"Jacob Skaria" wrote:

Rename 'Sheet' to 'Week'

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

changing worksheet reference name when copying down cells
 

Thanks for your help
--
ferne


"ozgrid.com" wrote:

Yes, list you worksheet names in any Column and use INDIRECT.



--
Regards
Dave Hawley
www.ozgrid.com
"ferne" wrote in message
...
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

.