$$Excel-Macros$$ Re: Most Helpful Member Feb'12 - Noorain Ansari

[anandydr]

Considering his work for the past few months.. could we recommend him
for next MVP 

On Mar 15, 3:49 am, Ayush Jain  wrote:
>  Hello Everyone,
>
> Noorain Ansari has been selected as 'Most Helpful Member' for the month of
> Feb'12
> He has posted 156 posts in Feb'12 and helped many people through his
> expertise. He has been consistent contributor to this excel forum but has
> acheived this recognition for third time.
>
> *About Noorain Ansari :*
> [image: noorain.jpg]
>
> Noorain is a great poet and keep sharing his thoughts on different topics
> throgh cool poems. Professionally, he is a Excel aficionado and presently
> working as senior business analyst in One97 Communication Ltd. He is a MS
> excel - vba expert and keep sharing his thoughts with this forum. You can
> follow his blogs for Excel knowledge and cool poems 
> :www.excel-macro.blogspot.com&www.noorain-ansari.blogspot.com
>
> *Noorain, Many thanks for all your great support to group. Keep it up*
>
> *Thanks everyone for helping excel enthusiasts voluntarily !! Keep it up !!
> **
> *Keep posting.
>
> Regards
> Ayush Jain
> Group Manager
> Microsoft MVP
>
>  noorain.jpg
> 2KViewDownload

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

$$Excel-Macros$$ Re: Macro to import email information to excel

[anandydr]

Hi,

The following subroutine will do that:

Sub GetReportCount()
On Error GoTo getMail_err

Dim ns As Namespace
Dim Inbox As MAPIFolder
Dim Item As Object
Dim Atmt As Attachment
Dim FileName As String
Dim i As Integer

Set ns = GetNamespace("MAPI")
Set Inbox = ns.GetDefaultFolder(olFolderInbox)
i = 0


If Inbox.Items.Count = 0 Then
MsgBox "There are no messages in the Inbox.", vbInformation, _
   "Nothing Found"
Exit Sub
 End If
 For Each Item In Inbox.Items
For Each Atmt In Item.Attachments
   FileName = "C:\Email Attachments\" & Atmt.FileName
   'Atmt.SaveAsFile FileName
   i = i + 1
Next Atmt
 Next Item

 MsgBox "I saved " & i & " attachments", vbOKOnly

'Error Handling goes here


getMail_err:

GetAttachments_exit:
   Set Atmt = Nothing
   Set Item = Nothing
   Set ns = Nothing

End Sub

Insert a module in VBA editor and paste this. Then you can attach it
to a command button.

Hope that helps,
Anand Kumar


On Feb 16, 8:24 pm, "B.N.Chethan Kumar" 
wrote:
> Dear friends,
>
> Need a help. I need to import email information into excel need following
> information from Outlook express.
>
> email received: -
>
> Sender Name or Email ID
> Sender time:
> Subject:
>
> Email responded:
>
> Responded name or email id
> Responded time
> Subject:
>
> --
> B.N Chetan kumar

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Pivot Table data range update

[anandydr]

Although I have not tested it on Excel 2010 in Excel 2007 the shortcut
key to Name Manager dialog box is Ctrl + F3 I hope it works for 2010
also.
As for explanation given by Noorain it's excellent and most complete.
You can also achieve this through VBA but it would effect the speed/
performance of your spreadsheet.

Warm Regards,
Anand Kumar

On Feb 15, 6:15 am, Awal  wrote:
> Thanks Noorain.
> That was very thorough. I am using excel 2010 but I can find the Name
> Manager.
> I will encourage all our Excel gurus to follow your footsteps.
> This little tutorial made me like this forum even more!!!
> Thanks a lot and best regards.
>
> On Feb 13, 11:11 pm, NOORAIN ANSARI  wrote:
>
>
>
>
>
>
>
> > Dear Awal,
>
> > Please use it
>
> > Press CTRL F3 or go to Edit Insert Name Define
>
> > Name your range – e.g. pivot_table_data1
>
> > In the refers to section type or copy and paste this formulas
>
> > =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))
>
> > or
>
> >  =Sheet1!$A$1:INDEX(Sheet1!$A:$F,COUNTA(Sheet1!$A:$A),6)
>
> > [image: clip_image002] 
>
> > Assuming that your data starts in cell A1 of Sheet1. Edit accordingly
> > before pasting in
>
> > What this formula does is creates an array that starts at cell a1 goes zero
> > cells down and zero cells right, and is x cells deep and y cells wide. x =
> > the count of all non blank cells in column A and y the count of all non
> > blank cells in row 1. Assumes that column A and row 1 are contiguous data
> > blocks the same length and width as the whole data set. If not use columns
> > and rows that are.
>
> > Now in your pivot table right click and go to Pivot Table Wizard
>
> > [image: clip_image004] 
>
> > Hit Back and go to the range selection
>
> > [image: clip_image006] 
>
> > Type = and the range name = e.g. =pivot_table_data1
>
> > In my case my range name was called test.
>
> > Hit Finish and it is done.
>
> > Now whenever you paste more data into the data sheet the pivot table just
> > needs to be refreshed to pick it up
>
> > Click anywhere on the pivot table and click the exclamation mark in the
> > toolbar.
>
> > The instructions above assume you are using office 2003. In office 2007 the
> > Name Manager is a little more confusing, but can be accessed with the same
> > keyboard shortcut and you will need to create a new range name and then use
> > the same formula listed above.
>
> > --
> > Thanks & regards,
> > Noorain Ansari
> >  *http://noorainansari.com/*
> > *http://excelmacroworld.blogspot.com/*
>
> > On Tue, Feb 14, 2012 at 6:49 AM, Awal  wrote:
> > > I have code to query a database and return data to the "data" sheet.
> > > The number of rows of data returned will vary.
> > > Through VBA code, how do I tell the pivot table to update the range of
> > > the source data (which is really to update the last row).
>
> > > (Selecting CTRL-End, goes past where the actual data ends)
>
> > > Thank you.
>
> > > --
> > > FORUM RULES (986+ members already BANNED for violation)
>
> > > 1) Use concise, accurate thread titles. Poor thread titles, like Please
> > > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> > > will not get quick attention or may not be answered.
>
> > > 2) Don't post a question in the thread of another member.
>
> > > 3) Don't post questions regarding breaking or bypassing any security
> > > measure.
>
> > > 4) Acknowledge the responses you receive, good or bad.
>
> > > 5)  Cross-promotion of, or links to, forums competitive to this forum in
> > > signatures are prohibited.
>
> > > NOTE  : Don't ever post personal or confidential data in a workbook. Forum
> > > owners and members are not responsible for any loss.
>
> > > ---
> > >  ---
> > > To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ last cell by Formula

[anandydr]

Hi Hanumant,

REPT("z", 255) will return a string with z repeated 255 times
something like "..." and MATCH will then try to find
it in the column. Since such a string will not be available in the
column so MATCH will return the last value in the column which has
data.

Hope that helps,
Anand Kumar

On Dec 26, 1:45 am, hanumant shinde  wrote:
> Thanks a lot rajan but i didnt understand the logic.
>
> =MATCH(REPT("z",255),A:A)
>
> in this what it is doing? any idea? i m going mad about it :(.
>
>
>
>
>
>
>
>
>
> >
> > From: Rajan_Verma 
> >To: excel-macros@googlegroups.com
> >Sent: Monday, 26 December 2011 1:19 AM
> >Subject: RE: $$Excel-Macros$$ last cell by Formula
>
> >Read this :
> >
> >http://www.techonthenet.com/excel/questions/last_value.php
> >
> >From:excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On 
> >Behalf Of hanumant shinde
> >Sent: 26 December 2011 01:13
> >To: Excel Group
> >Subject: $$Excel-Macros$$ last cell by Formula
> >
> >Hi,
> >
> >it was interview que.
> >
> >how can you find last cell jus by formula.
> >
> >also, can somebody tell help clear my OOPs concept.
> >--
> >FORUM RULES (934+ members already BANNED for violation)
> >
> >1) Use concise, accurate thread titles. Poor thread titles, like Please 
> >Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will 
> >not get quick attention or may not be answered.
> >
> >2) Don't post a question in the thread of another member.
> >
> >3) Don't post questions regarding breaking or bypassing any security measure.
> >
> >4) Acknowledge the responses you receive, good or bad.
> >
> >5) Cross-promotion of, or links to, forums competitive to this forum in 
> >signatures are prohibited.
> >
> >NOTE : Don't ever post personal or confidential data in a workbook. Forum 
> >owners and members are not responsible for any loss.
> >
> >-- 
> >
> >To post to this group, send email to excel-macros@googlegroups.com
> --
> >FORUM RULES (934+ members already BANNED for violation)
> >
> >1) Use concise, accurate thread titles. Poor thread titles, like Please 
> >Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will 
> >not get quick attention or may not be answered.
> >
> >2) Don't post a question in the thread of another member.
> >
> >3) Don't post questions regarding breaking or bypassing any security measure.
> >
> >4) Acknowledge the responses you receive, good or bad.
> >
> >5)  Cross-promotion of, or links to, forums competitive to this forum in 
> >signatures are prohibited.
> >
> >NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
> >owners and members are not responsible for any loss.
> >
> >-- 
> >
> >To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$dividing my data set in 70-30 Ratio

[anandydr]

Hi Vikas,

You will have to insert an extra column and put the formula
=RANDBETWEEN(min_value, max_value) there. Once the formula gives you
result copy the column where you had your Randbetween function copy it
and select paste special > values on the same column. This will make
the results of dummy column static. Now you can select as many random
values in column using filter. Copy the resulting data, and paste it
somewhere else. Now dummy column can be removed and origional data can
be used. eg (34,45,56,67,78,89,100,54,65,76) gave (34, 45, 78, 54)
when I added an extra column and used filter on dummy column to select
a value of 3 and 4 in dummy column then I deleted dummy column.

You might also want to use Haseeb's solutions of putting the following
functions if Data is between A and J columns:

=INDEX($A$2:$J$2,RANDBETWEEN(1,COUNTA($A$2:$J$2)))

=INDEX($A$2:$J$2,RANDBETWEEN(1,COUNTA($A$2:$J$2)))
B3, *Array Formula must hit CTRL+SHIFT+ENTER rather than just ENTER*
=INDEX($A$2:$J$2,MATCH(1,IF(ISNA(MATCH($A$2:$J
$2,$A3:A3,0)),IF(RANDBETWEEN( 1,COUNTA($A$2:$J$2)),1)),0))
copy across.
Hope that helps,
Anand Kumar

On Dec 25, 12:14 pm, Vikash Chandra  wrote:
> Thanks @ Anand, but the problem here will be it will give some random
> number,which will be not from my original observation.I want the new
> data set only from my original observation. For example, suppose my
> original data set has (34,45,56,67,78,89,100,54,65,76) these values.
> Now i want to split this data set into 70-30 Ratio who has all the
> original observation only. The result should be something like
> (45,100,65,76,34,89,78). All values must be in new data set taken
> randomly from the original data set.Please help me out.I think there
> is not any function to do this.There must be some vba code for this.
>
> Thanks & Regards,
> Vikash Chandra
> Bangalore
> 9902622922
>
>
>
>
>
>
>
>
>
> On Sun, Dec 25, 2011 at 6:20 AM, anandydr  wrote:
> > Hi Vikas,
>
> > One way to do this would be using a function =RANDBETWEEN( min_value,
> > max_value). For example if you insert a column in which you put this
> > function =RANDBETWEEN(1, 100) it will generate random numbers between
> > 1 and 100 then you can apply a filter on that column and select values
> > below 80. It will give you about 70-75% of your data and you can use
> > it then.
> > This will essentially be a trial and error method and you won't get
> > exact 70:30 ratio but if you could provide a sample file someone in
> > the group can come up with a better solution.
>
> > Hope that helps,
> > Anand Kumar
>
> > On Dec 24, 4:16 pm, Vikash Chandra  wrote:
> >> Hi Team, I am facing problem in breaking my data set into two random
> >> sample,which i need for model validation.means i wanted to break my
> >> entire row in two sample of say 70-30 ratio,in which the observations
> >> should be taken randomly from the  original  observations.Is there any
> >> function or macro we can write for this? Please help me out.
>
> >> Thanks & Regards,
> >> Vikash Chandra
> >> Bangalore
> >> 9902622922
>
> > --
> > FORUM RULES (934+ members already BANNED for violation)
>
> > 1) Use concise, accurate thread titles. Poor thread titles, like Please 
> > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice 
> > will not get quick attention or may not be answered.
>
> > 2) Don't post a question in the thread of another member.
>
> > 3) Don't post questions regarding breaking or bypassing any security 
> > measure.
>
> > 4) Acknowledge the responses you receive, good or bad.
>
> > 5)  Cross-promotion of, or links to, forums competitive to this forum in 
> > signatures are prohibited.
>
> > NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
> > owners and members are not responsible for any loss.
>
> > --- 
> > ---
> > To post to this group, send email to excel-macros@googlegroups.com
>
> --

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$dividing my data set in 70-30 Ratio

[anandydr]

Hi Vikas,

One way to do this would be using a function =RANDBETWEEN( min_value,
max_value). For example if you insert a column in which you put this
function =RANDBETWEEN(1, 100) it will generate random numbers between
1 and 100 then you can apply a filter on that column and select values
below 80. It will give you about 70-75% of your data and you can use
it then.
This will essentially be a trial and error method and you won't get
exact 70:30 ratio but if you could provide a sample file someone in
the group can come up with a better solution.

Hope that helps,
Anand Kumar

On Dec 24, 4:16 pm, Vikash Chandra  wrote:
> Hi Team, I am facing problem in breaking my data set into two random
> sample,which i need for model validation.means i wanted to break my
> entire row in two sample of say 70-30 ratio,in which the observations
> should be taken randomly from the  original  observations.Is there any
> function or macro we can write for this? Please help me out.
>
> Thanks & Regards,
> Vikash Chandra
> Bangalore
> 9902622922

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Macro Chart Problem For Stack Exchange Program

[anandydr]

Hi,

How your current file operates is more or less understandable from
your previous file. Somehow what changes you need are still not clear.
As Mr Don Guillett says could you attach a sample file with both
current method and results that you need after change as sample?

Regards,
Anand Kumar

On Nov 19, 2:31 pm, renuka chari  wrote:
> hi
>
> Sam Mathai Chacko,
> Don Guillett.
>
> thanks for ur responce
>
> Actually this Excel Project Will work like this:
>
> here Ohlc Sheet Having data it is automatically coming from yahoo site
> if we need to select symbol, next cell for company, and enter starting
> date and ending date then click on get data button then data will
> coming automatically here max & min values are depending on close
> column chart sheet having block size if we need to enter bolck size
> and click on drawchart then graph will display reversal is fixed
> constant
>
> Presently chart ranges are coming depending on the Fixed block size
> but I need like this, if Max & Min valus are Max is 6000 & min is 290
> then when ever clicking on draw chart Button the scale should be
> display 6000-2900 range diff is 100 & 2900-1400 range diff is 50 &
> 1400-600 Range diff is 20 like this.
>
> i think my problem is clear if not plz feel free to give a responce
>
> thanks in advance
> Renukachari

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

$$Excel-Macros$$ How to use COMBIN and PERMUT functions

[anandydr]

For an internal training assignment I need to use PERMUT() and
COMBIN() functions. Although I have been able to develop material for
other statistical functions like FREQUENCY(), VAR(), STDEV(), etc I
have been unable to develop material for these functions.
Let’s say for example if the question is :
1.  In a chess board of 9X9 squares how many rectangles can be drawn?
2.  Using 1, 2, 3, 4 how many two digit numbers can be generated?
How do I find the values to be put into formulas ? And how should I go
about explaining the steps involved? Could anyone help?

Warm regards,
Anand Kumar

-- 
FORUM RULES (925+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

$$Excel-Macros$$ Re: Excel VBA professional needed for a Project Work

[anandydr]

Hi,

I would be interested in taking up the project. But to automate your
worksheets I need to look into the sample sheets. If you could please
mail me a few samples and what you want to achieve we can discuss this
further.

Warm regards,
Anand Kumar
anand...@gmail.com
New Delhi

-- 
FORUM RULES (925+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Convert amount to Million

[anandydr]

Hi Aamir,

In case you want to create a button in the ribbon it is a bit
difficult in Excel 2007. Somehow you can add a command button which is
available in Developer tab in the ribbon and right click on the button
and assign macro to it.

Hope that helps,
Anand Kumar

On Oct 31, 1:55 am, Aamir Shahzad  wrote:
> Dear SAM I found my requirement in the link below
>
> http://www.contextures.com/xltoolbar02.html#AddIn
>
> I have also created & Installed 2 buttons but how create more
> buttons+assign macros to buttons.
>
> Regards,
>
> Aamir Shahzad
>
> On Sat, Oct 29, 2011 at 8:53 AM, Aamir Shahzad 
> wrote:
>
>
>
>
>
>
>
> > Yes SAM I want to enable this macro for all excel sheets in shape of add
> > in. Well .xlam is attached where I am created new 2nd module however your
> > "Convert to Million" Macro is running fine in sheet but when I enter this
> > macro into add in file this won't work.
>
> > Aamir Shahzad
>
> > On Sat, Oct 29, 2011 at 3:00 AM, Sam Mathai Chacko 
> > wrote:
>
> >> Why do you want this in another add-in? Do you want the feature to be
> >> available in ALL the workbooks that you use?
>
> >> Also Aamir, a normal add-in does not have the require events to capture a
> >> change in the sheets in another workbook. To get it to run automatically,
> >> you'll need to call an application level event from the add-in. Unless you
> >> send me this add-in, I will not be able to provide a solution.
>
> >> Sam
>
> >> On Sat, Oct 29, 2011 at 3:25 AM, Aamir Shahzad 
> >> wrote:
>
> >>> how to call? is it not run automatically when enter 1M & enter?
>
> >>> Aamir Shahzad
>
> >>> On Sat, Oct 29, 2011 at 2:52 AM, Sam Mathai Chacko 
> >>> wrote:
>
>  The original code that I gave should have been written within a
>  workbook. But now that you are using an add-in, yes you can use it in a
>  different module, but for it to run, you will have to CALL it when 
>  required.
>
>  Sam
>
>  On Sat, Oct 29, 2011 at 3:20 AM, Aamir Shahzad <
>  aamirshahza...@gmail.com> wrote:
>
> > Can I create another "Module 2" in the same add in & write code there?
>
> > Aamir Shahzad
>
> > On Sat, Oct 29, 2011 at 2:43 AM, Sam Mathai Chacko  > > wrote:
>
> >> Not sure what procedure you require, but if you knew the range, this
> >> is how you'd call this
>
> >>  your code --
> >>  your code --
> >> Call ConvertToMillions(Worksheets("NameOfSheet").Range("A1"))
> >>  your code --
>
> >> Private Sub ConvertToMillions(ByRef Target As Range)
>
> >>     Dim rngCell As Range
>
> >>     For Each rngCell In Target
> >>         If Right(UCase(Trim(rngCell.Text)), 1) = "M" Then
> >>             rngCell.Value = Replace(UCase(Trim(rngCell.Text)), "M",
> >> "") * 10 ^ 6
> >>         End If
> >>     Next
>
> >> End Sub
>
> >> Sam Mathai Chacko (GL)
>
> >> On Sat, Oct 29, 2011 at 3:10 AM, Aamir Shahzad <
> >> aamirshahza...@gmail.com> wrote:
>
> >>> how? procedure required.
>
> >>> On Sat, Oct 29, 2011 at 2:39 AM, Sam Mathai Chacko <
> >>> samde...@gmail.com> wrote:
>
>  Yes you can
>  Sam
>
>  On Sat, Oct 29, 2011 at 3:07 AM, Aamir Shahzad <
>  aamirshahza...@gmail.com> wrote:
>
> > Can I enter this code with another add in?
>
> > On Sat, Oct 29, 2011 at 2:27 AM, Sam Mathai Chacko <
> > samde...@gmail.com> wrote:
>
> >> Something like this might do the trick
>
> >> Private Sub Worksheet_Change(ByVal Target As Range)
>
> >>     Dim rngCell As Range
>
> >>     For Each rngCell In Target
> >>         If Right(UCase(Trim(rngCell.Text)), 1) = "M" Then
> >>             rngCell.Value = Replace(UCase(Trim(rngCell.Text)),
> >> "M", "") * 10 ^ 6
> >>         End If
> >>     Next
>
> >> End Sub
>
> >> Regards,
>
> >> Sam Mathai Chacko (GL)
>
> >> On Sat, Oct 29, 2011 at 2:52 AM, Aamir Shahzad <
> >> aamirshahza...@gmail.com> wrote:
>
> >>> Dear Experts,
>
> >>> Is it possible in excel that if I write in cell "1M" & press tab
> >>> or enter it automatically converted this figure to 1,000,000. or 
> >>> 4.5M to
> >>> 4,500,000.
>
> >>> Regards,
>
> >>> Aamir Shahzad
>
> >>> --
> >>> FORUM RULES (925+ members already BANNED for violation)
>
> >>> 1) Use concise, accurate thread titles. Poor thread titles, like
> >>> Please Help, Urgent, Need Help, Formula Problem, Code Problem, 
> >>> and Need
> >>> Advice will not get quick attention or may not be answered.
>
> >>> 2) Don't post a question in the thread of another member.
>
> >>> 3) Don't post questions regarding breaking or bypassing any
> >>> security measure.
>
> >>> 4) Acknowledge the responses you receive, good or bad.
>
> >>> 5) Cross-promotion of,

$$Excel-Macros$$ Re: Plotting without zero's at end of plot

[anandydr]

Hi,

One simple method would be to incase your formula in and IF formula
which can put NA( ) instead of zero. e.g.
=IF(COUNTIF(A1:A100,"T")=0,NA( ), COUNTIF(A1:A100,"T"))

Excel would not plot NA in the charts and still the cells would be
included so your chart will go all the way to the right side even if
the last values in your series are zero.

Hope that helps,
Anand Kumar

On Oct 17, 12:29 am, Ken  wrote:
> I have a sheet where I am listing the data (numbers) in columns. I add
> data at the bottom of the columns as I get it.  I plot a single column
> of data using the offset function within a Named range to set the data
> to plot against the number of data points input; plus a few blank rows
> (e.g. 30) below the data to make the chart look good.
>
> Named range "CashFlow" =OFFSET(Poker!$G$3,0,0,COUNTA(Poker!$G$3:$G
> $1004)+30,1)
>
> Source for chart: Series Values =Poker.xls!CashFlow
>
> I auto scale based on the offset function; testing for the data in the
> column. E.G. COUNTA.  No issues there.
>
> I am using another column to filter the data in the first column to
> show a subset of the data in the first column.  I use formulas in the
> second column to filter the data.  The formulas are filled in for a
> 1000+ rows.  When I try the same technique to plot the second column I
> get the data plus a few rows below the data with zeros in the plot.
> e.g. The plot goes to zero at the end for the rows below the data with
> formulas in the cells.  I have cut off the extra rows; it works; but
> does not look the same as the first chart.  Specifically: Plot goes
> all the way to the right side of the chart.  >>
>
> Ideas on making the second plot with a few extra rows that do not plot
> as zeros?  Where to add the rows? In the named range or in the source
> for the plot?  And how?

-- 
--
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$$ Re: Anyone like to take a project and challenge in excel

[anandydr]

Hi Mayank,

I tried to see what you are planing. As already stated by dguillett1
now many people might be anxious to help telemarketers still as a
project it would be challenging for sure. I can't find the attachment
that you have mentioned in your post. I would try to create a
flowchart for the program and send it to your mail ID.
You would understand that a program of this size would take time to
create and it is not a one man job. Initially it would at most sustain
a test run only and as the size and pressure on your call center grows
you will have to employ your own MIS executives to operate and
maintain the software full time.

Warm regards,
Anand Kumar
anand...@gmail.com

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

[anandydr]

Hi,

One easy way can be to add a helper column and use =MONTH ( B7) to
find the Month number for the date of birth in column B and then in
the lower part of column B we can use =COUNUTIF($B$7:$B$81, B84) to
see how many people were born in the first month which gives the
result as 4 in this case. Same can be achieved through a pivot table
also.

Alternatively SUMPRODUCT function can be used to give a similar
result. Would like to see what other methods can be used to solve this
test.

Hope that helps,

Warm regards,
Anand Kumar

-- 
--
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$$ Hi - Details needed for the MIS Executive Qualification

[anandydr]

Hi,

In case of MIS Executives the kind of the data sets is very rigid. The
data is always in the same format. Mostly the amount of data would
also be the same every day/week. The frequency of reports would be
also same for them, i.e. the MIS is generated on a weekly, daily or 15
days or quarterly basis. The data is extracted from a source which is
set by a business analyst beforehand and should not or is not
manipulated without prior permission. Considering all this we can
conclude:

1. MIS Executive is Good at Excel functions like Lookup, Vlookup and
Hlookup
2. He/She has some knowledge of pivot tables and data extraction.
3. He/She can work comfortably on data sets of around 20,000 rows and
200 colums (approx)
4. He/She should have some knowledge of text manipulation functions
(Concatenate, len, find, left, right etc).
5. He/She should be comfortable using combination of functions e.g.
extract first name from email id in cell A1 when A1 contains
anand...@gmail.com
6. He/She should be able to use logical functions
7. Should be able to use error handling functions available in MS
Excel.
8. Last but not the least WILLINGNESS TO LEARN.

Warm regards,
Anand Kumar.

-- 
--
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$$ Re: Excel test required

[anandydr]

Hi Navneet,

If you could elaborate the purpose of this test it would be easier to
generate a test. For example is this test for the purpose of
recruitment? Do you plan to recruit MIS executives, or data entry
operators or senior data analysts, or designers of dashboards,
templates. Depending upon the complexity the number and complexity of
questions would vary. Like the SUM ( ) function is volatile i.e. it
would recalculate everytime the sheet is reopened so if someone is
using sum or sumif function on a sheet say 500 or 1000 times the sheet
would be very slow, instead DSUM should be used. A data analyst or a
dashboard designer would know this but a data entry operator or MIS
executive would not. Similarly there would be Macros and there
performance issues. Would you need questions on macros as well??

Please elaborate. I can be reached at anandydr at gmail dot com. I am
sure many other group members would have readymade questions sets to
help you with your case as well.

Warm regards,
Anand Kumar

-- 
--
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$$ Converting Text Case By VBA

[anandydr]

Dear STDEV(i),

As you say text case can be converted to Proper Case using VBA but
wont be using Application.Function/Worksheet Function give faster
results as compared to this other method?

Warm regards,
Anand Kumar

On Jul 7, 6:02 am, "STDEV(i)"  wrote:
> Converting text case to ProperCase in VBA
>
> alt:
> Instead of using Application.Function / WorksheetFunction) we can also use
> VBA function:
> *StrConv *function
>
> StrConv("  ", vbProperCase)
>
> Example in sub procedur
>
> Sub ConvertProperCase()
>    Application.ScreenUpdating = False
>    Dim Cell As Range
>    For Each Cell In Selection.Cells
>       If Not Cell.HasFormula Then
>           Cell.Value = *StrConv(Rng, vbProperCase)*
>       End If
>    Next Cell
>    Application.ScreenUpdating = True
> End Sub
>
> Thank you
>
> On Thu, Jul 7, 2011 at 3:18 AM, Rajan_Verma wrote:
>
>
>
>
>
>
>
> >  http://excelpoweruser.blogspot.com/2011/07/hiding-data-on-worksheet.h...
> > ***
>
> > ** **

-- 
--
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$$ Re: and the Microsoft MVP award goes to Ashish Koul :)

[anandydr]

Congrats !!!

That's a very good news :D Keep up the good work Ashish

Warm regards,
Anand Kumar

-- 
--
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$$ Re: Time Issue

[anandydr]

A minor modification to your current formula can do that. Instead of
using your current formula you should use the following

=IF(AND(A2>C2,B2>D2),"Yes",IF(AND(TODAY()>C2,NOW()>D2),"Yes","No"))

Hope that helps
Ragards,
Anand Kumar


On May 27, 6:40 pm, Chandra Shekar 
wrote:
> Hi,
>
> I need to check whether date and time is greater than current date and time.
> Please find attached file. If date and time both are blank then it should
> check with present time and date.
>
> Thanks,
>
> Chandra Shekar B
>
>  TimeIssue.xls
> 19KViewDownload

-- 
--
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$$ Just started with version 2010 suggestions needed

[anandydr]

Hi,

I've just started using MS Excel 2010. Although I've been using Excel
for my work for years now and also plan to purchase John Walkenbach's
book on programming with 2010 I would like to have opinions from all
of my peers on what other things should I read to use Excel more
optimally
In case anyone happens to have any ebooks / PDF material or tutorials
on use of Excel 2010 could you please send them to my mail ID ?
My mail ID is anandydr at gmail dot com.
Eagerly awaiting your response...
Anand Kumar

-- 
--
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$$ How to remove space in excel cell

[anandydr]

At times the space is leading space which is not completely removed
using TRIM ( ) could anyone suggest what to do in such case?

On May 18, 3:38 am, "STDEV(i)"  wrote:
> sometimes what we thought as the space (as prefix or suffix) turns out to be
>  *Char (160)*so we need formula like this
> =SUBSTITUTE(A1,CHAR(160),"")
>
> On Sun, May 15, 2011 at 12:48 PM, Prabhu  wrote:
> > Hi friends,
> > How to remove spaces(bugs) in a particular cell either will be as
> > prefix or suffix before or after  number or text.
> > Regards,
> > Prabhu

-- 
--
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$$ Re: Excel blog of the month : Chandoo.org

[anandydr]

Hi,

I have been regularly visiting this site www.chandoo.org for past
couple of years. His comments on various things like dashboarding and
charting have been really helpful.

Regards,
Anand Kumar

On May 1, 10:25 pm, Ayush Jain  wrote:
> Dear group,
>
> As the part of discussexcel.com initiative, we declare "Excel bog of the
> month" each month. This is done to appreciate indvidual Excel bloggers and
> encourage excel blogging.
>
> *Chandoo.org* has been selected as blog of the month for *April 2011*. This
> is a great excel blog which is very active in sharing tips, tricks & data
> analysis techniques regularly.
> The blog is managed by *Purna Duggirala* who is Microsoft MVP since 2009.
> I have published the more info on discussexcel.com. Please do visit by
> clicking this 
> *link*
> for
> more information.
>
> *Another great news : We are 7800 strong now...Just 200 away from 8000
> milestone...Invite your friends and colleagues to be part of this great
> group. Thanks*
>
> Best 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

$$Excel-Macros$$ Changing interview scenario ??

[anandydr]

Hi Group,

I am Anand Kumar and I have been a member of Excel-macros google group
for past couple of years. I appreciate your inputs through the group
as they have not only helped me during my daily work but also in
clearing a few interview rounds.
My question relates to the kind of interview questions being put these
days. In recent times I have appeared for some interview (3 interviews
to be specific). Somehow the kind of questions have changed. Earlier
the questions were more technical let's say on difference between find
and search functions, or sum and dsum functions, how to create a
function that will lookup on the left side instead of right as vlookup
does. In these interviews most questions have been of type where the
focus is on how our work impacts business performance. Like how does a
dashboard / MIS impact decision making- give your views.
Does this implicate a change in business environment or is it just
with a few organizations? Also I can see most companies require skills
on databases like MySQL and Oracle or some SQL skills in data /
business analysts. In case other members have also faced such scenario
what skills do we need to upgrade to keep up with the changes in
business environment??

--
Warm regards,
Anand Kumar

-- 
--
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$$ Re: Open Excel addin is unavailable

[anandydr]

I've also tried to download and install the same. The process halts in
a few minutes. Kindly rectify the problem.

Warm Regards,
Anand Kumar

-- 
--
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$$ Re: Excel Interview Question - Help

[anandydr]

Hi,

You will find a lot of questions in Mr Ashish Koul's link besides you
can prepare for following questions:

1. What are database functions e.g what would be the difference
between SUM ( ) and DSUM ( )?
2. How are array functions useful ?
3. What is the syntax for VLOOKUP ( ) / HLOOKUP ( ) function?
4. What functions can be used to lookup data on both the sides of
criteria mentioned ( INDEX ( ) / OFFSET (  ))
5. Name any error handling formulas included in Excel built in
functions

Questions would vary according the requirements of profile. Somehow I
have found that error handling functions and lookup functions are
asked in almost all the profiles that need good MS Excel skills.

Hope that helps,
Regards,
Anand Kumar

-- 
--
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$$ Is there any carrer in excel.

[anandydr]

Hi,

Beside openings in MIS there are a lot of opportunities in back end
operations in Banking, Mutual Funds and Insurance organizations. More
importantly the knowledge of data analytics is what makes the use of
Excel as an important tool. A lot of companies look for professionals
with this skill set.
More important as an MIS executive you have worked on the same kind of
data set again and again. Somehow in analytics the kind of data that
you get keeps changing with the kind of project you are working upon.
So to improve your knowledge you can start working on VBA and Macros
to improve your chances of landing into a better job profile.

Hope that helps
Anand Kumar

On Mar 23, 2:43 pm, Bhushan Sabbani  wrote:
> Dear Sanjoy,
>
> Thank for the same.
>
> Warm Regards
> Bhushan Sabbani
> +91 98208 26012*
> *

-- 
--
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$$ Re: Excel Interview Q & A

[anandydr]

The Favorited question happens to be "VLOOkUP" for me... on every
interview this has been asked. The no of parameters (which is 4) and
the last parameter (which is 0/1 or False and True).
Next come the way to look up on the left which VLOOKUP can't do.. so
INDEX and MATCH functions with OFFSET of course...
Next would be advanced filters and then Pivot tables... everyone
knowledgeable knows that VBA performs slow when compared to inbuilt
Excel functions so questions on Macros have been few and far between...

-- 
--
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$$ Re: Question on Pivots

[anandydr]

Hi,

I just checked your file. You need to do a few modifications to your
pivot.
1) Click on pivot table. Select Options and make Field List visible.
It's the option on top right in Option tab. Remove Insurance , Claims
and Reinsurance for Colum Labels.
2) Drag insurance in the sum Values area (right below Column Lables in
field list to the right of row lables). Similarly drag Claims and
Reinsurance to the same area.
3) Default they would be showing Count of Insurance, Count of Claims
and Count of Reinsurance. Right click on any of them and select Field
Settings. Select Summarize by Sum. Now the field sum would be shown in
the table as you need it.

In case you have trouble understanding or implementing the solution
feel free to reach me at anandydr at gmail dot com.

Hope that helps,
Anand Kumar.
On Jan 26, 10:01 pm, Pavan Kumar VVN  wrote:
> Hi,
>
> I have a testing scenario where I have Severity (Critical, High, Medium &
> Low) and I have modules as Insurance, Claims, etc. When I take a pivot, I
> want to combine all the module information in the body of the pivot showing
> Severity in Row labels and all the other info under summation of Total of
> Insurance, Claims, etc. I do not have a total column in my data sheet. Can
> someone help. Attached is some data related to this.
>
> --
> V.V.N. Pavan Kumar
>
>  Pivot-Question.xlsx
> 16KViewDownload

-- 
--
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$$ Re: how to use " Or" in pivote table ?

[anandydr]

Hi,

Add an helper column to your data Insert column after column 2 and
there put =IF(A2="cat","Cat",IF(B2="cat","Cat",""))
This will put a "Cat" if there is "cat" in column1 or column2 then you
can base your pivot table on this column to get your solution.

Hope that helps,
Anand Kumar

On Jan 14, 2:23 am, "Omar"  wrote:
> I have a pivot table
>
> how to use " Or" in two columns
>
> I want to view all rows which
>
> Column1 = "cat" Or column2 = "cat"
>
> Not
>
> Column1 = "cat" AND column2 = "cat"
>
> By code or any way
>
> Thanks!

-- 
--
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$$ cumulative sum in pivot table

[anandydr]

Hi,

File by Mr Kantilal Badale is working as per your requirements. In
case you find problem in understanding how it is working please click
on any cell in column marked Sum of Sale 2 and right click. Select
"Value Field Setting.." > Select summarize by Sum and in Show Value as
tab select Running total in and Base Value: Qtr.

Hope that helps,
Anand

On Dec 9, 5:26 am, Kantilal Badale  wrote:
> check the attachment see if it helps .
>
> Kantilal  Badale
>
> On Tue, Dec 7, 2010 at 4:06 PM, alisha malhotra 
>
>
>
>
>
>
> > wrote:
> > Hi Group,
>
> > I have one pivot table. In that  I want to show the cumulative sum.
> > e.g.
> > Qtr1 sale:- 10 units
> > Qtr2 sale:- 20 units
> > Qtr3 sale:- 10 units
> > Qtr4 sale:- 30 units
> > now i want to show the sale of qtr1 and qtr2 i.e. 30 units after  qtr2 and
> > similarly after qtr3 cumulative sum of qtr1,2 and 3 i.e. 40 units  and so
> > on.
>
> > How can I do this? Can anyone help me please?
>
> > Thanks & Regards,
> > Alisha malhotra
>
> > --
>
> > --- 
> > ---
> > 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 athttp://www.excel-macros.blogspot.com
> > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > 5. Excel Tips and Tricks athttp://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/pages/discussexcelcom/160307843985936?v=wall&;...
>
>
>
>  Cumulative sum in pivot.xlsx
> 15KViewDownload

-- 
--
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/pages/discussexcelcom/160307843985936?v=wall&ref=ts

$$Excel-Macros$$ Re: Learning Macro

[anandydr]

Hi,

If you do a google search for the term MS Excel VBA tutorials you will
find a lot of websites for that. The best way would be to take a task
that you do repeatedly and start creating a macro for it. Learning
from one project you will eventually learn how to automate a lot of
tasks.

In case you need ebooks or further help on problems you encounter feel
free to post on the group or mail me at anand...@gmail.com

Hope that helps,
Anand Kumar

On Dec 9, 10:31 am, "balbirkumar ."  wrote:
> Dear Friends,
>
> I dont know how create macros in excel and how and what codes are used for
> its creation. But, I am interested in Knowing them. Please suggest me from
> where i could learn.
>
> With
> Thanks & Regards
>
> --
> Balbir Kumar

-- 
--
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/pages/discussexcelcom/160307843985936?v=wall&ref=ts

Re: $$Excel-Macros$$ Re: headcout dashboard report

[anandydr]

Hi,

Sent the dashboard to all those who need it. I have collected these
from internet and none of them were create by me, still they have
helped me a lot in my work.

I have been unable to find a way to attach files to my posts would
some one explain how it is done?

Thanks,
Anand

On Nov 30, 5:39 pm, Ahmed Honest  wrote:
> Dear Anand,
>
> As per your below mail i think that you have mailed to concerned person
> personal email. But would request you to be kind enough to send me as well.
>
> Appreciate and thanks in advance..!!
>
> Regards
> Ahmed Bawazir
>
>
>
>
>
>
>
>
>
> On Tue, Nov 30, 2010 at 8:30 AM, anandydr  wrote:
> > Hi,
>
> > Just mailed you a few dashboards which I had collected from different
> > sources on internet. Hope they solve your visualization problem. In
> > case you have something specific in mind do let us know.
>
> > Regards,
> > Anand Kumar
>
> > On Nov 29, 9:19 pm, bhavya khanna  wrote:
> > > Hi All,
>
> > > Can someone explain about headcount report in dashboard .how to make . If
> > > some one has idea plz explain about that or send useful sheet.
>
> > > Thanks
>
> > --
>
> > --- 
> > ---
> > 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 athttp://www.excel-macros.blogspot.com
> > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > 5. Excel Tips and Tricks athttp://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/pages/discussexcelcom/160307843985936?v=wall&;...
>
> --
>  Ahmed Bawazir
> *احمد باوزير*

-- 
--
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/pages/discussexcelcom/160307843985936?v=wall&ref=ts

Re: $$Excel-Macros$$

[anandydr]

Hi,

Just saw your query and the solution both. Doing this would be simple
though.

> Then you have formed a separate drop down column in G2 putting the months as
> reference - *How did you do this particular step??*

This step is done through data validation. In Excel 2007 you can find
this in Data tab on the ribbon. In 2003 version it can be found in
Data menu. Select List in the Allow: drop down box and in the source
you type in 1,2,3,412 click on OK and you will have a drop down
box

> *It also seems that you have done conditional formatting in the main table.
> But how is that only the month which am sorting is showing red in
> colour??You need to explain me this step too.

Correctly guessed it is conditional formatting in the main table. In
the Select a rule type select "use a formula for cells to format" in
the formula type =Month($D2)=$G$2 in the format section choose the
font and fill type for the cell and you are done.

In case you need further explanation feel free to reach me at
anand...@gmail.com. Hope that helps,

@Dave : Perfect solution. Neat and clean work, my appreciations for
the solution

Regards
Anand Kumar

On Nov 29, 8:58 pm, Anindya Roy  wrote:
> Thanks a million Dave. You have just served it on a platter for me.
>
> Simply awesome.
>
> But would request you to explain the steps if possible as I am a begginer in
> excel. What I could decipher is that
>
> You have formed a separate column and have put the formula.
>
> Then you have formed a separate drop down column in G2 putting the months as
> reference - *How did you do this particular step??*
>
> *It also seems that you have done conditional formatting in the main table.
> But how is that only the month which am sorting is showing red in
> colour??You need to explain me this step too.
> *
> *Regards*
> *Anindya
>
> *
> On Mon, Nov 29, 2010 at 2:44 PM, Dave Bonallack
> wrote:
>
>
>
>
>
>
>
> > Hi,
> > Not quite sure what you mean by "sort out names" but have a look at the
> > attached. You can select the month you want by using the drop-down in cell
> > G2 (1 to 12)
> > The main table contains Conditional Formatting, while Col E contains
> > formulas.
> > Regards - Dave.
>
> > --
> > Date: Sun, 28 Nov 2010 23:15:14 +0530
> > Subject: $$Excel-Macros$$
> > From: anind...@gmail.com
> > To: excel-macros@googlegroups.com
>
> > Suppose I have a file which contains names of the candidates along with
> > their date of birth. Now if i want to sort out names which have birthday
> > falling in the month of november.
>
> > How do I do that.
>
> > Please note that DOB is mm/dd/ format.
>
> >      S.No
> >  Beneficiary Name
> >  Ward No
> >  DOB
> >  1
> >  Ambarish Ghosh
> >  11
> >  12/8/1985
> >  2
> >  Anirban Chakraborty
> >  50
> >  7/13/1991
> >  3
> >  Anasuya Das
> >  21
> >  6/21/1981
> >  4
> >  Ananya Dasgupta
> >  37
> >  8/28/1984
> >  5
> >  Asish Majhi
> >  41
> >  2/11/1990
> >  6
> >  Amit Kumar Sharma
> >  27
> >  2/12/1984
> >  7
> >  Abhijit Golui
> >  37
> >  9/30/1989
> >  8
> >  Bidisha Manik
> >  12
> >  7/19/1985
> >  9
> >  Baishakhi Mallick
> >  9
> >  4/15/1986
> >  10
> >  Biswajit Paul
> >  10
> >  9/18/1984
> >  11
> >  Barnali Roy
> >  19
> >  11/11/1976
> >  12
> >  Dipanjan Karar
> >  18
> >  1/20/1988
> >  13
> >  Jesmine Mondal
> >  39
> >  12/18/1985
> >  14
> >  Kabita Mukherjee
> >  38
> >  8/24/1984
> >  15
> >  Kabita Roy Bagchi
> >  41
> >  8/12/1980
> >  16
> >  Kalpana Dey
> >  48
> >  5/6/1983
> >  17
> >  Kartick Ghosh
> >  48
> >  11/17/1988
> >  18
> >  Kuntal Mukherjee
> >  50
> >  5/5/1982
> >  19
> >  Madhumita Das
> >  39
> >  6/4/1985
> >  20
> >  Madhumita Sardar
> >  39
> >  5/3/1986
> >  21
> >  Moumita Debnath
> >  34
> >  12/23/1988
> >  22
> >  Poly Mukherjee
> >  45
> >  9/16/1988
> >  23
> >  Papia Nath
> >  49
> >  10/21/1979
> >  24
> >  Rituparna Pal
> >  22
> >  12/6/1983
> >  25
> >  Rini Banerjee
> >  33
> >  4/21/1988
> >  26
> >  Shrabani Dey
> >  40
> >  8/5/1975
> >  27
> >  Subhajit Sheet
> >  49
> >  3/10/1991
> >  28
> >  Soma Ghosh
> >  24
> >  12/19/1979
>
> > Thanks & Regards
> > Anindya Roy
>
> > --
>
> > --- 
> > ---
> > 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 athttp://www.excel-macros.blogspot.com
> > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > 5. Excel Tips and Tricks athttp://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/pages/discussexcelcom/160307843985936?v=wall&;...
>
> > --
>
> > --- 
> > --

$$Excel-Macros$$ Re: issue with Data validation

[anandydr]

I have mailed you back your sheet as I have been unable to attach it
here ... I have used an IF ( ) function to solve your problem in case
you need any assistance in implementing the solution or there is
something which is not proper pleas let me know.

Regards,
Anand Kumar

On Nov 29, 3:26 pm, anil panchal  wrote:
> Dear All,
>
> Please help me on Data Vadidation :-)
>
> I have issue in multi validation I want in first cell A1 Anil & B1 I want
> list of anil like 5 name & second is A2 Sunil & B2 list 4 names.
>
> Thanks & Regards
>
> Anil Kumar
>
>  
>
>  issue with validation.xlsx
> 10KViewDownload

-- 
--
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/pages/discussexcelcom/160307843985936?v=wall&ref=ts

$$Excel-Macros$$ Re: headcout dashboard report

[anandydr]

Hi,

Just mailed you a few dashboards which I had collected from different
sources on internet. Hope they solve your visualization problem. In
case you have something specific in mind do let us know.

Regards,
Anand Kumar

On Nov 29, 9:19 pm, bhavya khanna  wrote:
> Hi All,
>
> Can someone explain about headcount report in dashboard .how to make . If
> some one has idea plz explain about that or send useful sheet.
>
> Thanks

-- 
--
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/pages/discussexcelcom/160307843985936?v=wall&ref=ts

$$Excel-Macros$$ Re: time running

[anandydr]

A lot of information would be available on a google search. Depending
on how you want to use it, like in a cell or on a user form the code
would change by a bit.
If you can provide details someone can surely provide the code. It
would be in a module and code would be something like:

Public TimeOnOFF As Boolean

Sub Timer()

TimeOnOFF = TRUE
Dim S As Integer
While TimeOnOFF = TRUE
If Second(Now) > S Or Second(Now) = 0 Then
'  Run code
'
'for example
'Sheets("shee1").Range("A1").Value = Time

S = Second(Now)
End If
DoEvents
Wend

End Sub

Hope that helps

Regards,
Anand

On Nov 17, 10:35 pm, ratu elissa  wrote:
> hot to create time update per second in vba

-- 
--
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/pages/discussexcelcom/160307843985936?v=wall&ref=ts

$$Excel-Macros$$ Re: Grouping 20 ppl in 4 groups with 5 ppl in each, and repeat this 5 times - aim for as few repeats as possible..

[anandydr]

Hi,

In case you are using Excel 2007 or can use Analysis ToolPac you can
use =randbetween (lower_limit, upper_limit)  with 1 as lower and 5 as
upper limit. Otherwise you can use =int(rand( )*100) to generate
random integers then divide the numbers by 2, 3, 4, 5, and 6 and
select people based on the result, like if prime no select or if even
no select.

Hope that helps,
Regards,
Anand

On Nov 14, 11:26 pm, Beanbag  wrote:
> Hi,
>
> I have 20 ppl that i need to put in 4 groups with 5 each:
>
> First time
> Group 1: 1,2,3,4,5
> Group 2: 6,7,8,9,10
> Group 3: 11,12,13,14,15
> Group 4: 16,17,18,19,20
>
> The next week i want to form 4 new groups. However aiming to have as
> few ppl working together with ppl that they were in group with
> previously.
>
> And the following week same issue. New groups, as few repeats as
> possible.
>
> I have been able to do a lookup-matrix that can tell me how will i am
> doing with the ambition of no repeats.
> However, as i am not doing very well, i was wondering if it was
> possible to do an automatic allocation of ppl based on the few simple
> rules above?
>
> Is it?
>
> br
> Thomas

-- 
--
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/pages/discussexcelcom/160307843985936?v=wall&ref=ts

$$Excel-Macros$$ Re: How to change multiple values

[anandydr]

What if you do " * 0.4 " or " * 40% " in next column and then copy it
and use Paste special > Values on the column??? Will this solution
work?

Regards,

Anand

On Nov 9, 11:12 am, Vaaibhav  wrote:
> Hello saggi
>
> I think you must have given this reply considering that I have only 4
> rows to be changed. What if I have 4000 rows to be changed at same
> point of time?
>
> Kindly help if you can.
>
> Regards
> Vaaibhav
>
> On Nov 8, 4:51 pm, saggi  wrote:
>
>
>
> >  You can change Manually... Do F2 then = existing value * 40%
> > Else through macro it can easily possible
>
> > On Nov 8, 10:36 am, Vaaibhav  wrote:
>
> > > Hello All
>
> > > Is there any shortcut for changing values in various cells (same
> > > column) to a same multiplier. I am just elaborationg - say in Column A
> > > i have 4, 5, 6, 10 written in first four rows. I want to change these
> > > values to 40% in a single shot. Is that possible? I dont want to copy
> > > and paste the column and then calculate etc.
>
> > > Pls help if this is having a solution.
>
> > > Regards
> > > Vaaibhav- Hide quoted text -
>
> - Show quoted text -

-- 
--
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/pages/discussexcelcom/160307843985936?v=wall&ref=ts

$$Excel-Macros$$ Re: OPEN EXCEL - Needs your support and enthusiasm...

[anandydr]

Just downloaded the file. Let me play around with it for some time
before I can add any comment on its utilities. Somehow I was able to
see a few utilities which would be quite useful.

Warm regards,
Anand

On Oct 31, 10:57 pm, Ashish Jain  wrote:
> Dear Members,
>
> We're proud to launch OPEN EXCEL under the GNU Public License v3 which
> means it's available with source code to reach the maximum users
> around the globe with rapid development, more robust & innovative
> features. Since, public forums like these are hub of great
> professionals, knowledge sharers and bug shooters, hence we thought to
> launch it here. While beginners may download it, use it and can learn
> from it, amateur, experienced and professionals may contribute their
> some time and mind in developing it further ...
>
> Below are Google project links to download, develop and suggest new
> features in OPEN EXCEL :)
>
> Download -http://code.google.com/p/openexcel/downloads/list
> Suggest New Feature -http://code.google.com/p/openexcel/issues/list
> Develop - Reply this post with your mail id, so that you can be added
> as developer in the project and project's mailing lists.
>
> Thanks & Regards
> Ashish Jain
> McKinsey India Knowledge Center
> (Microsoft Certified Application Specialist)
> (Microsoft Certified 
> Professional)http://www.excelitems.comhttp://www.openexcel.com

-- 
--
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/pages/discussexcelcom/160307843985936?v=wall&ref=ts

$$Excel-Macros$$ Re: New Member

[anandydr]

Welcome Ted,

Please feel free to post your queries. Hope we all learn together

Warm regards,
Anand Kumar

On Oct 26, 7:47 pm, Ted  wrote:
> Hi, just joined the group to take a look at what I could learn.  I'll
> be posting any questions I have shortly.

-- 
--
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/pages/discussexcelcom/160307843985936?v=wall&ref=ts

Re: $$Excel-Macros$$ data cleaning in excel

[anandydr]

Good solution Dililp,

The data scenario sure presents the conflict you have mentioned. What
can be done to work around it?

Warm regards,
Anand Kumar
anand...@gmail.com

On Oct 27, 11:26 am, SAJID MANSOOR 
wrote:
> Good!
>
> On Sun, Dec 13, 2009 at 6:01 PM, Dilip Pandey  wrote:
> > Ok,  The solution is revised as per your needs. One thing the data scenario
> > given by you has some conflict.  Explained below:-
>
> > 11/1/09                  123456  -> duplicate
> > 10/19/09                123456  -> original
> > 12/1/09                  123456  -> duplicate
> > Let me know if I am correct in understanding.  Thanks.
>
> > --
> > DILIP KUMAR PANDEY
> >    MBA-HR,B COM(Hons.),BCA
> > Mobile: +91 9810929744
> > dilipan...@gmail.com
> > dilipan...@yahoo.com
> > New Delhi - 110062
>
> > On 12/11/09, azim...@gmail.com  wrote:
>
> >> Thanks Dilip sir for your help. however what i am looking for is a
> >> logic which will get duplicates. there are two condition for this.
> >> first telephone numbers should be repeated. secondly, only those
> >> entries will be duplicate where date is later one. say for example 123
> >> is a telephone number which is repeated more than once, then we have
> >> to mark the most earlier earlier date as original and all later dates
> >> as duplicate. data here is unorganized for which earlier date may have
> >> been recorded after the later date. continuing with the above example.
>
> >> 11/1/09                  123456
> >> 10/19/09                123456
>
> >> 12/1/09                  123456
> >> 11/1/09                  456789
> >> 12/1/09                  456789
> >> 10/1/09                  456789
>
> >> additional information: i may not be able to play with source data
> >> which means and i can't sort it. we use excel 2003 version and i am
> >> looking for solution in function. as i am not well versed with VBA, i
> >> may not be able to reproduce it. so solution in function will help me
> >> in future also.
>
> >> Regards,
>
> >> Azim
>
> >> On Dec 11, 12:41 pm, Dilip Pandey  wrote:
> >> > Dear Azimullah,
>
> >> > Choose any of the below formual and paste it in third column i.e, column
> >> C
> >> > Row 2 onwards and you are done.
>
> >> > =IF(SUMPRODUCT(($B$1:B2=B3)*($A$1:A20,"Duplicate","Unique")
> >> > OR,
> >> > =IF(COUNTIF($B$1:B1,B2)=0,"Unique","Duplicate")
>
> >> > solved workbook is also attached.
>
> >> > Best Regards,
> >> > --
> >> > DILIP KUMAR PANDEY
> >> >    MBA-HR,B COM(Hons.),BCA
> >> > Mobile: +91 9810929744
> >> > dilipan...@gmail.com
> >> > dilipan...@yahoo.com
> >> > New Delhi - 110062
>
> >> > On 12/10/09, azim...@gmail.com  wrote:
>
> >> > > Hi i have market research data that i need to clean in terms of
> >> > > finding out duplicate surveys. there are two criteria for this.
> >> > > firstly telephone number from which it was done. if it is repeated
> >> > > more han once then it means its duplicate, its the first criteria.
> >> > > second criteria is date. so if there is a single phone number repeated
> >> > > twice, then the earlier date/time should be original survey and later
> >> > > date/time to be counted as duplicate.
>
> >> > > example
>
> >> > > Date                       Phone number
> >> > > 11/1/09                   123456
> >> > > 12/1/09                   123456
> >> > > 11/1/09                   456789
> >> > > 12/1/09                   456789
>
> >> > > Thanks in advance!
>
> >> > > --
>
> >> ---
> >> ---
> >> > > Some important links for excel users:
> >> > > 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
> >> > >http://www.excelitems.com
> >> > > 2. Excel tutorials athttp://www.excel-macros.blogspot.com
> >> > > 3. Learn VBA Macros athttp://www.vbamacros.blogspot.com
> >> > > 4. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>
> >> > > To post to this group, send email to excel-macros@googlegroups.com
> >> > > If you find any spam message in the group, please send an email to:
> >> > > Ayush Jain  @ jainayus...@gmail.com or
> >> > > Ashish Jain @ 26may.1...@gmail.com
>
> >> > > <><><><><><><><><><><><><><><><><><><><><><>
> >> > > HELP US GROW !!
>
> >> > > We reach over 6,500 subscribers worldwide and receive many nice notes
> >> about
> >> > > the learning and support from the group. Our goal is to have 10,000
> >> > > subscribers by the end of 2009. Let friends and co-workers know they
> >> can
> >> > > subscribe to group at
> >> > >http://groups.google.com/group/excel-macros/subscribe
>
> >> >  Identifying repeated survey - By DILipandey.xls
> >> > 18KViewDownload
>
> >  --
>
> > --
> > Some important links for excel users:
> > 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
> >http://www.excelitems.com
> > 2. Excel tutorials athttp://www.excel-macros.blogspot.com
> > 3. Learn VBA Macros athttp://www.vbamacros.blogspot.com
> 

Re: $$Excel-Macros$$ Re: Sales Territory's MAP

[anandydr]

www.tushar-mehta.com

Mr Tushar Mehta is an MVP, and his website has many useful examples on
graphs. once you open the site please go to charts and scroll down.
You will be able to see a map in different colors, which is named
conditional colors.
The web page had been moved so it didn't work properly earlier. You
can also try this update. This works for me...

http://www.tushar-mehta.com/publish_train/xl_vba_cases/0301-dashboard-conditional_shape_colors.htm

Regards,
Anand

On Oct 26, 5:39 pm, ratu elissa  wrote:
> The url's can't be opened
>
> On Tue, Oct 26, 2010 at 1:35 PM, anandydr  wrote:
> > Hi,
>
> > Please see Tushar Mehta's website.
>
> >http://www.tushar-mehta.com/excel/charts/0301-dashboard-conditionalsh...
>
> > Check this site for creating the kind of map you want. I hope this
> > would help.
>
> > Anand Kumar
>
> > On Oct 25, 2:57 pm, sushilck  wrote:
> > > you can provide the you sale territory list will do VBA coding and
> > > send across to you.
>
> > > On Oct 25, 12:25 pm, Kashan Abbas  wrote:
>
> > > > Dear All,
> > > > I want to create sale territory map .Could you please suggest me any
> > free software or any other solution through which i could perform this task
> > effectively .
>
> > > > Your prompt response is needed
> > > > Thanks.
>
> > --
>
> > --
> > 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 athttp://www.excel-macros.blogspot.com
> > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > 5. Excel Tips and Tricks athttp://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/pages/discussexcelcom/160307843985936?v=wall&;...

-- 
--
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/pages/discussexcelcom/160307843985936?v=wall&ref=ts

$$Excel-Macros$$ Re: Sales Territory's MAP

[anandydr]

Hi,

Please see Tushar Mehta's website.

http://www.tushar-mehta.com/excel/charts/0301-dashboard-conditionalshapecolors.htm

Check this site for creating the kind of map you want. I hope this
would help.

Anand Kumar

On Oct 25, 2:57 pm, sushilck  wrote:
> you can provide the you sale territory list will do VBA coding and
> send across to you.
>
> On Oct 25, 12:25 pm, Kashan Abbas  wrote:
>
> > Dear All,
> > I want to create sale territory map .Could you please suggest me any free 
> > software or any other solution through which i could perform this task 
> > effectively .
>
> > Your prompt response is needed
> > Thanks.

-- 
--
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/pages/discussexcelcom/160307843985936?v=wall&ref=ts

Re: $$Excel-Macros$$ Help on Graph

[anandydr]

If we put =na( ) in the empty cell against May month the graph would
become straight, otherwise the average of april and june month may be
inserted there.

On Oct 21, 6:03 pm, Danilo  wrote:
> Instead of a blank cell, insert the average between the previous and
> the subsequent period.
> DV
>
> On Oct 21, 7:25 am, Shrinivas Shevde  wrote:
>
> > Thanks Ajay and Vijaykumar,Shiv kumar
> > Both Option I can not use (Pivot Table and 0 values)
> > Is there any another option any setting
> > Shrinivas
>
> > On Wed, Oct 20, 2010 at 11:13 PM, P.VIJAYKUMAR  wrote:
> > > Dear Srinivas,
>
> > > Just keep zeros whereever there are no values.
>
> > > Regards,
> > > Vijaykumar
>
> > >  On Wed, Oct 20, 2010 at 12:17 PM, Shrinivas Shevde <
> > > shrinivas...@gmail.com> wrote:
>
> > >> Dear All,
> > >> Can Any one help me on graph ?
> > >> Thanks in advance.
> > >> --
> > >> Shrini
>
> > >> --
>
> > >> --
> > >> 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 athttp://www.excel-macros.blogspot.com
> > >> 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > >> 5. Excel Tips and Tricks athttp://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/pages/discussexcelcom/160307843985936?v=wall&;...
>
> > > --
>
> > > P.VIJAY KUMAR
>
> > > --
>
> > > --
> > > 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 athttp://www.excel-macros.blogspot.com
> > > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > > 5. Excel Tips and Tricks athttp://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/pages/discussexcelcom/160307843985936?v=wall&;...
>
> > --
> > Shrini

-- 
--
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/pages/discussexcelcom/160307843985936?v=wall&ref=ts

Re: $$Excel-Macros$$ Text to collumn

[anandydr]

Hi,

The solution provided does not require a VBA code. Inbuilt facilities
of Excel can do that as well. There's a feature called text to Columns
in data tab in 2007 version and in data menu in previous ones. Select
the first column select Text to Colums. Click on Delimited in option
button click next in the menu provided on dialog box tick mark Tab and
Space click on next and finish. Excel will automatically separate each
value based on space into different cells. Hope that helps

Warm regards,
Anand Kumar

On Oct 14, 5:16 pm, SUMIT VYAS  wrote:
> Mr. Naveen
>
> Please explain to Mr. Shrinivas.
>
> i am request please provide vba code.
>
> Regards
>
> Sumit
>
> On Thu, Oct 14, 2010 at 5:17 PM, shrinivas shevde
> wrote:
>
> >  Please Can I know how to do it?
>
> > shrinivas
>
> > --
> > Date: Thu, 14 Oct 2010 13:16:47 +0530
> > Subject: Re: $$Excel-Macros$$ Text to collumn
> > From: nsharma110...@gmail.com
> > To: excel-macros@googlegroups.com
>
> > It is resolved. Please have a look
>
> > Naveen
> > 98219 80081
>
> > On Wed, Oct 13, 2010 at 11:59 AM, Kashan Abbas  > > wrote:
>
> > Dear  All,
>
> > I have extracted a report from sap .The first column has been merged with
> > multiple information for which i need separe column.
>
> > The detail of information in first column is given as first few character
> > represent product name ,then SAE level e.g (SAE 40,SAE 10W90) and then
> > packing size e.g (4L Can and 205LDrum) .I just want to have seprate collumn
> > for each given information.I am using text to collumn option but could not
> > able produce desired output.
>
> > For you working file have been attached.Please suggest me best possible
> > solution on urgent basis.
>
> > Need of your usual cooperation .
>
> > Regards ,
>
> > Kashan Abbas .
>
> > --
>
> > --
> > 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 athttp://www.excel-macros.blogspot.com
> > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > 5. Excel Tips and Tricks athttp://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/pages/discussexcelcom/160307843985936?v=wall&;...
>
> > --
>
> > --
> > 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 athttp://www.excel-macros.blogspot.com
> > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > 5. Excel Tips and Tricks athttp://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/pages/discussexcelcom/160307843985936?v=wall&;...
>
> > --
>
> > --
> > 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 athttp://www.excel-macros.blogspot.com
> > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > 5. Excel Tips and Tricks athttp://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/pages/discussexcelcom/160307843985936?v=wall&;...

-- 
--
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/pages/discussexcelcom/160307843985936?v=wall&ref=ts

Re: $$Excel-Macros$$ good evening to all experts

[anandydr]

In case you don't need historical data you can use this.. replacing
date should work fine in most cases...

On Oct 14, 11:04 am, Dilip Pandey  wrote:
> Oh. . . . . that's awsome  :)
>
> It is a good approach if u dont want to keep the history (previous month).
>
> Best Regards,
> DILIPandey
>
> On 10/13/10, renuka chari  wrote:
>
>
>
>
>
> > HI DILIP THANKS
> > FINALLY I GOT A ANSWER WITH OUT USING FORMULA SELET ENTIRE COL AND FIND
> > (CTRL+F) ENTER 10/11/2010 & REPLACE 11/11/2010 THEN FINALLY I GOT THAT.
>
> > On 12 October 2010 15:23, Dilip Pandey  wrote:
>
> >> Hi Renuka,
>
> >> Try using following formula.
>
> >> =TEXT(DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)),"mm/dd/")&"
> >> "&TEXT(TIME(HOUR(A1),MINUTE(A1),SECOND(A1)),"hh:mm am/pm")
>
> >> Let me know if this solves your problem :)
>
> >> Best Regards,
> >> DILIPandey
>
> >> On 10/11/10, renuka chari  wrote:
> >> > hi excel experts i have a small problem
>
> >> > MY DATE AND TIME FORMAT IS "mm/dd/ hh:mm am/pm"
> >> > I WANT
> >> > TO CHANGE COL: A CHANGED IN TO COL:A"
> >> > =
> >> > col:a                                          col:a
> >> > =
>
> >> > 10/11/2010 18:37                       11/11/2010 18:37
> >> > 10/11/2010 18:30              11/11/2010 18:30
> >> > 10/11/2010 18:40              11/11/2010 18:40
> >> > 10/11/2010 18:50              11/11/2010 18:50
> >> > 10/11/2010 18:23              11/11/2010 18:23
> >> > 10/11/2010 18:10              11/11/2010 18:10
> >> > 10/11/2010 18:09      >   11/11/2010 18:09
> >> > 10/11/2010 18:07              11/11/2010 18:07
> >> > 10/11/2010 18:01              11/11/2010 18:01
> >> > 10/11/2010 17:37              11/11/2010 17:37
> >> > 10/11/2010 18:33              11/11/2010 18:33
> >> > 10/11/2010 17:03              11/11/2010 17:03
> >> > 10/11/2010 21:37              11/11/2010 21:37
> >> > 10/11/2010 19:37              11/11/2010 19:37
>
> >> > i want change month only
> >> > thanks in advance
>
> >> > --
>
> >> ---
> >>  ---
> >> > 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 athttp://www.excel-macros.blogspot.com
> >> > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> >> > 5. Excel Tips and Tricks athttp://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/pages/discussexcelcom/160307843985936?v=wall&;...
>
> >> --
> >> Thanks & Regards,
>
> >> DILIP KUMAR PANDEY
> >>  MBA-HR,B.Com(Hons),BCA
> >> Mobile: +91 9810929744
> >> dilipan...@gmail.com
> >> dilipan...@yahoo.com
> >> New Delhi - 62, India
>
> --
> Sent from my mobile device
>
> Thanks & Regards,
>
> DILIP KUMAR PANDEY
>   MBA-HR,B.Com(Hons),BCA
> Mobile: +91 9810929744
> dilipan...@gmail.com
> dilipan...@yahoo.com
> New Delhi - 62, India

-- 
--
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/pages/discussexcelcom/160307843985936?v=wall&ref=ts

Re: $$Excel-Macros$$ good evening to all experts

[anandydr]

Hi,

The formula by Dilip works perfectly on the sample data provided,
can't think of a better solution.

Warm regards,
Anand Kumar

On Oct 12, 2:53 pm, Dilip Pandey  wrote:
> Hi Renuka,
>
> Try using following formula.
>
> =TEXT(DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)),"mm/dd/")&"
> "&TEXT(TIME(HOUR(A1),MINUTE(A1),SECOND(A1)),"hh:mm am/pm")
>
> Let me know if this solves your problem :)
>
> Best Regards,
> DILIPandey
>
> On 10/11/10, renuka chari  wrote:
>
>
>
> > hi excel experts i have a small problem
>
> > MY DATE AND TIME FORMAT IS "mm/dd/ hh:mm am/pm"
> > I WANT
> > TO CHANGE COL: A CHANGED IN TO COL:A"
> > =
> > col:a                                          col:a
> > =
>
> > 10/11/2010 18:37                       11/11/2010 18:37
> > 10/11/2010 18:30           11/11/2010 18:30
> > 10/11/2010 18:40           11/11/2010 18:40
> > 10/11/2010 18:50           11/11/2010 18:50
> > 10/11/2010 18:23           11/11/2010 18:23
> > 10/11/2010 18:10           11/11/2010 18:10
> > 10/11/2010 18:09   >        11/11/2010 18:09
> > 10/11/2010 18:07           11/11/2010 18:07
> > 10/11/2010 18:01           11/11/2010 18:01
> > 10/11/2010 17:37           11/11/2010 17:37
> > 10/11/2010 18:33           11/11/2010 18:33
> > 10/11/2010 17:03           11/11/2010 17:03
> > 10/11/2010 21:37           11/11/2010 21:37
> > 10/11/2010 19:37           11/11/2010 19:37
>
> > i want change month only
> > thanks in advance
>
> > --
> > --
> > 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 athttp://www.excel-macros.blogspot.com
> > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > 5. Excel Tips and Tricks athttp://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/pages/discussexcelcom/160307843985936?v=wall&;...
>
> --
> Thanks & Regards,
>
> DILIP KUMAR PANDEY
>   MBA-HR,B.Com(Hons),BCA
> Mobile: +91 9810929744
> dilipan...@gmail.com
> dilipan...@yahoo.com
> New Delhi - 62, India

-- 
--
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/pages/discussexcelcom/160307843985936?v=wall&ref=ts

$$Excel-Macros$$ Re: Losing weight

[anandydr]

Hi,

Templates are available on Microsoft online. You can download them for
free.
Hope that helps.

Warm regards,
Anand Kumar

On Oct 8, 6:44 pm, Shimmy  wrote:
> Hi Excellers, I am having a problem, I am trying to lose some weight,
> it is for tha reason that I would like to request anyone of you to
> send me a template that I can use to check my weight, either in excel
> of access format- diet tips could come in handy alongside that
> template. Preferably the tips should be in either PDF format or on a
> word processor.
>
> Your  help in this regard would come in handy.
>
> Thank you in advance!

-- 
--
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/pages/discussexcelcom/160307843985936?v=wall&ref=ts

$$Excel-Macros$$ Re: Microsoft Excel help needed for newbe

[anandydr]

Dear Dean Brown,

We would be glad to provide whatever support we can. Please feel free
to post your queries.

Warm regards,
Anand Kumar

On Oct 5, 11:53 am, Dean Brown  wrote:
> Hi group.
>
> I'm by no means a programmer. I'm trying to knock together a (what
> should be) simple spreadsheet in Microsoft Excel for a calculation I
> have to do on a regular basis. I'm hoping that some kind people within
> this group will be able to help me with some formulas and questions.
>
> If anyone is able to help me could you please contact me at
> fflatthefat...@hotmail.co.uk
>
> TIA
>
> Dean.

-- 
--
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/pages/discussexcelcom/160307843985936?v=wall&ref=ts

$$Excel-Macros$$ Re: Budget sheet

[anandydr]

Conditional formatting is what has been used here in your budget
sheet. Select the area which you want to format. With Excel 2007
onwards you can use Jim's method in case you use older version
Conditional format is an option under Format Menu. Somehow 2007 and
newer version have a lot more options than older ones.

Hope that helps,
Anand Kumar

On Oct 2, 9:26 pm, neil johnson  wrote:
> Dear Everyone,
>
> There is budget sheet . I need your help . In sheet Mention BT summary sheet
>  . There is symbol of ► like this . This symbol has three colors red ,
> yellow , green according the variance . Its symbol color should be change
> according to variance. If someone make report of budget , then tell me
> please how we do this.
>
> Thanks and Regards
>
>  budget1.xls
> 357KViewDownload

-- 
--
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/pages/discussexcelcom/160307843985936?v=wall&ref=ts

Re: $$Excel-Macros$$ Ayush Jain – Microsoft MVP 20 10

[anandydr]

Congrats Ayush,

You quite well deserved...

Warm regards,
Anand Kumar.

On Oct 4, 9:43 am, Jai  wrote:
> U deserve this Friend.
>
> On Mon, Oct 4, 2010 at 9:35 AM, C.G.Kumar wrote:
>
>
>
> > Congratulation friend on reaching a milestone. Hope may more to come 
>
> >  On Sun, Oct 3, 2010 at 3:47 PM, Ayush  wrote:
>
> >> Dear Group,
>
> >> I am proud to announce that I am awarded Microsoft Most Valuable
> >> Professional MVP on October 01, 2010 for providing technical expertise
> >> in Excel technical communities for past one year.
>
> >>http://blogs.technet.com/b/southasiamvp/archive/2010/10/01/new-mvps-a...
>
> >> This is a prestigious award from Microsoft given every Quarter to the
> >> people who share their deep knowledge, real-world experience, and
> >> impartial, objective feedback to help people enhance the way they use
> >> technology.
>
> >> My deep thanks to each group member for all your support and
> >> contribution to this group.
>
> >> Best regards,
> >> Ayush Jain
> >> Microsoft MVP
>
> >> --
>
> >> --
> >> 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 athttp://www.excel-macros.blogspot.com
> >> 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> >> 5. Excel Tips and Tricks athttp://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/pages/discussexcelcom/160307843985936?v=wall&;...
>
> > --
> > Regards,
>
> > C.G.Kumar
>
> > --
>
> > --
> > 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 athttp://www.excel-macros.blogspot.com
> > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > 5. Excel Tips and Tricks athttp://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/pages/discussexcelcom/160307843985936?v=wall&;...
>
> --
> Thanks For Mail.

-- 
--
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/pages/discussexcelcom/160307843985936?v=wall&ref=ts

$$Excel-Macros$$ Re: Ayush Jain – Microsoft MVP 20 10

[anandydr]

Congrats Ayush,

Beside congratulating you on your MVP status I would also like to know
how this status is awarded. Although I've been through the website but
still the process for nomination, time i.e. for how long a MVP has
been contributing, or number/quality of post has not been very clear.
Since you are an MVP now, I believe you would be the best person to
describe these.

Warm regards and congrats again,
Anand Kumar

On Oct 3, 3:17 pm, Ayush  wrote:
> Dear Group,
>
> I am proud to announce that I am awarded Microsoft Most Valuable
> Professional MVP on October 01, 2010 for providing technical expertise
> in Excel technical communities for past one year.
>
> http://blogs.technet.com/b/southasiamvp/archive/2010/10/01/new-mvps-a...
>
> This is a prestigious award from Microsoft given every Quarter to the
> people who share their deep knowledge, real-world experience, and
> impartial, objective feedback to help people enhance the way they use
> technology.
>
> My deep thanks to each group member for all your support and
> contribution to this group.
>
> Best regards,
> Ayush Jain
> Microsoft MVP

-- 
--
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/pages/discussexcelcom/160307843985936?v=wall&ref=ts

$$Excel-Macros$$ Re: Help require to understand the Formula of the attached sheet

[anandydr]

Dear Mr Hiren Sheth,

Perhaps you are refering to {=TABLE(,B3)} listed in the cells. This is
not an actual function but a table which is part of excel what if
analysis. In Excel 2007 you can go to Data tab > What if analysis >
Table and with older versions this feature is available in Tools menu.

Hope that helps,
Anand Kumar
anand...@gmail.com

On Sep 30, 4:17 pm, Hiren Sheth  wrote:
> Hi Experts,
>
> Can you please explain formula of cell B17, B18, B19 and B20 of the attached 
> sheet.
>
> Thanks
> Hiren
> This message is for the named person's use only. It may contain confidential, 
> proprietary or legally privileged information. No
> confidentiality or privilege is waived or lost by any mis-transmission. If 
> you receive this message in error, please immediately
> delete it and all copies of it from your system, destroy any hard copies of 
> it and notify the sender. You must not, directly or
> indirectly, use, disclose, distribute, print, or copy any part of this 
> message if you are not the intended recipient.
> Viteos Capital Market Services Ltd.and any of its subsidiaries each reserve 
> the right to monitor all e-mail communications through
> its networks. Any views expressed in this message are those of the individual 
> sender, except where the message states otherwise and
> the sender is authorized to state them to be the views of any such entity
>
>  Book8.xlsx
> 13KViewDownload

-- 
--
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/pages/discussexcelcom/160307843985936?v=wall&ref=ts

$$Excel-Macros$$ Re: Please Help

[anandydr]

The solution would need a simple macro. using Button_Click event this
can be done. It just has one trick that is the use of static variable
to store the last row of data used or a loop and condition to check
what value is currently in cell and then select the next value from
dump.
Problem is many organizations don't permit macros what's policy for
Wipro I don't know. Would a macro solution work for you?

Warm regards,
Anand Kumar

On Sep 27, 9:02 pm, hemant shah  wrote:
> Hi Team,
>
> Can you please help on the attached file.
>
> I have explained my query in the attached sheet.
>
> Regards,
> Hemant Shah-- Forwarded message --
> From:  
> Date: Mon, Sep 27, 2010 at 9:25 PM
> Subject: Book1.xls
> To: hemanthin...@gmail.com
>
> Please do not print this email unless it is absolutely necessary.
>
> The information contained in this electronic message and any
> attachments to this message are intended for the exclusive use of the
> addressee(s) and may contain proprietary, confidential or privileged
> information. If you are not the intended recipient, you should not
> disseminate, distribute or copy this e-mail. Please notify the sender
> immediately and destroy all copies of this message and any
> attachments.
>
> WARNING: Computer viruses can be transmitted via email. The recipient
> should check this email and any attachments for the presence of
> viruses. The company accepts no liability for any damage caused by any
> virus transmitted by this email.
>
> www.wipro.com
>
>
>
>  Book1.xls
> 28KViewDownload

-- 
--
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/pages/discussexcelcom/160307843985936?v=wall&ref=ts

$$Excel-Macros$$ Re: Excel chart plotting, depending on weightage

[anandydr]

I copied the data you had in your previous post and tried to plot it
using Excel 2007. 3D line charts are generated correctly if the
temperatures are converted to TEXT instead of NUMBERS, i.e. use a
single quotation mark " ' " before the number like in '10 instead of
10. Column charts and stacked column charts are also generated
correctly.

I hope you have limited temperature values, otherwise scrollable
charts would be required to show the chart. You can also use text
entries for temperature like Ten instead of 10, eleven instead of 11
and so on.

Hope that helps. In case I have not understood your problem correctly
do let me know. I can also be reached at anand...@gmail.com

Warm regards,
Anand Kumar

On Sep 27, 12:02 pm, startingexcel  wrote:
> Thanks anand,
>
> Yes the numbers in the columns of the zones indicate the weight ( i
> mean number of times)
> the temperature range represented in the first column.
>
> This is an attempt to show a viewer a line graph of each zone against
> the temperature.
> All zones thus can be shown with shift in the x coordinates as i don't
> need it. I can send the format if i can attach the document.
>
> Regards and Thanks in Advance
> -- Anand
> On Sep 26, 9:59 pm, anandydr  wrote:
>
> > Where's the weight assigned to each region? Do the numbers below
> > north, south, east and west represent that? In that case what does
> > this table shows?
>
> > On Sep 25, 4:31 am, startingexcel  wrote:
>
> > >  Excel chart plotting, depending on weightage
> > > Hi experts,
> > > Need help in ideas and plotting a graph to show a weightage of
> > > temperatures hit in 4 zones. I cannot relate this to x axis, i want to
> > > represent all in one chart so that i present a viewer, the number of
> > > times a particular temperatures are hit.
> > > Graph showing Temperatures of various regions, the number indicate the
> > > recorded number of Temperatures
> > > Temperature East West North South
>
> > > 10 5 6 23 12
> > > 11 8 3 3 23
> > > 12 10 6 0 34
> > > 13 12 2 2 12
> > > 14 15 2 9 8
> > > 15 19 3 2 4
> > > 16 22 34 3 6
> > > 17 1 0 2 2
> > > 18 0 34 35 6
> > > 19 0 3 2 2
> > > 20 0 0 4 5
> > > 21 23 0 4 2
> > > 22 23 0 2 0
>
> > > Note:Here over a period of time, samples are collected across various
> > > regions and I would like to represent this way
> > > Y axis: Temperature and I do not see anything wrt to x axis or I leave
> > > to the experts.
> > > Looking to clearly show 4 graphs in a single chart which shows a
> > > pattern of temperatures. The more the hits on a particualar
> > > temperatures, the bold is the marker ( diamond, or whatever).
> > > I am thinking if the 4 regions (east, west, north, south) are plotted
> > > at different x co ordinates so that viewer could co-relate with the
> > > temperatures on the Y axis.
>
> > > Thanks a lot in Advance.
> > > L

-- 
--
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/pages/discussexcelcom/160307843985936?v=wall&ref=ts

$$Excel-Macros$$ Re: Excel chart plotting, depending on weightage

[anandydr]

Where's the weight assigned to each region? Do the numbers below
north, south, east and west represent that? In that case what does
this table shows?

On Sep 25, 4:31 am, startingexcel  wrote:
>  Excel chart plotting, depending on weightage
> Hi experts,
> Need help in ideas and plotting a graph to show a weightage of
> temperatures hit in 4 zones. I cannot relate this to x axis, i want to
> represent all in one chart so that i present a viewer, the number of
> times a particular temperatures are hit.
> Graph showing Temperatures of various regions, the number indicate the
> recorded number of Temperatures
> Temperature East West North South
>
> 10 5 6 23 12
> 11 8 3 3 23
> 12 10 6 0 34
> 13 12 2 2 12
> 14 15 2 9 8
> 15 19 3 2 4
> 16 22 34 3 6
> 17 1 0 2 2
> 18 0 34 35 6
> 19 0 3 2 2
> 20 0 0 4 5
> 21 23 0 4 2
> 22 23 0 2 0
>
> Note:Here over a period of time, samples are collected across various
> regions and I would like to represent this way
> Y axis: Temperature and I do not see anything wrt to x axis or I leave
> to the experts.
> Looking to clearly show 4 graphs in a single chart which shows a
> pattern of temperatures. The more the hits on a particualar
> temperatures, the bold is the marker ( diamond, or whatever).
> I am thinking if the 4 regions (east, west, north, south) are plotted
> at different x co ordinates so that viewer could co-relate with the
> temperatures on the Y axis.
>
> Thanks a lot in Advance.
> L

-- 
--
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/pages/discussexcelcom/160307843985936?v=wall&ref=ts

$$Excel-Macros$$ Re: Remove the Space

[anandydr]

Hi,

I am not sure if I understood your problem correctly. If you want to
clean all the leading and trailing spaces from data in column B and
then use them in column C this nested function works perfectly:

=TRIM(SUBSTITUTE(B12,CHAR(160),CHAR(32)))

Hope that helps. In case you need something else please elaborate your
case.

Warm regards,
Anand Kumar.

On Sep 25, 10:28 am, Jai  wrote:
> Dear Groups Member,
>
> I want to Remove the Space, In Cloum C , Sheet Attach, Please Help me.
>
> Thanks for Advance.
>
> --
> Thanks For Mail.
>
>  Remove the Space.xls
> 1213KViewDownload

-- 
--
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/pages/discussexcelcom/160307843985936?v=wall&ref=ts

$$Excel-Macros$$ Re: 3d terminal chart in excel 2007 possibel

[anandydr]

Create a stacked cylinder chart and remove the fill color from upper
portion it will create something close to what you want to achieve.
You can search www.chandoo.org for "Thermometer charts". This will
give you an idea on how to create this kind of chart.
Hope that helps..
In case it doesn't do inform me and send some sample data I'll try to
create one

Warm regards,

Anand Kumar
anand...@gmail.com

On Sep 24, 12:23 pm, ali abbas  wrote:
> Dear all can a possible this type of 3D terminal chart in excel 2007
> If possible please help me and tell complete procedure
>
>  3D TERMINAL CHAR IN XL POSSIBEL.docx
> 84KViewDownload

-- 
--
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/pages/discussexcelcom/160307843985936?v=wall&ref=ts

Re: $$Excel-Macros$$ Insert work sheet and rename when insert.

[anandydr]

The code will surely insert sheets after the last sheet in the
workbook, but what if we want to insert sheets before any particular
sheet ? let's say before active sheet or the fourth sheet. What should
be written instead of Sheets.Add after:=Sheets(Sheets.Count) ???

On Sep 4, 12:40 pm, siti Vi  wrote:
> Sub CreateMoreSheets()
>    '-- by siti Vi / Jakarta, Sept 4, 2010
>    Dim shtArr
>    Dim i As Integer
>    shtArr = InputBox("Type the Sheet's Name, separate by comma (,)")
>    shtArr = Split(shtArr, ",")
>    For i = 0 To UBound(shtArr)
>       Sheets.Add after:=Sheets(Sheets.Count)
>       ActiveSheet.Name = shtArr(i)
>    Next
> End Sub
>
> On Sat, Sep 4, 2010 at 12:48 AM, santanu_ca  wrote:
>
> > Hi
> > I want to insert multiple sheet. Say 10 sheet and I want to rename
> > those sheet as per my choice.
> > That is a window will come and I type my choiceable name then the
> > sheet is created. If there is any macro code for that please help me.
>
> > Thanks
> > Santanu
>
>
>
>  ctv_InsertNewSheets_with_SpecificNames.xls
> 49KViewDownload

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

<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!

We reach over 7000 subscribers worldwide and receive many nice notes about the 
learning and support from the group.Let friends and co-workers know they can 
subscribe to group at http://groups.google.com/group/excel-macros/subscribe

$$Excel-Macros$$ Re: find a value of intersection of two graph in excel

[anandydr]

@ Michael Hayes

Dear Michael,

I went through the solution you sent for Mr Sudhir. Using his
spreadsheet I used the formula you have suggested. I got the results
but not the logic behind. How are we getting results even though the
cells B4 & C5 are blank, and what are these being used for???

Regards,
Anand

On Aug 6, 1:40 am, Michael Hayes  wrote:
> Sudhir,
>
> I am assuming by the subject matter of your posting you need to find
> the X and Y coordinates of any place on the graph where series 1 and
> series 2 intersect. So if I understand your request correctly, this
> should help
>
> Lets put your X axis coordinates in cells B4 through E4. So in cell B4
> enter 1, in cell C4 enter 2 etc.
>
> Then starting in cell C5 put this formula to determine if there is an
> intersection =IF((B2-B3)*(C2-C3)>0,"N/A",B4+(B2-B3)/((B2-C2)-(B3-C3)))
> Copy that formula to cells D5 and E5
>
> That formula will tell your X axis coordinate of intersection (if
> applicable)
>
> Then in cell C6 enter this formula =IF(C5="N/A","N/A",+B2-((B2-C2)*(C5-
> B4))) copy that formula over to cells  D6 and E6
>
> That formula will tell you your Y axis coordinate where the graphs
> intersect (if applicable)
>
> Email directly if you need a copy of the edited spreadsheet.
>
> Michael Hayes
>
> On Aug 5, 9:34 am, sudhir kumar  wrote:
>
> > hi friend,
> > i have a problem , i made two graph in excel sheet intersecting with each
> > other. now i want to highlight the value of the intersection point. plze
> > help
>
> > --
> > Regards-
> > Sudhir Kumar
> > Design Engineer,
> > Conceptia Software Technologies Pvt. Ltd.
> > Email id-sudhir.p...@gmail.com,
>
> >  graph.xls
> > 29KViewDownload

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

<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!

We reach over 7000 subscribers worldwide and receive many nice notes about the 
learning and support from the group.Let friends and co-workers know they can 
subscribe to group at http://groups.google.com/group/excel-macros/subscribe

$$Excel-Macros$$ Re: How to sum numbers in time format

[anandydr]

Hi,

I've just checked the sheet you have attached. Following problems were
there:
1. You have entered time in different formats i.e. the formatting in
column C is not consistent.
2. Excel treats date and time as a number not as time so the
multiplication that you do in column D will never give proper results.
e.g. the Total which shows 5:00 is actually 17.20833 , where 17
represents the date and 20833 represents the time. You can see this by
changing the format of cell to Number. Just right click the cell and
choose Format>Number.

To solve this I've copied the data into Column F using =HOUR( )
formula which would extract the hours from column C which is actually
your minute value. In column G I've used the data in Column F * 0.3
which you have used and results are proper.

HTH,
Anand Kumar



On Jul 16, 1:51 pm, lucky singh  wrote:
> Hi All,
>
> Please me in the attached Sheet to SUm the numbers in Time format and to sum
> the charges for the same...
>
> Thanks in Advance,
> Regards,
> Jay S
> 9987337414
>
>  SUM of Minutes.xls
> 27KViewDownload

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

<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!

We reach over 7000 subscribers worldwide and receive many nice notes about the 
learning and support from the group.Let friends and co-workers know they can 
subscribe to group at http://groups.google.com/group/excel-macros/subscribe

$$Excel-Macros$$ How to create a similar resume in MS Excel ???

[anandydr]

Hello All,

I came across this interesting resume which has been prepared as a
graph and charts sheet / dashboard. Although some may question its
relevance / consistency in color usage / display of information , it
is doing what it means to do... It captures attention and would surely
give the candidate a boost.

http://theportfolio.ofmichaelanderson.com/wp-content/uploads/2008/05/resume-infographic.jpg

I was wondering how this can be created using MS Excel... can someone
help me on this ???

Regards,
Anand Kumar

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

<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!

We reach over 7000 subscribers worldwide and receive many nice notes about the 
learning and support from the group.Let friends and co-workers know they can 
subscribe to group at http://groups.google.com/group/excel-macros/subscribe

Re: $$Excel-Macros$$ Do not request or provide copyrighted eBook in group.

[anandydr]

Yeah... ebooks can be easily downloaded from different sites or
torrent downloads, so even if posting them on group may not be advice-
able I don't see any harm in posting the same to personal email ID if
someone needs them

On May 16, 9:34 am, Mr excel  wrote:
> Yeah Ayush had said is true.
>
>
>
> On Sat, May 15, 2010 at 10:41 AM, Ayush  wrote:
> > Dear Group,
>
> > Please do not send any request of copyrighted material in group. Any
> > ebook/material which is copyrighted should not be shared in forum as
> > it is a loss for the owner of that item.
>
> > You are always welcome to share thoughts, idea , queries, tips and
> > tricks in group. They are not copyrighted at all :)
>
> > I hope you understand the same. Any such message again could lead to
> > termination from the group membership.
>
> > Keep Posting.
>
> > Best regards,
> > Ayush Jain
> > Group Owner
>
> > --
>
> > --
> > 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 athttp://www.excel-macros.blogspot.com
> > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>
> > To post to this group, send email to excel-macros@googlegroups.com
>
> > <><><><><><><><><><><><><><><><><><><><><><>
> > HELP US GROW !!
>
> > We reach over 6,800 subscribers worldwide and receive many nice notes about
> > the learning and support from the group.Let friends and co-workers know they
> > can subscribe to group at
> >http://groups.google.com/group/excel-macros/subscribe
>
> --
> --
> 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 athttp://www.excel-macros.blogspot.com
> 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> HELP US GROW !!
>
> We reach over 6,800 subscribers worldwide and receive many nice notes about 
> the learning and support from the group.Let friends and co-workers know they 
> can subscribe to group athttp://groups.google.com/group/excel-macros/subscribe

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

<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!

We reach over 6,800 subscribers worldwide and receive many nice notes about the 
learning and support from the group.Let friends and co-workers know they can 
subscribe to group at http://groups.google.com/group/excel-macros/subscribe

Re: $$Excel-Macros$$ "Excel 2007 Power Programming with VBA" by John Walkenbach.

[anandydr]

I've got a few ebooks on VBA with MS Excel though not the specific
book you mention. If you can use ebooks I can mail them to you.

Anand

On Mar 24, 12:59 pm, rf1234 rf1234  wrote:
> Dear Nani
> Check This Link 
> Outhttp://www.angelfire.com/biz7/julian_s/julian/julians_macros.htm
>
> this link provide the basic vba concept with code.
> For the book you mension,i have to look it out.
>
> Hope it Will Help
> Happy To Help
> :)
> Shyam
>
> On Wed, Mar 24, 2010 at 7:12 PM, Nani Ram <06n...@gmail.com> wrote:
> > Hi Excel Lovers.
>
> > Could any one have this book "Excel 2007 Power Programming with VBA" by John
> > Walkenbach.
> > If not this book, can any one provide me material to learn basic VBA on
> > Excel.
>
> > Thanks,
>
> > Naniram
>
> > --
> > --
> > Some important links for excel users:
> > 1. Follow us on TWITTER for tips tricks and links :
> >http://twitter.com/exceldailytip
> > 2. Join our Facebook Group @
> >http://www.facebook.com/group.php?gid=287779555678
> > 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>
> > To post to this group, send email to excel-macros@googlegroups.com
>
> > <><><><><><><><><><><><><><><><><><><><><><>
> > HELP US GROW !!
>
> > We reach over 6,800 subscribers worldwide and receive many nice notes about
> > the learning and support from the group.Let friends and co-workers know they
> > can subscribe to group at
> >http://groups.google.com/group/excel-macros/subscribe
>
> > To unsubscribe from this group, send email to
> > excel-macros+unsubscribegooglegroups.com or reply to this email with the
> > words "REMOVE ME" as the subject.
>
> --
>
> Thanks Regards
> Shyam
> Software Engineer
> ''
> When one door of happiness closes, another opens;
>  but often we look so long at the closed door that we do not see the
>  one which has been opened for us.
> - Helen Keller
> ''
> Wenn eine Tür des Glücks schließt, öffnet sich ein weiteres,
> aber oft schauen wir so lange auf die geschlossene Tür,
>  dass wir nicht sehen ein, die für uns geöffnet wurde. - Helen Keller
> ''

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678
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

<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!

We reach over 6,800 subscribers worldwide and receive many nice notes about the 
learning and support from the group.Let friends and co-workers know they can 
subscribe to group at http://groups.google.com/group/excel-macros/subscribe

To unsubscribe from this group, send email to 
excel-macros+unsubscribegooglegroups.com or reply to this email with the words 
"REMOVE ME" as the subject.

$$Excel-Macros$$ Re: COUNTIF Formula Help

[anandydr]

 =SUMIFS(Data!E:E, Data!I:I,A4,  Data!J:J,B4,  Data!D:D,">="&VALUE($B
$1),  Data!D:D,"<="&VALUE($B$2))

The formula is simple and works in 2007 version of excel only. First
parameter [Data!E:E] specifies the range to sum and rest are the
criterion you need to test. As for coumn D Value formula has been used
because MS Excel understands dates as numbers i.e. if the number is
"1.234" then "1" signifies the date and the portion after decimal
which is "234" signifies the time. As a matter of fact specifying
range as D:D or E:E will also not work with previous versions of Excel
and you will have to use Offset and Index functions in combination
with named ranges to create a dynamically increasing range.
This means a lot of learning somehow you can do it in 2-3 days time
and solve your "problem" from recurring in future...
Happy learning !!!

--~--~-~--~~~---~--~~
--
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at 
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 

To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain  @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!

We reach over 6,500 subscribers worldwide and receive many nice notes about the 
learning and support from the group. Our goal is to have 10,000 subscribers by 
the end of 2009. Let friends and co-workers know they can subscribe to group at 
http://groups.google.com/group/excel-macros/subscribe
-~--~~~~--~~--~--~---

$$Excel-Macros$$ Re: Subtract dates

[anandydr]

Somehow if you have date in format hh:mm dd/mm/yy and you try to
subtract you may not get accurate results in such a case use the
following formula. Suppose your start time and date is in cell A1 and
end time and date in cell B1 in C1 type
=B1+ (A1>B1) - A1

Since on subtracting dates Excel doesn't like negative values an error
"" is generated but on use of this formula this problem won't
occur.

On Oct 2, 9:33 pm, "Upendra Singh" 
wrote:
> Hi Huzaifa,
>
> This is very much possible to subtract dates. Simply subtract as you
> subtract no.s and format the cell as "yy-mm-dd".
> This works fine provided you are subtracting dates and not text written as
> date.
>
> Thanks and Regards,
>
> Upendra Singh
> +91-9910227325
>
> -Original Message-
> From: excel-macros@googlegroups.com [mailto:excel-mac...@googlegroups.com]
>
> On Behalf Of Huzaifa
> Sent: Friday, October 02, 2009 11:19 PM
> To: MS EXCEL AND VBA MACROS
> Subject: $$Excel-Macros$$ Subtract dates
>
> Is it possible to subtract dates in Microsoft Excel and get the result
> in yy-mm-dd format? Please help me.
> Thanks

--~--~-~--~~~---~--~~
--
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at 
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 

To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain  @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!

We reach over 6,500 subscribers worldwide and receive many nice notes about the 
learning and support from the group. Our goal is to have 10,000 subscribers by 
the end of 2009. Let friends and co-workers know they can subscribe to group at 
http://groups.google.com/group/excel-macros/subscribe
-~--~~~~--~~--~--~---

$$Excel-Macros$$ Re: Best Excel Tip Award of JUNE goes to *****UPENDRA SINGH*****

[anandydr]

Dear Ayush,

Could you please provide the links to where these tips can be found?

Anand Kumar
09718516200

--~--~-~--~~~---~--~~
--
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at 
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 

To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain  @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!

We reach over 5,000 subscribers worldwide and receive many nice notes about the 
learning and support from the group. Our goal is to have 10,000 subscribers by 
the end of 2009. Let friends and co-workers know they can subscribe to group at 
http://groups.google.com/group/excel-macros/subscribe
-~--~~~~--~~--~--~---

$$Excel-Macros$$ Re: Run a macro on Enter?

[anandydr]

Put your code on Worksheet_onEnter event hope that would be able to
solve your problem.

On May 8, 9:14 am, Akhilesh Karna  wrote:
> The following code may be of help:
>
> open excel
> open visual basic editor
> double click sheet1 (or any other sheet) under microsoft excel objects
> Add the following code
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>     If Intersect(Range("C:C"), Target) Is Nothing Then
>         'do nothing
>     Else
>         MsgBox "it is changed"
>     End If
> End Sub
>
> You can do many thing by just clicking a cell in column C (in this example).
>
> Akhilesh Kumar Karna
>
>
>
> On Fri, May 8, 2009 at 4:58 AM, Doug  wrote:
>
> >  I've got an Excel spreadsheet that I'm going to use a wedge program
> > with to capture data from an instrument and parse it into the sheet. I
> > would like to make it happen without having to use a mouse to click a
> > button. A hot key combo or a form would work but it would be really
> > sweet if I could just press Enter from the correct cell.
>
> > Is it possible to run a macro by simply hitting enter on a cell
> > without even entering any data in the cell? The active cell will
> > always be in column 4.
>
> > None of the answers I found online so far work for me.
>
> > Thanks.
> > Doug- Hide quoted text -
>
> - Show quoted text -

--~--~-~--~~~---~--~~
-
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at 
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 

To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain  @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
-
-~--~~~~--~~--~--~---

$$Excel-Macros$$ Re: Want help on Lookup and pivot tables

[anandydr]

Hi Jayesh,

I've got some ebooks on Excel. If you need them you can have them. In
case you have specific querries all the forum is here to help you.
Regards,
Anand

On Apr 26, 9:44 pm, Aindril De  wrote:
> Hi Jayesh,
>
> Could you please elaborate your exact problem please
>
> Regards,
> Andy
>
> On Sat, Apr 25, 2009 at 4:37 PM, Jayesh Joshi wrote:
>
>
>
> > Hello all ,
> > This is jayesh joshi , i am looking for some help on Lookup and pivot
> > tables as it is required in my job profile , so could some one help me out
> > with the above subjects.
>
> > Thanks
>
> > Jayesh Joshi- Hide quoted text -
>
> - Show quoted text -

--~--~-~--~~~---~--~~
-
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at 
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 

To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain  @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
-
-~--~~~~--~~--~--~---

$$Excel-Macros$$ Re: EXCEL / VBA BOOK REQU

[anandydr]

Hi,

If you can provide more details on what kind of ebooks you need the
books I send would be more specefic. Kindly also mention your email id
at some place visible. Preferably in the message itself.
By specific I mean whether you do statistical or financial analysis or
are you into data entry or senario based analysis? What you use excel
for?

Regards,
Anand Kumar

On Nov 25, 9:26 am, "Sunita Paudel" <[EMAIL PROTECTED]> wrote:
> *Hi All,*
> *Yeah I need that too..if anybdy can send it wud b vry grateful*
> *thnx*
>
> On 11/24/08, M, Madhu : Barclays Finance <[EMAIL PROTECTED]> wrote:
>
>
>
>
>
> >  Dear All,
>
> > Pls any bade having  the excel /vba books
>
> > Pls sent to my mail id
>
> > Kindly do need full
>
> > *Thanking You,***
>
> > *  ** Madhu*
>
> > *  Bangalore HUB***
>
> > *Cell : 91-9743130534*
>
> > This e-mail and any attachments are confidential and intended solely for
> > the addressee and may also be privileged or exempt from disclosure under
> > applicable law. If you are not the addressee, or have received this e-mail
> > in error, please notify the sender immediately, delete it from your system
> > and do not copy, disclose or otherwise act upon any part of this e-mail or
> > its attachments.
>
> > Internet communications are not guaranteed to be secure or virus-free.
> > The Barclays Group does not accept responsibility for any loss arising from
> > unauthorised access to, or interference with, any Internet communications by
> > any third party, or from the transmission of any viruses. Replies to this
> > e-mail may be monitored by the Barclays Group for operational or business
> > reasons.
>
> > Any opinion or other information in this e-mail or its attachments that
> > does not relate to the business of the Barclays Group is personal to the
> > sender and is not given or endorsed by the Barclays Group.
>
> > Barclays Bank PLC.Registered in England and Wales (registered no. 1026167).
> > Registered Office: 1 Churchill Place, London, E14 5HP, United Kingdom.
>
> > Barclays Bank PLC is authorised and regulated by the Financial Services
> > Authority.- Hide quoted text -
>
> - Show quoted text -

--~--~-~--~~~---~--~~
Visit the blog to download Excel tutorials at 
http://www.excel-macros.blogspot.com

To post to this group, send email to excel-macros@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/excel-macros?hl=en

Visit & Join Our Orkut Community at 
http://www.orkut.com/Community.aspx?cmm=22913620

Visit the blog to download Excel tutorials at 
http://www.excel-macros.blogspot.com

To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com

To see the Daily Excel Tips, Go to:
http://exceldailytip.blogspot.com
-~--~~~~--~~--~--~---

$$Excel-Macros$$ Re: Worked hour difference calculation

[anandydr]

If you want to use the same sheet that you have sent you can use the
formula

=IF(LEFT(A1,10)=LEFT(B1,10),RIGHT(B1,8)-RIGHT(A1,8),RIGHT(A1,8)-RIGHT
(B1,8))

When pasted in cell C1 this will calculate the number of hours worked.
Set the format of C1 cell to hh:mm:ss and you can see the correct
result.

Hope that helps,
Anand
09971050827

On Nov 21, 10:06 am, "konark vyas" <[EMAIL PROTECTED]> wrote:
> Dear Satish,
>
> FInd attached is the solution. have doe it in the first row. Kindly put the
> time and and time out figures for the rest of the cells
>
> Regards,
>
> Konark Vyas
>
> On 11/21/08, Sathish Jalendran <[EMAIL PROTECTED]> wrote:
>
>
>
>
>
> >  Hi,
>
> > Can you please let me know how to find the actual worked hour difference
> > between 2 dates have attached the sample file my day starts at 8 am in the
> > morning and ends at 8pm in the evening
>
> > Regards
>
> > Sathish Jalendran
>
> > The information contained in this electronic message and any attachments to
> > this message are intended for the exclusive use of the addressee(s) and may
> > contain proprietary, confidential or privileged information. If you are not
> > the intended recipient, you should not disseminate, distribute or copy this
> > e-mail. Please notify the sender immediately and destroy all copies of this
> > message and any attachments contained in it.
>
>
>
>  time diff(1).xls
> 204KViewDownload- Hide quoted text -
>
> - Show quoted text -

--~--~-~--~~~---~--~~
Visit the blog to download Excel tutorials at 
http://www.excel-macros.blogspot.com

To post to this group, send email to excel-macros@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/excel-macros?hl=en

Visit & Join Our Orkut Community at 
http://www.orkut.com/Community.aspx?cmm=22913620

Visit the blog to download Excel tutorials at 
http://www.excel-macros.blogspot.com

To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com

To see the Daily Excel Tips, Go to:
http://exceldailytip.blogspot.com
-~--~~~~--~~--~--~---