changing worksheet reference name when copying down cells
in [Functions]
|
Prev: Conditional Formatting - to highlight overwritten formulas
Next: EXTENSION OF EXCEL SHEET COLUMNS BEYOND IV GRID
From: ferne on 18 May 2010 22:03 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
From: ozgrid.com on 18 May 2010 23:52 Yes, list you worksheet names in any Column and use INDIRECT. -- Regards Dave Hawley www.ozgrid.com "ferne" <ferne(a)discussions.microsoft.com> wrote in message news:03F6D9ED-544F-48F9-8C54-3A558210DE2F(a)microsoft.com... >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
From: Jacob Skaria on 19 May 2010 02:46 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
From: Jacob Skaria on 19 May 2010 02:46 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
From: ferne on 19 May 2010 21:49
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 |