tag:blogger.com,1999:blog-91271272024-03-12T21:27:48.183-04:00Tech Notes Exchange- MS ExcelEveryday we come across some problem for which we try, research for hours and once solved, looks so easy in MS Excel.I like to keep a note of it and share with others, so people can use them.These notes are not necessarily my own writing but are the one which I came accross during my research over the net to find out solutions.Udan Tashtarihttp://www.blogger.com/profile/06057252073193171933noreply@blogger.comBlogger65125tag:blogger.com,1999:blog-9127127.post-29460524921748676302015-09-15T13:57:00.000-04:002015-09-15T13:57:40.922-04:00Find Cells which have Strikethrough Text or numbers in MS Excel<div class="MsoNormal">
To Find Cells which have Strikethrough Text or numbers in MS
Excel:<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoListParagraphCxSpFirst" style="mso-list: l0 level1 lfo1; text-indent: -.25in;">
</div>
<ul>
<li><span style="text-indent: -0.25in;">-</span><span style="font-size: 7pt; font-stretch: normal; text-indent: -0.25in;">
</span><span style="text-indent: -0.25in;">Select a cell in the worksheet</span></li>
<li><span style="text-indent: -0.25in;">-</span><span style="font-size: 7pt; font-stretch: normal; text-indent: -0.25in;">
</span><span style="text-indent: -0.25in;">Go to the "Find and Select" button and
choose "Find"</span></li>
<li><span style="text-indent: -0.25in;">-</span><span style="font-size: 7pt; font-stretch: normal; text-indent: -0.25in;">
</span><span style="text-indent: -0.25in;">Click "Options", </span><span style="text-indent: -0.25in;"> </span><span style="text-indent: -0.25in;">and then click the "Format" button”.</span></li>
<li><span style="text-indent: -0.25in;">-</span><span style="font-size: 7pt; font-stretch: normal; text-indent: -0.25in;">
</span><span style="text-indent: -0.25in;">Click the "Font" tab.</span></li>
<li><span style="text-indent: -0.25in;">-</span><span style="font-size: 7pt; font-stretch: normal; text-indent: -0.25in;">
</span><span style="text-indent: -0.25in;">Select the box Strikethrough" to place a
check in it, and then click "OK."</span></li>
<li><span style="text-indent: -0.25in;">-</span><span style="font-size: 7pt; font-stretch: normal; text-indent: -0.25in;">
</span><span style="text-indent: -0.25in;">Click "Find All"</span></li>
<li><span style="text-indent: -0.25in;">-</span><span style="font-size: 7pt; font-stretch: normal; text-indent: -0.25in;">
</span><span style="text-indent: -0.25in;">A list of every cell that has strikethrough text
or number in it will show up</span></li>
<li><span style="text-indent: -0.25in;">-</span><span style="font-size: 7pt; font-stretch: normal; text-indent: -0.25in;">
</span><span style="text-indent: -0.25in;">Click on the name of a cell in the list to go to
particular cell.</span></li>
</ul>
<!--[if !supportLists]--><o:p></o:p><br />
<div class="MsoListParagraphCxSpMiddle" style="mso-list: l0 level1 lfo1; text-indent: -.25in;">
<o:p></o:p></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-list: l0 level1 lfo1; text-indent: -.25in;">
<o:p></o:p></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-list: l0 level1 lfo1; text-indent: -.25in;">
<o:p></o:p></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-list: l0 level1 lfo1; text-indent: -.25in;">
<o:p></o:p></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-list: l0 level1 lfo1; text-indent: -.25in;">
<o:p></o:p></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-list: l0 level1 lfo1; text-indent: -.25in;">
<o:p></o:p></div>
<br />
<div class="MsoListParagraphCxSpLast" style="mso-list: l0 level1 lfo1; text-indent: -.25in;">
<o:p></o:p></div>
Udan Tashtarihttp://www.blogger.com/profile/06057252073193171933noreply@blogger.com3tag:blogger.com,1999:blog-9127127.post-89381434521254724582015-08-24T12:07:00.000-04:002015-08-24T12:07:21.309-04:00The INDIRECT function in MS Excel<div class="MsoNormal">
The INDIRECT function is very useful in case you have
derived or calculated in a cell the cell reference from where you want the data
from.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
For Example, if cell A3 has a value C5 (May Be calculated
based on some formula or logic) and you want to refer C5 from A3 to get the
value of cell C5:<o:p></o:p></div>
<br />
<div class="MsoNormal">
=Indirect(A3)<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEidMCPMpNoPma_1kr1AQfT0zI9mX0NH_5mKEi2WQhuFWiI-UbRqD9zZ94C7wePxDcTpzOWl_cSPV4f-BRCMducZBlumbm50HQpBC1oyB4ondov4lzZmyn516-rgoZTguNZHOShi_g/s1600/Indirect.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="281" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEidMCPMpNoPma_1kr1AQfT0zI9mX0NH_5mKEi2WQhuFWiI-UbRqD9zZ94C7wePxDcTpzOWl_cSPV4f-BRCMducZBlumbm50HQpBC1oyB4ondov4lzZmyn516-rgoZTguNZHOShi_g/s320/Indirect.JPG" width="320" /></a></div>
<div class="MsoNormal">
<br /></div>
Udan Tashtarihttp://www.blogger.com/profile/06057252073193171933noreply@blogger.com1tag:blogger.com,1999:blog-9127127.post-49146948589291831752015-08-20T11:04:00.000-04:002015-08-20T11:04:12.673-04:00Remove Leading Spaces in MS Excel<div class="MsoNormal">
Many times =Trim(Text) and =Trim(Clean(Text)) does not work
in MS Excel to remove and clean leading spaces created due to non-breaking
space (Char(160)) specially if you are copying data from web or bullet list in
MS Word into an Excel Worksheet.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
This problem can be resolved by :<o:p></o:p></div>
<br />
<div class="MsoNormal">
=TRIM(CLEAN(SUBSTITUTE(Old Text,CHAR(160)," ")))<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh3Y8-dJBBuD5gjv5LkWRgzOBiES4ywuvTcm9aQJJEC7QyffNIbMekJISM3ANLq0BcYMtZYYHapr1nUwPlh4JJXqZefJVzaLBVkN8r29N0tcpHhHDxjQSB6xMm-POmxcmAZH5fHqg/s1600/leadingspace.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="260" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh3Y8-dJBBuD5gjv5LkWRgzOBiES4ywuvTcm9aQJJEC7QyffNIbMekJISM3ANLq0BcYMtZYYHapr1nUwPlh4JJXqZefJVzaLBVkN8r29N0tcpHhHDxjQSB6xMm-POmxcmAZH5fHqg/s320/leadingspace.JPG" width="320" /></a></div>
<div class="MsoNormal">
<br /></div>
Udan Tashtarihttp://www.blogger.com/profile/06057252073193171933noreply@blogger.com1tag:blogger.com,1999:blog-9127127.post-74983233555201954982015-06-25T10:11:00.004-04:002015-06-25T10:11:34.199-04:00Create Bullet in MS Excel<span style="background-color: white; color: #333333; font-family: 'Segoe UI regular', 'Segoe UI', Arial, Tahoma, sans-serif; font-size: 13px; line-height: 16px;">To create the bullet character, press ALT+0149 (Press Alt and type </span><span class="userInput" style="background-color: white; box-sizing: border-box; color: #333333; font-family: 'Segoe UI regular', 'Segoe UI', Arial, Tahoma, sans-serif; font-size: 13px; font-weight: bold; line-height: 16px; margin: 0px; padding: 0px;">0149</span><span style="background-color: white; color: #333333; font-family: 'Segoe UI regular', 'Segoe UI', Arial, Tahoma, sans-serif; font-size: 13px; line-height: 16px;"> on the numeric keypad and release Alt).</span><br />
<span style="background-color: white; color: #333333; font-family: 'Segoe UI regular', 'Segoe UI', Arial, Tahoma, sans-serif; font-size: 13px; line-height: 16px;"><br /></span>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgebuU0J_970mIusJLzHKLzRaT2G8SQkaT7xXzYrv9rAUE5ko05s26rDVScE3dYdGSmndBvJHmTIFg7RuZ53g0RvMWjcKJpPjZkQCqrSoJjF8fMpjsfwJ4OGV04ohqBB_LKWFGkyQ/s1600/excel_bullet.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="205" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgebuU0J_970mIusJLzHKLzRaT2G8SQkaT7xXzYrv9rAUE5ko05s26rDVScE3dYdGSmndBvJHmTIFg7RuZ53g0RvMWjcKJpPjZkQCqrSoJjF8fMpjsfwJ4OGV04ohqBB_LKWFGkyQ/s320/excel_bullet.JPG" width="320" /></a></div>
<span style="background-color: white; color: #333333; font-family: 'Segoe UI regular', 'Segoe UI', Arial, Tahoma, sans-serif; font-size: 13px; line-height: 16px;"><br /></span>Udan Tashtarihttp://www.blogger.com/profile/06057252073193171933noreply@blogger.com1tag:blogger.com,1999:blog-9127127.post-81525992040739239402014-02-28T13:59:00.002-05:002014-02-28T21:20:05.902-05:00MS Excel: Get preceding Zero of fixed width text field<div class="MsoNormal">
When you open csv file in MS Excel, for any text field as
number with preceding Zero, excel treats them as number and remove the
preceding Zero.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Let us say, that field is 5 characters long:<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
00542<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Excel will open it as (Assume in cell A1):<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
542<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
You can use the following formula to get back the Zero:<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
=Text(A1,”00000”)<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<br />
<div class="MsoNormal">
See the example below:<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiFumATVqAojQjKrpb1aBcDJZTkznqSEpuzPCR1Q-8MTUKEhHj5QnKudnagaQ0dPXXdpgMH3VeHdoedDkHgsAMv3wyTAkqkrtNrNm7YPVAk8YVbO13x-fop-eWnvXmy2Lkrn8cZlw/s1600/Text.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiFumATVqAojQjKrpb1aBcDJZTkznqSEpuzPCR1Q-8MTUKEhHj5QnKudnagaQ0dPXXdpgMH3VeHdoedDkHgsAMv3wyTAkqkrtNrNm7YPVAk8YVbO13x-fop-eWnvXmy2Lkrn8cZlw/s1600/Text.JPG" height="227" width="320" /></a></div>
<div class="MsoNormal">
<br /></div>
Udan Tashtarihttp://www.blogger.com/profile/06057252073193171933noreply@blogger.com2tag:blogger.com,1999:blog-9127127.post-71598550674910144672014-02-28T10:00:00.002-05:002014-02-28T10:00:26.017-05:00MS Excel: Lookup instead of Vlookup or Hlookup<div class="MsoNormal">
The <b>limitation of vlookup</b> is that you can only lookup in the
first column of array and bring back results from it or any column right of it. </div>
<div class="MsoNormal">
Similar<b> limitation in Hlookup</b> where you can only lookup in first row of array
and bring back result from it or any row below it.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
To get over with this limitation, use lookup instead of
vlookup and Hlookup:<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
=lookup(Lookup Value, Lookup Vector, Result Vector)<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
See the examples below:<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<b>Use of Lookup for Vlookup</b><o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgzgMZv5szJORDSMIF7LUcydYjmIjbjNyTfnG45LMYTrA7hWTlguupaUdfv1Xmzn5ibEDhvMqxj1KyqNFoyDNHGLuQXE86HSzK-4zIMKG95_TVc_0xsEOMgP2yVN4RCzp9APO7GEw/s1600/lookup.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgzgMZv5szJORDSMIF7LUcydYjmIjbjNyTfnG45LMYTrA7hWTlguupaUdfv1Xmzn5ibEDhvMqxj1KyqNFoyDNHGLuQXE86HSzK-4zIMKG95_TVc_0xsEOMgP2yVN4RCzp9APO7GEw/s1600/lookup.JPG" height="173" width="320" /></a></div>
<div class="MsoNormal">
<br /></div>
<br />
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<b>Use of lookup for Hlookup</b><o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhhQdduknZ-YN6hpS5m5YpMYFsAucKcdQ4GAqRxXd4LRl7M1sGxankHEzoABAA8CeSHHSD1jQOEzZloDXpiasD_ecGhmuNAQsw8t95T-XFed_8TUfj0VdcwszktPtiZ5GG4g_dGLQ/s1600/lookup_H.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhhQdduknZ-YN6hpS5m5YpMYFsAucKcdQ4GAqRxXd4LRl7M1sGxankHEzoABAA8CeSHHSD1jQOEzZloDXpiasD_ecGhmuNAQsw8t95T-XFed_8TUfj0VdcwszktPtiZ5GG4g_dGLQ/s1600/lookup_H.JPG" height="206" width="320" /></a></div>
<div class="MsoNormal">
<br /></div>
Udan Tashtarihttp://www.blogger.com/profile/06057252073193171933noreply@blogger.com0tag:blogger.com,1999:blog-9127127.post-29078234168866764412014-02-27T15:47:00.000-05:002014-02-27T15:47:05.816-05:00MS Excel : IfError function in Excel 2010<div class="MsoNormal">
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></div>
<div class="MsoNormal">
<span style="font-family: Arial, Helvetica, sans-serif;">Iferror is new function in MS Excel 2010 to make life
simple. It works as:<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></div>
<div class="MsoNormal">
<span style="font-family: Arial, Helvetica, sans-serif;"><b><span class="apple-converted-space"><span style="background-color: white; background-position: initial initial; background-repeat: initial initial; color: #444444;">= </span></span><em><span style="background-color: white; background-position: initial initial; background-repeat: initial initial; color: #444444; font-style: normal;">IFERROR</span></em></b><span style="background-color: white; background-position: initial initial; background-repeat: initial initial; color: #444444;"><b>(value,
value_if_error)</b><o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="background-color: white; background-position: initial initial; background-repeat: initial initial; color: #444444;"><span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></span></div>
<div class="MsoNormal">
<span style="background-color: white; background-position: initial initial; background-repeat: initial initial; color: #444444;"><span style="font-family: Arial, Helvetica, sans-serif;">See it's use with vlookup below:<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="background-color: white; background-position: initial initial; background-repeat: initial initial; color: #444444;"><span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></span></div>
<div class="MsoNormal">
<span style="background-color: white; background-position: initial initial; background-repeat: initial initial; color: #444444;"><span style="font-family: Arial, Helvetica, sans-serif;"><b>=Iferror(vlookup(A1,Sheet2!A1:C60, 3,False),”Not Found”)</b><o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="background-color: white; background-position: initial initial; background-repeat: initial initial; color: #444444;"><span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></span></div>
<br />
<div class="MsoNormal">
<span style="background-color: white; background-position: initial initial; background-repeat: initial initial; color: #444444;"><span style="font-family: Arial, Helvetica, sans-serif;">In this formula, if vlookup returns error because value is
not found, instead of returning #N/A, it will return “Not Found”.</span><span style="font-family: Arial, sans-serif;"><o:p></o:p></span></span></div>
Udan Tashtarihttp://www.blogger.com/profile/06057252073193171933noreply@blogger.comtag:blogger.com,1999:blog-9127127.post-27592774540513407752014-02-27T11:44:00.003-05:002014-02-27T15:11:48.818-05:00MS Excel: Show Alphabetic Row or Column Number<div class="MsoNormal">
To show row number in alphabetic order, simply right this
formula is first location where you wish to start the alphabet i.e. A and then,
drag and paste the formula in rows below:<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
=SUBSTITUTE ((ADDRESS (1, ROW (A1), 4)), 1,"")<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhHhyphenhyphenHKINZZpMh4xDllb8cu_VsrPfnkDum88ItFAj-vtLTQc5vNU4lnhA53FHaf0xkjMjbl5GtTOcxnedLX2ySRh8kmAe4mkhcpNmUWFSZN9ypS6OGc4dASP9sc02ef56unuZBKSw/s1600/Alphabetic_Row.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhHhyphenhyphenHKINZZpMh4xDllb8cu_VsrPfnkDum88ItFAj-vtLTQc5vNU4lnhA53FHaf0xkjMjbl5GtTOcxnedLX2ySRh8kmAe4mkhcpNmUWFSZN9ypS6OGc4dASP9sc02ef56unuZBKSw/s1600/Alphabetic_Row.JPG" height="253" width="320" /></a></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Same the case of alphabetical column numbers, at start point,
write the following formula and drag and paste on adjacent columns:<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<br />
<div class="MsoNormal">
=SUBSTITUTE ((ADDRESS (1, Column (A1), 4)), 1,"")<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjDZy3TLxLm_9sCI0IYAJoBVrIEctY6LXIg-MB-ZcCc15SuGD90B-Uel8hyphenhyphenbiSEbKmPFtyRig-aU3In9JGWsBvaRXwYErzfTZAwPyf5m_jbYC4Lh4QfPYgPkzWwrcbm3Le0WS5znw/s1600/Alphabetic_Column.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjDZy3TLxLm_9sCI0IYAJoBVrIEctY6LXIg-MB-ZcCc15SuGD90B-Uel8hyphenhyphenbiSEbKmPFtyRig-aU3In9JGWsBvaRXwYErzfTZAwPyf5m_jbYC4Lh4QfPYgPkzWwrcbm3Le0WS5znw/s1600/Alphabetic_Column.JPG" height="200" width="320" /></a></div>
<div class="MsoNormal">
<br /></div>
Udan Tashtarihttp://www.blogger.com/profile/06057252073193171933noreply@blogger.comtag:blogger.com,1999:blog-9127127.post-24275591013339713282011-11-21T19:24:00.002-05:002014-02-27T15:12:08.937-05:00MS Excel: DATEDIF function<a href="https://lh3.googleusercontent.com/-QhL7W3mYad0/TsrrcqgLdHI/AAAAAAAAJRY/HhzPkDNawkk/s434/datedif.jpg" onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}"><img alt="" border="0" src="https://lh3.googleusercontent.com/-QhL7W3mYad0/TsrrcqgLdHI/AAAAAAAAJRY/HhzPkDNawkk/s434/datedif.jpg" style="cursor: hand; cursor: pointer; display: block; height: 434px; margin: 0px auto 10px; text-align: center; width: 363px;" /></a><br />
<br />
The DATEDIF function can be used to calculate the number of days/moths/years between two dates in versions of Excel from 97 to 2007.<br />
<br />
=DATEDIF(B9,C9,"m")/12<br />
<br />
=DATEDIF(B9,TODAY(),"d")<br />
<br />
=DATEDIF(B9,C9,"m")<br />
<br />
It is interesting to note that DATEDIF is not listed with other functions under the formula tab in Excel 2007.Udan Tashtarihttp://www.blogger.com/profile/06057252073193171933noreply@blogger.comtag:blogger.com,1999:blog-9127127.post-73566464346756223102009-05-28T13:43:00.001-04:002009-05-28T13:46:09.433-04:00Folder and File Option : Missing Security Tab: Windows XP<span style="font-weight:bold;">Problem: Right clicking on folder or file not showing security tab:</span><br /><br /><br />Go to Explorer -> tools -> folder options -> View-> <br /><br />navigate to the last check box in the list “USE SIMPLE FILE SHARING” . <br /><br />Uncheck this checkbox and click OK. <br /><br />Now you will be able to see the security tab in file properties.Udan Tashtarihttp://www.blogger.com/profile/06057252073193171933noreply@blogger.com0tag:blogger.com,1999:blog-9127127.post-1176479866222634172007-04-13T11:57:00.000-04:002007-04-13T12:02:04.243-04:00Allow only numeric value in a textbox on a formRemove //** From the html codes:<br /><br />This is the function in the header tag <br /><br /> function AllowNumeric(e) { <br /> var iKeyCode = 0; <br /> if (window.event) <br /> iKeyCode = window.event.keyCode <br /> else if (e) <br /> iKeyCode = e.which; <br /> if (iKeyCode > 47 && iKeyCode < 58) <br /> return true <br /> else <br /> return false; <br /><br />Here is the HTML for the textbox<br /><br />//////(<)//**<br />INPUT onkeypress="javascript: return AllowNumeric(event);" id="txtAcctNo" style="WIDTH: 120px; HEIGHT: 20px"<br /> type="text" maxLength="8" runat="server" size="14"<br />//**>///////Udan Tashtarihttp://www.blogger.com/profile/06057252073193171933noreply@blogger.com0tag:blogger.com,1999:blog-9127127.post-1155748121361212202006-08-16T13:01:00.000-04:002007-01-18T12:47:41.526-05:00The VLOOKUP Function:Very useful and very simple<HEAD><br /><br /><SCRIPT LANGUAGE="JavaScript"><br />function person_in() {<br /> enter=new Date();<br />}<br />function person_out() {<br /> exit=new Date();<br /> time_dif=(exit.getTime()-enter.getTime())/1000;<br /> time_dif=Math.round(time_dif);<br /> alert ("You've only been here for: " + time_dif + " seconds!!")<br />}<br /></SCRIPT><br /></HEAD><br /><BODY bgcolor=ffffff onLoad='person_in()' onUnLoad='person_out()'><br /><br /><br />The most useful and least used in common practice due to lack of knowledge is the VLOOKUP function of MS Excel.<br />Here I am presenting with the help of one example the common use of VLOOKUP function.<br />Let us first see the function:<br />With the VLOOKUP function, ypu can look for specified data in the first column of a table. The result is returned from the column number specified, if found.The formula for VLOOKUP is:<br /><br /><strong>=vlookup(lookup_value,table_array,col_index_num,range_lookup)</strong><br /><br /><br /><a href="http://photos1.blogger.com/blogger/6147/651/1600/ExcelBlogVlookup.jpg"><img style="MARGIN: 0px 10px 10px 0px; CURSOR: hand" alt="" src="http://photos1.blogger.com/blogger/6147/651/320/ExcelBlogVlookup.jpg" border="0" /></a><br />Let us look at the example, I want to see if value in column A3 to A6 of table A exist in Table B.If yes, I want formula to return me column E's value.<br />The formula is:<br /><br /><strong>=+VLOOKUP(A3,$E$3:$F$6,1,FALSE)<br /></strong><br />Now, here I am looking for A3(lookup_value) of table A in E3 to F6(table_array), remember: it will look into left most column,so in E3 to E6,and return me value from column 1(col_index_num) which is E3 to E6.Then comes the range_lookup which is False.The Reason for it being False is,the use of False as the optional range_lookup Argument. This directs to the VLOOKUP to find an exact match and is most often needed when looking for a text match. If this is omitted, or True, you will often get unwanted results when searching for text that is in an unsorted column of data. When True is used, or the range_lookup Argument is ignored, the data should be sorted (by the first column) in ascending order.<br /><br />See the results in column A8 to A11.<br /><br />Now, if I want to return the value from column F of Table B, all I have to do is to change column index to 2.<br /><br /><strong>=+VLOOKUP(A3,$E$3:$F$6,<span style="color:#ff0000;">2</span>,FALSE)<br /></strong><br />See the results in column D8 to D11.<br /><br />Whenever, match is not found, the formula will retorn an error i.e. #N/A.To avoid this, i write another formula:<br /><br /><strong>=IF(ISERROR(+VLOOKUP(A3,$E$3:$F$6,1,FALSE))=TRUE,0,(+VLOOKUP(A3,$E$3:$F$6,1,FALSE)))</strong><br /><br />Meaning that if vlookup returns error i.e. #N/A, then return 0 else return vlookup value.<br />That is all to it.Hope you find it useful.<br /></BODY>Udan Tashtarihttp://www.blogger.com/profile/06057252073193171933noreply@blogger.com0tag:blogger.com,1999:blog-9127127.post-1130513651280957722005-10-28T11:28:00.000-04:002005-10-28T11:34:11.283-04:00Export Custom Outlook Form Data to MS AccessDim appAccess<br />Dim nms<br />Dim strFolder<br />Dim fld<br />Dim strAccessPath<br />Dim rst<br />Dim dbe<br />Dim wks<br />Dim dbs<br />Dim itms<br />Dim itm<br /><br />Sub CommandButton1_Click()<br /><br /> Set nms = Application.GetNamespace("MAPI") <br /> strFolder = "fbtest folder" <br />Set fld = nms.Folders("Personal Folders").Folders(strFolder)<br /><br />'Pick up path to Access database directory from Access SysCmd function <br />Set appAccess = CreateObject("Access.Application") <br />strAccessPath = appAccess.SysCmd(9) <br /><br />'Get DAO version from DBEngine <br />strDBEngine = appAccess.Application.DBEngine.Version <br /><br />'MsgBox "DBEngine version: " & strDBEngine <br /><br />appAccess.Quit<br /><br />If strDBEngine = "3.51" Then <br />'Office 97 DAO version <br />Set dbe = CreateObject("DAO.DBEngine.35") <br />strDBName = strAccessPath & "fbtestdb.mdb" <br />ElseIf strDBEngine = "3.6" Then <br />'Office 2000 DAO version <br />Set dbe = CreateObject("DAO.DBEngine.36") <br />strDBName = strAccessPath & "fbtestdb.mdb" <br /><br />Else <br /><br />MsgBox "Unknown Office version; canceling" <br /><br />Exit Sub <br /><br />End If <br /> 'MsgBox "DBName: " & strDBName <br />Set wks = dbe.Workspaces(0) <br />Set dbs = wks.OpenDatabase("S:\sameer\fbtestdb.mdb") <br /><br /> 'Open Access table containing contact data <br />Set rst = dbs.OpenRecordset("fbtesttable")<br /> 'Set up reference to Outlook folder of items to export <br /><br />Set itms = fld.Items <br />ItemCount = itms.Count <br />If ItemCount = 0 Then <br />MsgBox "No FB requests to export" <br /><br />Exit Sub <br />Else <br /><br />MsgBox ItemCount & " FB requests to export" <br /><br />End If<br /> 'Set up reference to Outlook folder of items to export <br />Set itms = fld.Items <br />ItemCount = itms.Count <br /><br />If ItemCount = 0 Then<br /> <br />MsgBox "No Fund Builder requests to export" <br /><br />Exit Sub <br />Else <br /><br />MsgBox ItemCount & " FB requests to export" <br /><br />End If<br /> For Each itm In itms <br /><br /> rst.AddNew <br /><br /> 'Custom Outlook properties<br />rst.Request=itm.userproperties("001 Request")<br />rst.AccountNumber=itm.userproperties("002 Account Number") rst.ClientName=itm.userproperties("002 Client Name")<br /> <br />rst.Update <br /><br />Next<br /> <br />rst.Close <br /><br />MsgBox "All FB requests exported!" <br /><br />End SubUdan Tashtarihttp://www.blogger.com/profile/06057252073193171933noreply@blogger.com0tag:blogger.com,1999:blog-9127127.post-1130512623189160302005-10-28T11:16:00.000-04:002005-10-28T11:22:33.946-04:00Export Data From Outlook Custom Form to ExcelSub CommandButton1_Click()<br />ExportToExcel()<br />End Sub<br /><br />Sub ExportToExcel()<br /><br />Dim appExcel<br />Dim olMAPI<br />Dim strTemplatePath<br />Dim strSheet<br />Dim Ifld<br />Dim MItem<br /><br />Set olMAPI = GetObject("", "Outlook.Application").GetNameSpace("MAPI")<br />Set Ifld = olMAPI.Folders("Personal Folders").Folders("SamTest")<br /><br />i=1<br /><br />'Pick up Template path from the word options dialog<br />strTemplatePath="H:\"<br /><br />'Debug.Print "Document folder: " & strTemplatePath<br /><br />strSheet="SameerTest.xls"<br />strSheet=strTemplatePath & strSheet<br /><br />'Set appExcel= New Excel.Application<br />Set appExcel=GetObject("", "Excel.Application")<br /><br />appExcel.Workbooks.Open (strSheet)<br />Set wkb = appExcel.ActiveWorkBook<br />Set wks =wkb.Sheets(1)<br />wks.Activate<br />wks.Cells(1, 1)="Subject"<br />wks.Cells(1,2)="ClientName"<br />wks.Cells(1,3)="ClientAddress"<br />wks.Cells(1,4)="ClientAge"<br /><br />appExcel.Application.Visible=TrueFor Each MItem In Ifld.Items<br /><br />If Left(MItem.Subject, 12) = "Client Form" Then<br />i = i + 1<br /><br />If MItem.Subject<>"" Then<br />wks.Cells(i,1).Value = MItem.Subject<br />End If<br /><br />If MItem.UserProperties("010 ClientName").Value<>"" Then<br />wks.Cells(i,2).Value = MItem.UserProperties("010 ClientName").Value<br />End If<br /><br />If MItem.UserProperties("020 ClientFirstName").Value<>"" Then<br />wks.Cells(i,3).Value = MItem.UserProperties("020 ClientAddress").Value<br />End If<br /><br />If MItem.UserProperties("030 ClientInitial").Value<>"" Then<br />wks.Cells(i,4).Value = MItem.UserProperties("030 ClientAge").Value<br />End If End If<br /><br />Next<br /><br />Set MItem = Nothing<br />Set Ifld = Nothing<br />Set strTemplatePath = Nothing<br />Set strSheet = Nothing<br />Set olMAPI = Nothing<br />Set appExcel = Nothing<br /><br />End SubUdan Tashtarihttp://www.blogger.com/profile/06057252073193171933noreply@blogger.com0tag:blogger.com,1999:blog-9127127.post-1126100697518177882005-09-07T09:42:00.000-04:002005-09-07T09:44:57.523-04:00Counting Commas or Special characters in a cell<span style="font-size:85%;">Suppose, your names seperated by "," is in cell A4.<br /><br />Here is the formula to calculate number of commas in the cell A4:<br /><br /><strong><span style="color:#3366ff;">=LEN(A4)-LEN(SUBSTITUTE(A4,",",""))</span></strong><br /><br /><br />The first part: LEN(A4) calculates length of string with commas. </span><br /><span style="font-size:85%;"><br />The Second part: LEN(SUBSTITUTE(A4,",","")) calculates length of string without commas.<br /><br />The differnece of the two i.e. first part-second part will give you number of commas in your string.<br /><br />This formula can come out handy in many calculations.</span><br /></span>Udan Tashtarihttp://www.blogger.com/profile/06057252073193171933noreply@blogger.com0tag:blogger.com,1999:blog-9127127.post-1124721960896508262005-08-22T10:44:00.000-04:002005-08-22T10:46:00.906-04:00VB 6.0-Some Formatting ExpressionI find it very handy:<br /><br />Formatting numbers with named formats Expression Result <br />Format(35988.3708, "general number") 35988.3708 <br />Format(35988.3708, "currency") $35,988.37 <br />Format(-35988.3708, "currency") ($35,988.37) <br />Format(35988.3708, "fixed") 35988.37 <br />Format(1, "fixed") 1.00 <br />Format(35988.3708, "standard") 35,988.37 <br />Format(1, "standard") 1.00 <br />Format(0.35988, "percent") 35.99% <br />Format(0, "Yes/No") No <br />Format(0.35988, "Yes/No") Yes <br />Format(0, "True/False") False <br />Format(342, "True/False") True <br />Format(0, "On/Off") Off <br />Format(-1, "On/Off") On <br /><br /><br />Formatting numbers with special characters Expression Result <br />Format(35988.3708, "00000.0") 35988.4 <br />Format(35988.3708, "0000000.0") 0035988.4 <br />Format(35988.3708, "00,000.00000") 35,988.37080) <br />Format(6.07, "0.###") 6.07 <br />Format(6.07, "0.000##") 6.070 <br />Format(143879, "#,###,###.00") 143,879.00 <br /><br />Formatting numbers with embedded characters Expression Result <br />Format(45, "\[00\]") [45] <br />Format(642, "\£000.00") £642.00 <br />Format(99, "00\¢") 99¢ <br />Format(8, "#0\).") 8). <br /><br />Formatting dates and time<br /><br />Format(36715.5784, "general date") 7/8/00 1:52:54 PM <br />Format(36715.5784, "short date") 7/8/00 <br />Format(36715.5784, "medium date") 08-Jul-00 <br />Format(36715.5784, "long date") Saturday, July 08, 2000 <br />Format(36715.5784, "short time") 13:52 <br />Format(36715.5784, "medium time") 01:52 PM <br />Format(36715.5784, "long time") 1:52:54 PM <br />Format(36715.5784, "c") 7/8/00 1:52:54 PM <br />Format(36715.5784, "d") 8 <br />Format(36715.5784, "dd") 08 <br />Format(36715.5784, "ddd") Sat <br />Format(36715.5784, "dddd") Saturday <br />Format(36715.5784, "ddddd") 7/8/00 <br />Format(36715.5784, "dddddd") Saturday, July 08, 2000 <br />Format(36715.5784, "w") 7 <br />Format(36715.5784, "ww") 28 <br />Format(36715.5784, "m") 7 <br />Format(36715.5784, "mm") 07 <br />Format(36715.5784, "mmm") Jul <br />Format(36715.5784, "mmmm") July <br />Format(36715.5784, "q") 3 <br />Format(36715.5784, "y") 190 <br />Format(36715.5784, "yy") 00 <br />Format(36715.5784, "yyyy") 2000 <br />Format(36715.5784, "h") 13 <br />Format(36715.5784, "hh") 13 <br />Format(36715.5784, "n") 52 <br />Format(36715.5784, "nn") 52 <br />Format(36715.5784, "s") 54 <br />Format(36715.5784, "ss") 54 <br />Format(36715.5784, "ttttt") 1:52:54 PM <br />Format(36715.5784, "AM/PM") PM <br />Format(36715.5784, "am/pm") pm <br />Format(36715.5784, "A/P") P <br />Format(36715.5784, "a/p") p <br />Format(36715.5784, "AMPM") PM <br /><br />Format "w" returns day of week (1 = Sunday, 7 = Saturday) <br />Format "ww" returns week of year (1-53) <br />Format "y" returns day of year (1-366) <br />Format "h" returns hour of day as one or two digits...if necessary <br />Format "hh" returns hour of day as two digits...definitely <br />Above applies to "n"/"nn", and "s"/"ss" as well <br />Format "AMPM" uses settings from WIN.INI [intl] s1159=AM, s2359=PM <br />Try mixing and matching the format strings Expression Result <br />Format(36715.5784, "m-d-yy") 7-8-00 <br />Format(36715.5784, "d-mmmm-y") 8-July-00 <br />Format(36715.5784, "mmmm yyyy") July 2000 <br />Format(36715.5784, "hh:mm a/p") 01:52 p <br />Format(36715.5784, "m/d/yy h:mm") 7/8/00 13:52 <br /><br />FormatDateTime<br />This new function works about the same as the regular Format function, but you're only allowed to <br />use one of 5 constants - <br />vbGeneralDate, vbLongDate, vbShortDate, vbLongTime, vbShortTime.Udan Tashtarihttp://www.blogger.com/profile/06057252073193171933noreply@blogger.com0tag:blogger.com,1999:blog-9127127.post-1113743577234682622005-04-17T09:10:00.000-04:002005-04-17T09:12:57.236-04:00MS Excel: Working in multi-sheet environmentWhen we are referencing one sheet data to another sheet in a formula or otherwise comparing multi sheets in a work book, it becomes tedious to move from sheet to sheet back and forth. Think of a situation where you have to refer data from three or four sheet.<br /><br />Wouldn't it be nice, if you can have all those three sheets of workbook open side by side?<br />Did you know that there is an easy way to do this and I always find it very handy while working on a multi-sheet workbook?<br /><br />Open your workbook, sheet1.<br /><br />Go to Window tab,<br />Click on New Window,<br />Select sheet 2 on this newly opened workbook where you will see on the top with the name: 2.<br /><br /><img src="http://www.geocities.com/sameerlal_2/picblog/multisheet/wmsheet1.jpg" /><br /><br /><br />Again repeat the same steps and open sheet 3. Here with the name, you will see: 3.<br /><br />Now, that you have the same workbook opened thrice with different sheets,<br /><br />Go to window,<br />Click arrange,<br />Select Vertical<br /><br />and here you go, three sheet of same workbook opened side by side, toggle around and enjoy working on multi-sheet workbook.<br /><br /><img src="http://www.geocities.com/sameerlal_2/picblog/multisheet/wmsheet2.jpg" />Udan Tashtarihttp://www.blogger.com/profile/06057252073193171933noreply@blogger.com0tag:blogger.com,1999:blog-9127127.post-1112291576181288862005-03-31T04:46:00.000-05:002005-03-31T12:52:56.183-05:00MS Excel: Close all Open WorkbooksAt times, when you have many workbook open on your desktop and wish to close all of them together,there is an easy way in MS Excel to do this. I could never understand the reason why this option is not on main menu and is kept hidden.<br />Follow this:<br /><br /><span style="color:#3333ff;">Press Shift key on the keyboard while clicking File on the menu;</span><br /><br /><span style="color:#3333ff;">And now you will see the option: Close All</span><br /><br /><span style="color:#3333ff;">Click on it and all the workbooks are closed</span> unless, in some workbook(s), you have made changes and not saved them.Excel will prompt you to save changes and close the workbooks.Udan Tashtarihttp://www.blogger.com/profile/06057252073193171933noreply@blogger.com0tag:blogger.com,1999:blog-9127127.post-1112235846953646192005-03-30T21:22:00.000-05:002005-03-30T22:24:10.326-05:00MS Excel: Not only sort data in a column but also in the rowsClick a cell of the row you want to sort horizontally.<br /><br />Select Sort from the Data menu.<br /><br />In the Sort dialog box, click on the Options.<br /><br />In the Sort Options dialog box, click on Sort Left to Right, then click OK.<br /><br />Back in the Sort dialog box, go to the Sort By combo box and select a row to sort, and also select the direction (ascending or descending).<br /><br />You can select additional rows by going to the boxes.<br /><br />Click OK.<br />Yu Hooo!!! Excel sorts the row horizontally.Udan Tashtarihttp://www.blogger.com/profile/06057252073193171933noreply@blogger.com0tag:blogger.com,1999:blog-9127127.post-1112128604700681832005-03-29T15:34:00.000-05:002005-04-17T09:09:49.486-04:00MS Excel: Get Summary Calculations on the FlyAs a busy executive, you are presented with lots of data in MS Excel format. Though you know the formulas to calculate and find results, but it is just a waste of time to write formula to see what the sum of particular range is or which is the highest value or lowest value or may be the average.<br />While looking at the spreadsheet, you can do the following to improve your analysis capabilities or effectively respond to one off questions which keeps coming up during meetings:<br /><br /><br />1. From the menu option, select View, and check the status bar.<br /><br /><br /><img src="http://www.geocities.com/sameerlal_2/picblog/analyzeonfly/statuspic1.jpg" /><br /><br /><br />2. This action will start showing you status bar at the bottom of spreadsheet.<br /><br />3. Select the range of data you wish to analyze, and you will see on the status bar, Sum= 100 or whatever is the sum of the range selected (By default, excel is set to show Sum).<br /><br />4. Right click on status bar, and you will see options like Average, Count, Count Nums, Max, Min and Sum. Select any one and see the result on status bar.<br /><br /><br /><img src="http://www.geocities.com/sameerlal_2/picblog/analyzeonfly/statuspic2.jpg" />Udan Tashtarihttp://www.blogger.com/profile/06057252073193171933noreply@blogger.com0tag:blogger.com,1999:blog-9127127.post-1112027627914672582005-03-28T11:29:00.001-05:002005-03-29T15:34:16.713-05:00MS Excel: Sum of a column with #DIV/0! Or any other error message in some cells<p>My friend recently requested me to suggest some quick fix for sum of a column which has few cells containing #DIV/0! Or any other error message. Doing the sum function produces error because sum neither ignores those cells nor understands it.<br /><br />My process to deal with this problem is:<br /><br />1.Insert a column next to the column you are trying to sum up.<br />2.In my example, the first cell is A3, I inserted column B.<br />3.In Cell B3, I wrote the formula “=IF (ISERROR (A3), 0,A3)”<br />4.This formula looks for the cell value and if it is an error, converts it to 0.<br />5.Then I copied this formula in B4 to B6 since my values were in up to cell B6, you could copy it till last cell value.<br />6.Select Cells B3 to B6, copy and paste special-values only.<br />7.Now you can sum this column and you are done.<br /><br />Example:<br />Formulas:<br /><br /><img src="http://www.geocities.com/sameerlal_2/picblog/sumerror/exerror1.jpg" /><br /><br />Values:<br /><br /><img src="http://www.geocities.com/sameerlal_2/picblog/sumerror/exerror1.jpg" /></p>Udan Tashtarihttp://www.blogger.com/profile/06057252073193171933noreply@blogger.com0tag:blogger.com,1999:blog-9127127.post-1111855476008689222005-03-26T11:38:00.000-05:002005-03-26T11:47:49.700-05:00MS Excel and VBA: Transposing Data<span style="color:#3333ff;"></span><br /><span style="color:#3333ff;">This tip is submitted by Anupam Srivastava:</span><br /><br /><span style="color:#003300;"><em><strong>Transposing Data from Column to Row with a feature to start a New Row for each New Record which is identified by an empty Row:<br /></strong></em></span><br />The original dataset was stored in the same column followed by an empty row before each new group entry. The task was to bring each dataset into row format to make data analysis job easier. This is particularly useful for comparing information.<br />This Macro scans through the column, copies each item and paste it into different columns in the same row and begins a new row for each empty row it finds. The datasets are differentiated by empty row before new dataset begins.<br />To maintain consistency with the generated table, it is wise to make sure that each new dataset has the same number of data entities (items).<br /><br />For users with knowledge of DB, if we consider each group to be an attribute with x number of entities, then each new attribute that you add to the original data should consist x entities to make sure that the final table gets populated in a uniform fashion.<br /><br />For this Macro it is assumed that each dataset consists of 7 entities<br /><br /><br /><span style="color:#3333ff;">Sub DataTranspose()<br /><br /><span style="color:#009900;">‘ Declare variable to denote column and row assignment</span><br /></span><br /><span style="color:#009900;">' Row Variable</span><br /><span style="color:#3333ff;">Dim i As Integer </span><br /><span style="color:#3333ff;"><span style="color:#009900;">' Column Variable</span><br />Dim j As Integer<br />i = 2<br />j = 1<br /><br /><br /><span style="color:#009900;">' Sheet has 218 rows of data</span><br /><br />For i = 1 To 218<br />Range("A" & i).Select<br />Selection.Copy<br />Range("a" & j).Select<br />ActiveSheet.Paste<br /><br /><span style="color:#009900;">' Increment Row Counter to read next row</span><br />i = i + 1<br />Range("A" & i).Select<br />Selection.Copy<br /><br /><span style="color:#009900;">' Transposing Row Entry to Column</span><br />Range("B" & j).Select<br />ActiveSheet.Paste<br />i = i + 1<br />Range("A" & i).Select<br />Selection.Copy<br />Range("C" & j).Select<br />ActiveSheet.Paste<br />i = i + 1<br />Range("A" & i).Select<br />Selection.Copy<br />Range("D" & j).Select<br />ActiveSheet.Paste<br />i = i + 1<br />Range("A" & i).Select<br />Selection.Copy<br />Range("E" & j).Select<br />ActiveSheet.Paste<br />i = i + 1<br />Range("A" & i).Select<br />Selection.Copy<br />Range("F" & j).Select<br />ActiveSheet.Paste<br />i = i + 1<br />Range("A" & i).Select<br />Selection.Copy<br />Range("G" & j).Select<br />ActiveSheet.Paste<br />i = i + 1<br />Range("A" & i).Select<br />Selection.Copy<br />Range("H" & j).Select<br />ActiveSheet.Paste<br />i = i + 1<br />Range("A" & i).Select<br />Selection.Copy<br />Range("I" & j).Select<br />ActiveSheet.Paste<br />i = i + 1<br />j = j + 1<br />Next<br /><br />End Sub<br /></span><br /><br />----------------------------------------------------------------------------------<br /><em><span style="color:#000099;">Anupam Srivastava is B.Sc. (Computer Engineering) from Queen’s University, Kingston, Canada.</span></em>Udan Tashtarihttp://www.blogger.com/profile/06057252073193171933noreply@blogger.com0tag:blogger.com,1999:blog-9127127.post-1111767565161953862005-03-25T11:14:00.000-05:002005-03-28T11:27:10.776-05:00MS Excel: Always show full menuTurn off the frequently used menu option and always use full menu option during your learning process of MS Excel and remember, learning is a continuous process and you are always learning. So make the full menu option as a regular option for you.<br />MS Excel only shows you frequently used menu under each tab and then, if you wish to use something other than shown in the menu, you have to go to that expand sign to see full menu:<br /><br /><img src="http://www.geocities.com/sameerlal_2/picblog/menuitem/excpic1.jpg" /><br /><br /><br />This is frustrating, especially when you are learning. How are you suppose to know, what is hidden under that magic expand sign. I always prefer to keep full menu on.<br />To do this, it is a very simple two step process:<br /><br />Step 1: Go to Tools, find out Customize and click:<br /><br /><br /><img src="http://www.geocities.com/sameerlal_2/picblog/menuitem/excpic2.jpg" /><br /><br /><br />Now, you will see something like this:<br /><br /><br /><img src="http://www.geocities.com/sameerlal_2/picblog/menuitem/excpic3.jpg" /><br /><br /><br />Step 2: Click on Options tab and select, always show full menus, close and you are done.<br /><br />Now, every time you click on a menu, you will see full menu. Remember, by doing this on MS Excel, your all other MS Office programs like MS Words etc. will also show full menu items and it is not possible to avoid this. But who cares, it is always good to keep full menu in view irrespective of program you are using.Udan Tashtarihttp://www.blogger.com/profile/06057252073193171933noreply@blogger.com0tag:blogger.com,1999:blog-9127127.post-1111412750708782652005-03-24T08:45:00.000-05:002005-03-25T11:20:49.843-05:00MS Excel : The Leading ZerosMS Excel keeps removing that leading zero from your number?<br />Excel automatically sees the data as a number and leading zeros have no value in numbers, so it gets rid of them.<br />It is helpful when you're actually dealing with numbers, but irritating when it's a Item number or Employee number.<br />There is a quick way to get Excel to leave your zeros alone. Justchange the cell format to text. There are couple of ways to make this change—<br />1. Highlight the cells to change.<br />Then go to the Format menu, Cells choice. (Ctrl + 1 works too.)<br />On the General tab choose Text from the Categories list.<br />Click OK.<br />The leading zeros are there.<br />2.If you only have a few cells to change you could just type an apostrophe before entering the data (ie. instead of typing 01122 in the cell type '011122).<br />When you hit the Enter key the change is made instantly, for that cell only, and again your zero is there.Udan Tashtarihttp://www.blogger.com/profile/06057252073193171933noreply@blogger.com0tag:blogger.com,1999:blog-9127127.post-1111412051201062762005-03-21T08:32:00.000-05:002005-03-21T08:34:11.203-05:00Outlook SaveAs Type Constants<strong>Constant Value</strong><br />olTXT 0<br />olRTF 1<br />olTemplate 2<br />olMSG 3<br />olDoc 4<br />olHTML 5<br />olVCard 6<br />olVCal 7Udan Tashtarihttp://www.blogger.com/profile/06057252073193171933noreply@blogger.com0