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
Relative worksheet reference for multiple worksheets in a workbook 5
Excel Count If Formula Question 1
Reference other worksheet 4
Auto Update worksheet references as new worksheets are added 1
Modifying only the worksheet when draging down a cell reference 20
Excel Copy cells from one worksheet to another workseet - increment row & do again 0
Absolute Sheet Reference 3

Top