Re: $$Excel-Macros$$ Ashish Koul : Most Helpful Member - May'11
Congrate Sir ji...lage raho... On Thu, Jun 2, 2011 at 10:29 PM, Ayush Jain jainayus...@gmail.com wrote: Hello Everyone, Ashish Koul has been selected as 'Most Helpful Member' for the month of May'11 He has posted 67 posts in May 2011 and helped many people through his expertise. He has been consistent contributor to this excel forum and achieved this recognition from last six months consecutively.This is really awesome. ASHISH, Many Many thanks for the valuable support to group. We are proud to have you in this forum. *I have published the same on discussexcel.com homepage.* Regards Ayush Jain Group Manager -- -- 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 -- Thanks regards, Noorain Ansari -- -- 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
Re: $$Excel-Macros$$ Dynamic file name and sum
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.comwrote:
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.comwrote:
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 @
Re: $$Excel-Macros$$ Ashish Koul : Most Helpful Member - May'11
Congrats Ashish, keep it up. Best, Manoj Kumar On Thu, Jun 2, 2011 at 10:29 PM, Ayush Jain jainayus...@gmail.com wrote: Hello Everyone, Ashish Koul has been selected as 'Most Helpful Member' for the month of May'11 He has posted 67 posts in May 2011 and helped many people through his expertise. He has been consistent contributor to this excel forum and achieved this recognition from last six months consecutively.This is really awesome. ASHISH, Many Many thanks for the valuable support to group. We are proud to have you in this forum. *I have published the same on discussexcel.com homepage.* Regards Ayush Jain Group Manager -- -- 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
$$Excel-Macros$$ Excel File icon changed.. wanted to retrive
Hi members, My excel file icon got changed and shows the internal data instead of standard excel image [cid:image001.jpg@01CC2201.6DA84E00] [cid:image002.jpg@01CC2201.6DA84E00] I wanted the standard excel image as my file view and not the internal data image... please help Thanks Regards, Anil Bhange IP - 512320 -- -- 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 inline: image001.jpginline: image002.jpg
Re: $$Excel-Macros$$ Ashish Koul : Most Helpful Member - May'11
Dear Ashish G, Congratulations on a great success. It is really appreciable for winning consecutive 6 times as MOST HELPFUL MEMBER. Sanjay From: Ayush Jain jainayus...@gmail.com To: excel-macros excel-macros@googlegroups.com Sent: Thursday, 2 June 2011 10:29 PM Subject: $$Excel-Macros$$ Ashish Koul : Most Helpful Member - May'11 Hello Everyone, Ashish Koul has been selected as 'Most Helpful Member' for the month of May'11 He has posted 67 posts in May 2011 and helped many people through his expertise. He has been consistent contributor to this excel forum and achieved this recognition from last six months consecutively.This is really awesome. ASHISH, Many Many thanks for the valuable support to group. We are proud to have you in this forum. I have published the same on discussexcel.com homepage. Regards Ayush Jain Group Manager -- -- 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 Best Regards, -- -- 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
Re: $$Excel-Macros$$ Ashish Koul : Most Helpful Member - May'11
once again congratulation ashish...keep it up Kalyan On Fri, Jun 3, 2011 at 3:54 PM, NOORAIN ANSARI noorain.ans...@gmail.comwrote: Congrate Sir ji...lage raho... On Thu, Jun 2, 2011 at 10:29 PM, Ayush Jain jainayus...@gmail.com wrote: Hello Everyone, Ashish Koul has been selected as 'Most Helpful Member' for the month of May'11 He has posted 67 posts in May 2011 and helped many people through his expertise. He has been consistent contributor to this excel forum and achieved this recognition from last six months consecutively.This is really awesome. ASHISH, Many Many thanks for the valuable support to group. We are proud to have you in this forum. *I have published the same on discussexcel.com homepage.* Regards Ayush Jain Group Manager -- -- 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 -- Thanks regards, Noorain Ansari -- -- 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
Re: $$Excel-Macros$$ Dynamic file name and sum
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.comwrote:
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.comwrote:
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.comwrote:
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
--
Re: $$Excel-Macros$$ Excel File icon changed.. wanted to retrive
Hi Anil, Follow the below steps. Open the file click the below following steps File Properties - Summary - and Uncheck the Save Preview Picture Regards, Kiran On Fri, Jun 3, 2011 at 3:18 PM, Anil Bhange anil.bha...@tatacommunications.com wrote: Hi members, My excel file icon got changed and shows the internal data instead of standard excel image I wanted the standard excel image as my file view and not the internal data image… please help Thanks Regards, Anil Bhange IP - 512320 -- -- 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 -- Thanks Regards, Kiran 9920456606 -- -- 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 image001.jpgimage002.jpg
Re: $$Excel-Macros$$ Formula Needed to Extract Data
see if it helps On Wed, Jun 1, 2011 at 7:07 PM, John A. Smith johnasmit...@gmail.comwrote: Need help please extracting specific data from an Excel spreadsheet into an Excel report. The arrangement of the data isn't always in the same order as shown in the attached sample. Thank you again for your kind help. John -- -- 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* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ http://akoul.posterous.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. -- -- 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 Copy of Formula Needed to Extract Data from Excel.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
