date:20110603

$$Excel-Macros$$ Find value in Active Cell in another sheet(s) & delete

2011-06-03 Thread sswcharlie

Hi

Part of a another code -

I want to find the value(text) in active cell (value changes often) in
workbook (except current WS and one other worksheet 'home") and
delete. Could be more than one instance.


Thanks

Charlie

-- 
--
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$$ Hyperlink for Sheet in a Cell - changes

2011-06-03 Thread sswcharlie

Hi

Is it possible to have say a cell with hyperlink to sheet (that is ok)
but also if the name in that cell changes it stays as a hyperlink to a
another sheet.

eg   if A1 has 'Sheet2' hyperlinked, and then A1 changes to 'sheet3'
will the hyperlink change to sheet 3 ?

Thanks


Charlie

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

2011-06-03 Thread jmothilal

Congratulations!  , J.Mothilal, Vellore

On Fri, Jun 3, 2011 at 4:04 PM, SANJAY SONI wrote:

> Dear Ashish G,
>
> Congratulations on a great success. It is really appreciable for winning
> consecutive 6 times as "MOST HELPFUL MEMBER".
>
> *
> Best Regards,
> *
> *Sanjay **
>  *
> **
>  *From:* Ayush Jain 
> *To:* excel-macros 
> *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
>
>
>  --
>
> --
> 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
>



-- 
J.Mohilal
Universal Computer Systems
# 16, Brindavan Complex
Otteri, Vellore-2

-- 
--
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$$ Formula Needed to Extract Data

2011-06-03 Thread ashish koul

see if it helps

On Wed, Jun 1, 2011 at 7:07 PM, John A. Smith wrote:

> 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.posterous.com/
*akoul*.wordpress.com 
My Linkedin Profile 


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

Re: $$Excel-Macros$$ Excel File icon changed.. wanted to retrive

2011-06-03 Thread Kiran Kancharla

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

Re: $$Excel-Macros$$ Dynamic file name and sum

2011-06-03 Thread Skanda

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  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 wrote:
>
>> Vasant,I'm seeking help to write a macro.
>>
>>
>> On Thu, Jun 2, 2011 at 12:23 AM, Vasant  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 wrote:
>>>
 vasant everymonth we have to change the file name.i.e MAR_2011

 On Wed, Jun 1, 2011 at 4:58 AM, Vasant  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 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/ex

Re: $$Excel-Macros$$ Ashish Koul : Most Helpful Member - May'11

2011-06-03 Thread Kal xcel

once again congratulation ashish...keep it up

Kalyan

On Fri, Jun 3, 2011 at 3:54 PM, NOORAIN ANSARI wrote:

> Congrate Sir ji...lage raho...
>
>
> On Thu, Jun 2, 2011 at 10:29 PM, Ayush Jain  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$$ Ashish Koul : Most Helpful Member - May'11

2011-06-03 Thread SANJAY SONI

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 
To: excel-macros 
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

$$Excel-Macros$$ Excel File icon changed.. wanted to retrive

2011-06-03 Thread Anil Bhange

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

Re: $$Excel-Macros$$ Ashish Koul : Most Helpful Member - May'11

2011-06-03 Thread Manoj kumar

Congrats Ashish, keep it up.



Best,
Manoj Kumar

On Thu, Jun 2, 2011 at 10:29 PM, Ayush Jain  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

Re: $$Excel-Macros$$ Dynamic file name and sum

2011-06-03 Thread Vasant

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  wrote:

> Vasant,I'm seeking help to write a macro.
>
>
> On Thu, Jun 2, 2011 at 12:23 AM, Vasant  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 wrote:
>>
>>> vasant everymonth we have to change the file name.i.e MAR_2011
>>>
>>> On Wed, Jun 1, 2011 at 4:58 AM, Vasant  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 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@go

Re: $$Excel-Macros$$ Ashish Koul : Most Helpful Member - May'11

2011-06-03 Thread NOORAIN ANSARI

Congrate Sir ji...lage raho...

On Thu, Jun 2, 2011 at 10:29 PM, Ayush Jain  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

$$Excel-Macros$$ Find value in Active Cell in another sheet(s) & delete

$$Excel-Macros$$ Hyperlink for Sheet in a Cell - changes

Re: $$Excel-Macros$$ Ashish Koul : Most Helpful Member - May'11

Re: $$Excel-Macros$$ Formula Needed to Extract Data

Re: $$Excel-Macros$$ Excel File icon changed.. wanted to retrive

Re: $$Excel-Macros$$ Dynamic file name and sum

Re: $$Excel-Macros$$ Ashish Koul : Most Helpful Member - May'11

Re: $$Excel-Macros$$ Ashish Koul : Most Helpful Member - May'11

$$Excel-Macros$$ Excel File icon changed.. wanted to retrive

Re: $$Excel-Macros$$ Ashish Koul : Most Helpful Member - May'11

Re: $$Excel-Macros$$ Dynamic file name and sum

Re: $$Excel-Macros$$ Ashish Koul : Most Helpful Member - May'11

12 matches

Site Navigation

Mail list logo

Footer information