# How do I copy a worksheet name reference with embedded date and have date increment?

Hello Experts.  I need your help again.

I have this formula in cell A1 and I want to copy it down for each day of the month:
=SUBTOTAL(9,'Z:\Production Reports\[PR 01-2011.xls]11-01-01'!G\$11:G\$600)

Cell A2 should look like this:
=SUBTOTAL(9,'Z:\Production Reports\[PR 01-2011.xls]11-01-02'!G\$11:G\$600)

My source workbook name is:  PR 01-2011.xls
Each worksheet name represents a date, eg.:  11-01-02

You guys are so good at knowing how to save time/effort by writing formulas that can be copied.  Can you help me with this one?

Thanks,
Gary
###### Who is Participating?

Commented:
Is this what you want?

``````Sub Sample()
Dim LastRow As Long
Dim strFormula As String

'~~> To get the lastrow. Amend as applicable
LastRow = Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row

For i = 2 To LastRow
'=SUBTOTAL(9,'Z:\Production Reports\[PR 01-2011.xls]11-01-01'!G\$11:G\$600)
strFormula = "=SUBTOTAL(9,'Z:\Production Reports\[PR 01-2011.xls]11-01-" & _
Format(i - 1, "00") & "'!G\$11:G\$600)"

Sheets("Sheet1").Range("A" & i).Formula = strFormula
Next
End Sub
``````

Sid
0

Author Commented:
I'm not very comfortable yet with VBA.  Is there a way I can do this with the formula?

Gary
0

Commented:
UNTESTED

Yes there is. Insert a new column in B and type the following

In cell B1
1
In cell B2
2
In cell B3
3
and so on...

Type this formula in A1

=SUBTOTAL(9,INDIRECT("'Z:\Production Reports\[PR 01-2011.xls]11-01-"&TEXT(B1,"00")&"'!G\$11:G\$600"))

AND then pull the formula down.

Sid
0

I think i should work without need for extra column with this change

=SUBTOTAL(9,INDIRECT("'Z:\Production Reports\[PR 01-2011.xls]11-01-"&TEXT(ROW()-1,"00")&"'!G\$11:G\$600"))

Kind regards

Eric
0

Author Commented:
SiddharthRout,

Is there a way of referencing cells in column B by cell address instead of using the INDIRECT & TEXT functions (they require that the source workbook open to run or update)?

Gary
0

Commented:
No only the INDIRECT requires the source workbook to be open.

I would suggest you the VBA method then. I can explain how it works if you want? It is very easy :)

Sid
0

Author Commented:
SiddharthRout,

Ok, I'm ready to explore the VBA approach.  I've studied the basics of VBA and am anxious to get an actual project working.

Gary
0

Commented:
Did you see the first post? You need to paste that in a module. Do you have a sample file so that I can explain it step by step?

Sid
0

Author Commented:
SiddharthRout:

Thanks for responding so promptly.  I can really use your help on this.

Attached is a file which I use to collect data fields from other large reports that are produced daily.  This report simply uses links to these reports.  I would like to be able to drag the bottom row and have the link references increment as required without having to manually edit them.

As you can see, I have tried the INDIRECT/TEXT solutions already.

I’m looking forward to learning more about VBA in the process.

Gary

PCS-Facility-03-2011-EE.xls
0

Author Commented:
I'm sorry for the delay in responding to this solution.  I would like to award points now.

Gary
0

Author Commented:
SiddharthRout:  Thanks for the prompt response.  Your VBA solution seems to be the only way to solve this problem.  I am not yet comfortable with VBA but I'm studying it and will implement later.  Thanks.

Gary
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.