Considering his work for the past few months.. could we recommend him
for next MVP
On Mar 15, 3:49 am, Ayush Jain jainayus...@gmail.com 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
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
vba code for this.
Thanks Regards,
Vikash Chandra
Bangalore
9902622922
On Sun, Dec 25, 2011 at 6:20 AM, anandydr anand...@gmail.com 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
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,
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
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
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
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
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
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
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.
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
. 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
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 :
A minor modification to your current formula can do that. Instead of
using your current formula you should use the following
=IF(AND(A2C2,B2D2),Yes,IF(AND(TODAY()C2,NOW()D2),Yes,No))
Hope that helps
Ragards,
Anand Kumar
On May 27, 6:40 pm, Chandra Shekar chandrashekarb@gmail.com
wrote:
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
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) setiyowati.d...@gmail.com wrote:
sometimes what we thought as the space (as prefix or suffix) turns out to be
*Char (160)*so we need
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 jainayus...@gmail.com wrote:
Dear group,
As the part of
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
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
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 ( )
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
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
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 nagapavan@gmail.com wrote:
Hi,
I have
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,
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
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
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
, 2010 at 8:30 AM, anandydr anand...@gmail.com 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
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 bhavya...@gmail.com wrote:
Hi All,
Can
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,
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
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 vaaibhavjhav...@gmail.com wrote:
Hello saggi
I think you must have given this reply considering that I have
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 ashishj...@openexcel.com wrote:
Dear Members,
We're
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 sajidmansooral...@gmail.com
wrote:
Good!
On Sun, Dec 13, 2009 at 6:01 PM, Dilip Pandey
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 suicid...@gmail.com wrote:
Hi, just joined the group to take a look at what I could learn. I'll
be posting any questions I have shortly.
--
:35 PM, anandydr anand...@gmail.com 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
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 sushilck.sha...@gmail.com wrote:
you can provide
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
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 dilipan...@gmail.com wrote:
Hi Renuka,
Try using following formula.
=TEXT(DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)),mm/dd/)
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 morur...@gmail.com 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
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 bramfiel...@googlemail.com wrote:
Hi group.
I'm by no means a programmer. I'm trying to knock together a (what
should be)
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.
Congrats Ayush,
You quite well deserved...
Warm regards,
Anand Kumar.
On Oct 4, 9:43 am, Jai jaihumtu...@gmail.com wrote:
U deserve this Friend.
On Mon, Oct 4, 2010 at 9:35 AM, C.G.Kumar kumar.bemlmum...@gmail.comwrote:
Congratulation friend on reaching a milestone. Hope may more to
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
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.
-- Anand
On Sep 26, 9:59 pm, anandydr anand...@gmail.com 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 reach...@gmail.com wrote:
Excel chart
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
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 reach...@gmail.com wrote:
Excel chart plotting, depending on weightage
Hi experts,
Need help in ideas and
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
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
@ 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???
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
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
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 excelkeec...@gmail.com wrote:
Yeah
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 rfhyd1...@gmail.com wrote:
Dear Nani
Check This Link
Outhttp://www.angelfire.com/biz7/julian_s/julian/julians_macros.htm
=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
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+ (A1B1) - A1
Since on subtracting dates Excel
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
Put your code on Worksheet_onEnter event hope that would be able to
solve your problem.
On May 8, 9:14 am, Akhilesh Karna akhiles...@gmail.com 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
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 aind...@gmail.com wrote:
Hi Jayesh,
Could you please elaborate your exact problem please
61 matches
Mail list logo
