changing worksheet reference name when copying down cells

F

ferne

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

Jacob Skaria

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"),"")
 
F

ferne

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

Thanks again!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

HLOOKUP into another xls file 1
Visio: copy text of a master shape to User-define Cells (Variable) 0
Dealing with a Pivot Table (when I don't want to) 0
Relative worksheet reference for multiple worksheets in a workbook 5
Auto Update worksheet references as new worksheets are added 1
MTD/YTD FUNCTION 2
Excel 2010 countifs for date range 3
Extracting data/using COUNTIFS 2

Top