the entire range reference
including the name of the sheet, same as above.
Attaching a sample for reference.
Regards,
Sam Mathai Chacko (GL)
On Fri, Oct 14, 2011 at 11:11 PM, Alexander Cargill
zandercatz1...@gmail.com wrote:
hi there have cells on another sheet of my workbook
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
--
Sam Mathai Chacko
the attachment.
Have used the BeforeRightClick event, as well as the DoubleClick event.
Either one event should trigger the macro, which means you don't have to
select another cell and come back for the macro to trigger.
Regards,
Sam Mathai Chacko (GL)
On Sat, Oct 15, 2011 at 3:32 AM, Cab Boose swch
Another faster method would be
=Sheet1!$A$1:INDEX(Sheet1!$A$1:$A$1000,COUNTA(Sheet1!$A$1:$A$1000))
OR for a more expanded range
=Sheet1!$A$1:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))
Regards,
Sam Mathai Chacko (GL)
On Thu, Oct 13, 2011 at 8:17 AM, rajan verma rajanverma1...@gmail.comwrote
Use this formula based conditional in your Sheet 2 column A
=ISNUMBER(MATCH(INDIRECT(RC,FALSE),Holiday,0))
Regards,
Sam Mathai Chacko
On Thu, Oct 13, 2011 at 6:09 AM, Atul atul_pate...@yahoo.com wrote:
Hi,
I need your help on using conditional formatting for a cell contaning
Oops, forgot to write Holiday*s*
=ISNUMBER(MATCH(INDIRECT(RC,FALSE),Holiday*s*,0))
Sam
On Thu, Oct 13, 2011 at 11:55 AM, Sam Mathai Chacko samde...@gmail.comwrote:
Use this formula based conditional in your Sheet 2 column A
=ISNUMBER(MATCH(INDIRECT(RC,FALSE),Holidays,0))
Regards,
Sam
Fortunately, there are smaller formulas. Here's one
=INDEX($C$2:$C$19,SUMPRODUCT(MATCH($E2$D2,$B$2:$B$19$A$2:$A$19,1)))
Here's another one that uses array
=INDEX($C$2:$C$19,MATCH($E2$D2,$B$2:$B$19$A$2:$A$19,1))
Regards,
Sam Mathai Chacko (GL)
On Thu, Oct 13, 2011 at 1:06 PM, B Sharma sharma
Mathai Chacko
--
--
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
Here's the formula, where your dates start from A2 to A7
=SUMPRODUCT(MAX(($A$2:$A$7=B2)*($A$2:$A$7)))
Regards
Sam Mathai Chacko
On Wed, Oct 12, 2011 at 5:45 PM, B Sharma sharma@gmail.com wrote:
Hi Sanjib ... can you please send me your email id so that i can
send you a sample file
With
End If
Next lngLoop
With Application
.Goto Sheets(1).Cells(1)
.ScreenUpdating = True
End With
End Sub
Regards,
Sam Mathai Chacko (GL)
On Wed, Oct 12, 2011 at 9:03 PM, Divaker Pandey divake...@gmail.com wrote:
it is so easy, if you use hyperlink instead of button
, and On Error GoTo -1 disables the enabled
exception. For more info on Error Handling, check
http://msdn.microsoft.com/en-us/library/5hsw66as%28v=vs.80%29.aspx
Regards,
Sam Mathai Chacko (GL)
On Tue, Oct 11, 2011 at 5:37 PM, Monizri moni...@gmail.com wrote:
Hi Sam, I am new to VBA and have been trying
formula, try this
=IF(ISNUMBER(A2),A2,
DATE(RIGHT(A2,4),SUBSTITUTE(MID(A2,SEARCH(/,A2)+1,2),/,),SUBSTITUTE(LEFT(A2,2),/,))
)
Regards,
Sam Mathai Chacko (GL)
On Tue, Oct 11, 2011 at 9:01 PM, NOORAIN ANSARI noorain.ans...@gmail.comwrote:
Dear Suman,
if you interested to use VBA then you can use
Mathai Chacko
--
--
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
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1:B1)))=1))
Where A1 and B1 are your start dates, and 1=Sunday, 2=Monday, 3=Tuesday etc
Regards,
Sam Mathai Chacko (GL)
On Mon, Oct 10, 2011 at 10:57 PM, RockyFontane vtorral...@gmail.com wrote:
I am trying to count the number of Sunday's between two date
,
Sam
On Mon, Oct 10, 2011 at 11:17 PM, Sam Mathai Chacko samde...@gmail.comwrote:
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1:B1)))=1))
Where A1 and B1 are your start dates, and 1=Sunday, 2=Monday, 3=Tuesday
etc
Regards,
Sam Mathai Chacko (GL)
On Mon, Oct 10, 2011 at 10:57 PM, RockyFontane
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(C3:D3)),1)6))
--
Thanks regards,
Noorain Ansari
*http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/
*http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/
On Mon, Oct 10, 2011 at 11:39 PM, Sam Mathai Chacko
- 4 service records copied to the service
report.
End Sub
On Oct 8, 12:17 am, Sam Mathai Chacko samde...@gmail.com wrote:
I believe the forum is not able to comprehend what 'can i get excel to
read
a menu with selections using X in a cell' is trying to convey. Maybe
you
want
on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
Sam Mathai Chacko
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com
to this group, send email to excel-macros@googlegroups.com
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
Sam Mathai Chacko
--
--
Some important links for excel users:
1
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
Sam Mathai Chacko
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http
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
--
Sam Mathai Chacko
If
With ActiveSheet.QueryTables.Add(Connection:= _
TEXT; strFile, Destination:= _
Range($A$1))
strFile =
.Name = MyFile1
.FieldNames = True
.
...
...
Regards,
Sam Mathai Chacko (GL)
On Wed, Oct 5, 2011 at 9:21 PM
://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/**discussexcelhttp://www.facebook.com/discussexcel
--
Sam Mathai Chacko
If you need prompts, and if the files are in different folders, then
Application.GetOpenFileName (my earlier code uses it) can be helpful
Regards,
Sam
On Wed, Oct 5, 2011 at 11:06 PM, Sam Mathai Chacko samde...@gmail.comwrote:
Just take care of the sheet activation also. If you are going
ThisWorkbook.Sheets.Select
Application.EnableEvents = False
Selection.Formula = Target.Formula
Application.EnableEvents = True
ActiveSheet.Select
End If
End Sub
Regards,
Sam Mathai Chacko (GL)
On Wed, Oct 5, 2011 at 9:01 PM, hariharan.thara...@wipro.com
on searching in this forum.and learnt that i
need to use the
Worksheet Change Event.is it exactly the same don which i need to use
Any ways,thanks for all the help given by you all...Especially DON..
On Tue, Oct 4, 2011 at 11:09 PM, Sam Mathai Chacko
samde...@gmail.comwrote:
For the sake
=DATEVALUE(LEFT(A3,2)/MID(A3,FIND( ,A3)+1,3)/RIGHT(A3,4))
Regards,
Sam Mathai Chacko (GL)
On Thu, Oct 6, 2011 at 10:20 AM, B.N.Chethan kumar
chetankumar1...@gmail.com wrote:
Hi All,
I have attached file where i get some data in text format. I need convert
to date and time format
http://www.facebook.com/discussexcel
--
Sam Mathai Chacko
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN
Well, actually, use
Range(A3:A6).TextToColumns Destination:=Range(A3), DataType:=1,
TextQualifier:=1, FieldInfo:=Array(1, 4), TrailingMinusNumbers:=True
Sorry for posting without testing the previous one.
Regards,
Sam Mathai Chacko (GL)
On Thu, Oct 6, 2011 at 10:57 AM, Sam Mathai Chacko
two columns, year and month along with the average of
VND.
Post back if you need any help
Regards,
Sam Mathai Chacko (GL)
On Tue, Oct 4, 2011 at 9:24 PM, Rahul Garwad garwads...@gmail.com wrote:
Thanks Sam,
Could you please explain the method used ?
Regards
Rahul.
On Tue, Oct 4, 2011
,
Sam Mathai Chacko (GL)
On Mon, Oct 3, 2011 at 10:48 PM, Nitin nitinhemm...@gmail.com wrote:
Hi All,
I want to be able to run queries within Excel like this:
--
mysql01 = select * from
--
Sam Mathai Chacko
--
--
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
manually?
Regards,
Sam
On Tue, Oct 4, 2011 at 12:00 AM, Nitin Hemmady nitinhemm...@gmail.comwrote:
Hi Yes I saw this, but I still get a General ODBC error. thank you
On Mon, Oct 3, 2011 at 1:23 PM, Sam Mathai Chacko samde...@gmail.comwrote:
Working with the inbuilt query feature in Excel
://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
--
Sam Mathai Chacko
at the very beginning of the
text like '8% with pcm or 45% in the aggregate', it will return a blank, and
not 8%. But I guess that would be a very rare case :D
Enjoy,
Sam Mathai Chacko (GL)
On Sun, Oct 2, 2011 at 5:15 PM, rajan verma rajanverma1...@gmail.comwrote:
Try this:
=IF(ISERROR(MID(A2,FIND
.Offset(, -1).Resize(, 2).Copy
Sheets.Add(After:=ActiveSheet).Cells(1)
.AutoFilter
.Cells(0).Resize(.Rows.Count + 1).Clear
End With
End Sub
Regards,
Sam Mathai Chacko (GL)
On Sun, Oct 2, 2011 at 11:05 AM, anil kumar kmr7a...@gmail.com wrote:
Hi suman,
I have
Your success precedes you Ayush. We acknowledge and appreciate your
contribution to the Excel community, and so have Microsoft. Congratulations
again on behalf of all those who have and will gain from your selfless
endeavors.
Best Regards
Sam Mathai Chacko (GL)
On Sun, Oct 2, 2011 at 8:54 PM
.
Regards,
Sam Mathai Chacko (GL)
On Sun, Oct 2, 2011 at 6:18 PM, Amit Desai (MERU)
amit.de...@merucabs.comwrote:
Dear All,
** **
Need your help..
** **
Please find attached excel file. I need to analyse revisit of vehicles in
workshop with a moving average of 30 days 1st aug
Amit, please go to Names Manager, and give the name of the range as Data,
and use the formula that I provided below. To go to Name Manager using a
short-cut key, hit CTRL+F3
Post back if you need more help.
Regards,
Sam Mathai Chacko (GL)
On Mon, Oct 3, 2011 at 12:10 AM, Amit Desai (MERU
FOR HELPING ME I HAVE ONE QUERY ALSO PENDING
I WANT THAT WHICH CELLS HAVE NOT ANY RESULT THEY CAN DISPLAYED AS BLANK
NOT AS #NUM IN THE FORMULA THAT I HAVE USED IN ATTACHED FILE.
On Sat, Oct 1, 2011 at 4:15 PM, Sam Mathai Chacko samde...@gmail.comwrote:
By the way, replace SINGH with $C$3
,MMMYY)!$B$2:$B$9)))+1,),ROW(A1
Sam
On Sat, Oct 1, 2011 at 10:28 PM, Sam Mathai Chacko samde...@gmail.comwrote:
Interesting!!!
Here you go...
=IF(ISERROR(SMALL(IF(ISNUMBER(SEARCH($C$3,INDIRECT(TEXT(SEARCH!$D$1,MMMYY)!$B$2:$B$9))),ROW(INDIRECT(TEXT(SEARCH!$D$1,MMMYY)!$B$2:$B$9))-MIN(ROW
Fabulous work Noorain. You make a difference, and all of us in the
forum appreciate your contribution. Carry on the excellent work.
Sam Mathai Chacko (GL)
On 10/2/11, suryaprasad...@gmail.com suryaprasad...@gmail.com wrote:
Hip Hip Hurry.. For sure you deserve that NOORAIN u rock dear
USE
=INDEX(INDIRECT(TEXT(D1,MMMYY)!$A$2:$A$65536),SMALL(IF(ISNUMBER(SEARCH($C$3,INDIRECT(TEXT(D1,MMMYY)!$B$2:$B$65536))),ROW(INDIRECT(TEXT(D1,MMMYY)!$B$2:$B$65536))-MIN(ROW(INDIRECT(TEXT(D1,MMMYY)!$B$2:$B$65536)))+1,),ROW(A1)))
in B8
Regards
Sam Mathai Chacko (GL)
On Sat, Oct 1, 2011 at 8:03
://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
--
Sam Mathai Chacko
://www.facebook.com/discussexcel
--
Sam Mathai Chacko
--
--
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
Nicely done by Noorain.
Shouldn't the solution provided by ChilExcel suffice? Pivot tables as it is
dynamic in nature, you just need to pass the source range.
Regards,
Sam Mathai Chacko (GL)
On Mon, Sep 26, 2011 at 11:40 AM, NOORAIN ANSARI
noorain.ans...@gmail.comwrote:
Dear Amit,
Please
with the mentioned sheet name is activated at an application level.
The event which you mentioned is at the Workbook level (associated with the
file that it is written in). Aju requires the event at an application level.
Let me know if you need further clarification.
Regards
Sam Mathai Chacko
On Mon, Sep
.
Regards
Sam Mathai Chacko
On Mon, Sep 26, 2011 at 4:42 PM, Paul Schreiner schreiner_p...@att.net
wrote:
Forgive me if this is totally off-base.
But I have never used the Activate event in a Class module.
In the ThisWorkbook module, I would use:
Private Sub Workbook_Activate()
startappmon
OR, you could use a formula to reverse it too
Use =INDEX($A$2:$M$2,COUNTA(A2:$M$2)) and drag to the right
Regards
Sam Mathai Chacko (GL)
On Sep 23, 8:54 pm, dguillett1 dguille...@gmail.com wrote:
Try in a macro enabled workbook (.xls or .xlsm)
Sub CustomSortSAS() 'converts to values
Rows
Also, on second thought, the column count should be more sure than the value
count. So
*=INDEX($A$2:$M$2,COLUMNS(A2:$M$2))* would be better than *=INDEX($A$2:$M$2,
COUNTA(A2:$M$2))*
Regards,
Sam Mathai Chacko (GL)
On Sun, Sep 25, 2011 at 10:20 PM, Sam Mathai Chacko samde...@gmail.comwrote
, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlLeftToRight
End Sub
*From:* Sam Mathai Chacko samde...@gmail.com
*Sent:* Sunday, September 25, 2011 11:53 AM
*To:* MS EXCEL AND VBA MACROS excel-macros@googlegroups.com
*Subject:* Re: $$Excel-Macros$$ columns data need
Try
=FORECAST(O8,$E$6:E7,$O$6:O7) in M8 and drag down
HTH,
Sam Mathai Chacko (GL)
On Sun, Sep 25, 2011 at 11:12 PM, Steve Weaver steveweave...@comcast.netwrote:
Each day I update the attached spreadsheet with with our previous days
shipments. I would like to use the daily ship data
You could use a combination of Haseeb's formula and this array formula
=INDEX(Sheet1!$A:$A,MIN(IFERROR(MATCH(RIGHT(A2,ABS(LEN(A2)+1-ROW(INDEX($A:$A,1):INDEX($A:$A,LEN(A2)*,Sheet1!$A:$A,0),)))
The trick would be to identify *when* you would use either...
Sam Mathai Chacko (GL)
On Wed, Sep
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
--
Sam Mathai Chacko
://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
--
Sam Mathai Chacko
://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
--
Sam Mathai Chacko
--
--
Some important links
Mr Excellent,
In the properties of the user form, you will have to change the showmodal
property to False
Regards,
Sam Mathai Chacko (GL)
On Fri, Sep 16, 2011 at 2:57 PM, Mr Excellent suryaprasad...@gmail.comwrote:
*DID YOU GET MY QUESTION AS THE ANSWER IS NOT RELEVANT*
On Fri, Sep 16
format, instead of
dd-mm-.
Regards,
Sam Mathai Chacko
On Thu, Sep 15, 2011 at 7:25 PM, Shankar Bheema shankar.n...@gmail.comwrote:
hai all,
I am attaching my excel sheet userform. I am getting wrong result of
months in different way.
for example :
Actual months difference between 01-02
, dtmEnd)
TextBox3.Text = (TextBox13.Text + Val(ComboBox9.Text)) * TextBox17.Text
End Sub
Private Sub UserForm_Initialize()
ComboBox9.List = Array(1800, 1900, 2400, 2800, 4200, 4600,
4800, 5400, 6600, 7600, 8700, 8900, 1)
End Sub
Regards,
Sam Mathai Chacko (GL)
On Thu, Sep 15, 2011
words. Note that you cannot use ALT
Regards,
Sam Mathai Chacko (GL)
On Tue, Sep 13, 2011 at 10:50 PM, Nikhil Shah nikhil201...@gmail.comwrote:
Dear All,
As per your E-mail I am sending here with an Excel Sheet Attachment in
which I have shown what exactly I want.
( see column No ' O
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
--
Sam Mathai Chacko
Private Sub CommandButton1_Click()
txtdor.Text = DateAdd(, 60, IIf(Day(CDate(txtdob.Text)) = 1,
DateAdd(d, -1, CDate(txtdob.Text)), CDate(txtdob.Text)))
End Sub
Regards,
Sam Mathai Chacko (GL)
On Mon, Sep 12, 2011 at 9:28 PM, Shankar Bheema shankar.n...@gmail.comwrote:
but i am
And if you go the formula way, try this
=IF(DAY(A2)=1,DATE(YEAR(A2-1)+60,MONTH(A2-1)+1,0),DATE(YEAR(A2)+60,MONTH(A2)+1,0))
On Mon, Sep 12, 2011 at 9:43 PM, Sam Mathai Chacko samde...@gmail.comwrote:
Thought you wanted to increment date only...
Try this
Private Sub CommandButton1_Click
Mathai Chacko (GL)
On Mon, Sep 12, 2011 at 9:37 PM, Shankar Bheema shankar.n...@gmail.comwrote:
I tried with the provided code but its working only with the years but
months and days are not working
On Mon, Sep 12, 2011 at 9:33 PM, Sam Mathai Chacko samde...@gmail.comwrote:
Private Sub
)
The downside of this function is that it evaluates BOTh the true and false
parts, and sometimes can cause issues when evaluating various calculations
like cheecking if a divisor is 0.
Hope this helps.
Sam Mathai Chacko
On Mon, Sep 12, 2011 at 9:57 PM, Shankar Bheema shankar.n...@gmail.comwrote
, send email to excel-macros@googlegroups.com
**
Like our page on facebook , Just follow below link
http://www.facebook.com/**discussexcelhttp://www.facebook.com/discussexcel
--
Sam Mathai Chacko
--
--
Some
are to this group unwelcome then I have better things
to do. GEEZ!!!
Sub OpenFile()
Workbooks.Open Filename:= _
C:\Users\user\Desktop\2011_ InputBox(Vnesi nalog) .xlsx
End Sub
*From:* Sam Mathai Chacko samde...@gmail.com
*Sent:* Sunday, September 11, 2011 3:55 PM
*To:* excel-macros
://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
--
Sam Mathai Chacko
--
--
Some
--
Sam Mathai Chacko
--
--
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
://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
--
Sam Mathai Chacko
--
--
Some
501 - 570 of 570 matches
Mail list logo
