Vasant,
I will try this out and let you know.
Thanks for the quick around.
On Fri, Jun 3, 2011 at 1:12 AM, Vasant <vasant...@gmail.com> wrote:
> There are many approaches of doing it.
>
> I have listed two here..
>
> Pass the month and year as parameters to the - 'UpdateFormula' subroutine.
> You can pass the parameters either through a worksheet change event or a
> userform.
>
> 'Trigger on worksheet change event in Memo sheet (all the workbooks has to
> be opened)
> '--------------------------------------------------
> Private Sub Worksheet_Change(ByVal Target As Range)
> If ThisWorkbook.Worksheets("Memo").Range("J2") <> "" And
> ThisWorkbook.Worksheets("Memo").Range("J3") <> "" Then
> Call UpdateFormula(ThisWorkbook.Worksheets("Memo").Range("J2"),
> ThisWorkbook.Worksheets("Memo").Range("J3"))
> End If
> End Sub
>
>
> Sub UpdateFormula(Mn, Yr)
> Flnm = "DDD_Report_" & Mn & "_" & Trim(Str(Yr)) & ".xls"
> ThisWorkbook.Worksheets("Memo").Range("F4").Formula =
> "=SUM(prod!J2:J9)-SUM('E:\[" & Flnm & "]prod'!$I$2:$I$9)"
> End Sub
>
>
>
> The other is : -
> this approach will work if the workbook names are standardized.
> ie. DDD_REPORT_APR2011.xls, DDD_REPORT_MAR2011.xls and so on.
>
> the below code gets the previous month file name using the current month
> workbook name and then updates the formula in the cell if the previous month
> file is available in the folder.
>
>
> Sub UpdateFormula2()
> Dim Flnm As String, Mn As String, Yr As Integer, PrevMn As String
> Dim FlPath As String, WkBk As Workbook, GetPrevMnthFile As String
> Flnm = ThisWorkbook.Name
> Mn = Mid(Flnm, 12, 3)
> Yr = Mid(Flnm, 15, 4)
> PrevMn = WorksheetFunction.Choose(Month(DateAdd("m", -1, DateValue("01-" &
> Mn & "-" & Yr))), "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug",
> "Sep", "Oct", "Nov", "Dec")
> If PrevMn = "Dec" Then
> Yr = Yr - 1
> End If
> GetPrevMnthFile = "DDD_REPORT_" & PrevMn & Trim(Str(Yr)) & ".xls"
> FlPath = ThisWorkbook.Path & "\" & GetPrevMnthFile
> If IsFileExists(FlPath) Then
> ThisWorkbook.Worksheets("Memo").Range("F4").Formula =
> "=SUM(prod!J2:J9)-SUM('" & ThisWorkbook.Path & "\[" & GetPrevMnthFile &
> "]prod'!$I$2:$I$9)"
> else
> msgbox "Previous Month File does not exist !"
> End If
> End Sub
>
> ' Function to check if file exists in the folder
> Function IsFileExists(Flnm
> As String) As Boolean
> On Error Resume Next
> If Not Dir(Flnm, vbDirectory) = vbNullString Then IsFileExists = True
> On Error GoTo 0
> End Function
>
> Hope this helps
>
> Regards
> Vasant
>
>
>
>
> On Thu, Jun 2, 2011 at 6:55 PM, Skanda <skanda.pokkun...@gmail.com>wrote:
>
>> Vasant,I'm seeking help to write a macro.
>>
>>
>> On Thu, Jun 2, 2011 at 12:23 AM, Vasant <vasant...@gmail.com> wrote:
>>
>>> A crude way of doing it is using the indirect function (though not
>>> advisable), on condition that the file name is updated in C13 cell and file
>>> is open.
>>> Generally Indirect is not used outside a workbook
>>>
>>> =SUM(prod!J2:J9)-SUM(INDIRECT("["&C13&"]prod!$I$2:$I$9"))
>>>
>>> The other way round is writing a macro.
>>>
>>>
>>>
>>> On Wed, Jun 1, 2011 at 7:00 PM, Skanda <skanda.pokkun...@gmail.com>wrote:
>>>
>>>> vasant everymonth we have to change the file name.i.e MAR_2011
>>>>
>>>> On Wed, Jun 1, 2011 at 4:58 AM, Vasant <vasant...@gmail.com> wrote:
>>>>
>>>>> Hi Skanda,
>>>>>
>>>>>
>>>>> =SUM(prod!J2:J9)-SUM([DDD_Report_Mar_2011.xls]prod!$I$2:$I$9)
>>>>>
>>>>> Hope this helps
>>>>>
>>>>> Regards
>>>>> Vasant
>>>>>
>>>>> On Wed, Jun 1, 2011 at 12:15 AM, Skanda <skanda.pokkun...@gmail.com>wrote:
>>>>>
>>>>>> There are two excel workbooks:DDD_REPORT_APR2011.xls and
>>>>>> DDD_REPORT_MAR2011.xls.
>>>>>>
>>>>>> In the DDD_REPORT_APR2011.xls, how to calculate the value for cell F4
>>>>>> in memo worksheet of :DDD_REPORT_APR2011.xls calculated as sum(J column)
>>>>>> minus sum(i Column) from prod worksheet minus sum(i2:i9) of prod
>>>>>> sheet from DDD_REPORT_MAR2011.xls.
>>>>>>
>>>>>>
>>>>>>
>>>>>> This is a done every month so the file names have to be dynamic.
>>>>>>
>>>>>> --
>>>>>>
>>>>>> ----------------------------------------------------------------------------------
>>>>>> Some important links for excel users:
>>>>>> 1. Follow us on TWITTER for tips tricks and links :
>>>>>> http://twitter.com/exceldailytip
>>>>>> 2. Join our LinkedIN group @
>>>>>> http://www.linkedin.com/groups?gid=1871310
>>>>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>>>>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>>>>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>>>>>
>>>>>> To post to this group, send email to excel-macros@googlegroups.com
>>>>>>
>>>>>> <><><><><><><><><><><><><><><><><><><><><><>
>>>>>> Like our page on facebook , Just follow below link
>>>>>> http://www.facebook.com/discussexcel
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Regards
>>>>>
>>>>> Vasant
>>>>>
>>>>> --
>>>>>
>>>>> ----------------------------------------------------------------------------------
>>>>> Some important links for excel users:
>>>>> 1. Follow us on TWITTER for tips tricks and links :
>>>>> http://twitter.com/exceldailytip
>>>>> 2. Join our LinkedIN group @
>>>>> http://www.linkedin.com/groups?gid=1871310
>>>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>>>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>>>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>>>>
>>>>> To post to this group, send email to excel-macros@googlegroups.com
>>>>>
>>>>> <><><><><><><><><><><><><><><><><><><><><><>
>>>>> Like our page on facebook , Just follow below link
>>>>> http://www.facebook.com/discussexcel
>>>>>
>>>>
>>>> --
>>>>
>>>> ----------------------------------------------------------------------------------
>>>> Some important links for excel users:
>>>> 1. Follow us on TWITTER for tips tricks and links :
>>>> http://twitter.com/exceldailytip
>>>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>>>
>>>> To post to this group, send email to excel-macros@googlegroups.com
>>>>
>>>> <><><><><><><><><><><><><><><><><><><><><><>
>>>> Like our page on facebook , Just follow below link
>>>> http://www.facebook.com/discussexcel
>>>>
>>>
>>>
>>>
>>> --
>>> Regards
>>>
>>> Vasant
>>>
>>> --
>>>
>>> ----------------------------------------------------------------------------------
>>> Some important links for excel users:
>>> 1. Follow us on TWITTER for tips tricks and links :
>>> http://twitter.com/exceldailytip
>>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>>
>>> To post to this group, send email to excel-macros@googlegroups.com
>>>
>>> <><><><><><><><><><><><><><><><><><><><><><>
>>> Like our page on facebook , Just follow below link
>>> http://www.facebook.com/discussexcel
>>>
>>
>> --
>>
>> ----------------------------------------------------------------------------------
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel
>>
>
>
>
> --
> Regards
>
> Vasant
>
> --
>
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
