Ask Anything on Excel, Word & Powerpoint

Hi Abhishek,

Please tell me about seting the numbers in excell in indian currency format i.e. ##,##,###.##

thanks.

alok

If U R Using WinXP & OfficeXP

Then change the Currency format under control panel reginal setting, For further detail mail me at azmath @ springfieldsschool.com

Dear Sir

Thanks a lot for your advice, but i knew this formatting , actually my question is if  I enter any date in a cell how can the excel shows "No. of Days'" falling in the mentioned year for example if  I write 25-02-2008 the answer is 29 days or if I write 25-02-2007 the answer is 28 days.

There is a formula, applying that we are able to know total no. of days falling in that year' s month.

I hope this will help you to understand my question.

Now pls advise.

Thanks

Rajesh

 DEAR RAJESH

Please don't call us Sir, we are all colleagues , so Abhishek will work great.

As for ur problem the solution is as follows :

=DAY(DATE(YEAR(D3),MONTH(D3)+1,1)-1)

D3 is the cell in which your date is.

The formulla is made up of three functions

1) I have used the DATE function DATE(YEAR(D3),MONTH(D3)+1,1)   

 I have added + 1 so that it will automatically take the 1st of next month . example  if D3 = 15-02-08 . the result will be 01-03-08. 

Then i have reduced one day from the result : DATE(YEAR(D3),MONTH(D3)+1,1)-1

This will make the result s follows  = 29-02-08.

Now the thing is simple , i just used the DAY function and took the Nos. of Days in your month  ie Day(29-02-08) will give me 29 - YOUR RESULT. 

regards

Abhishek 

In case our colleagues just need the formulla and no explanation/logic please feel free to say so. I just want everyone to understand the things in total and can suggest new and exciting tips & tricks. 

 dear nagaraju 

If u can just find a part of the name .

it is possible by using Vlookup only . 

U see when u use the function there are three options : 

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Range_lookup will help u 

• If range_lookup is TRUE, the values in the first column of table_array must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise VLOOKUP may not give the correct value.

  If range_lookup is FALSE, table_array does not need to be sorted and it willgive a result only if it finds an exact match .

• You can put the values in ascending order by choosing the Sort command from the Data menu and selecting Ascending.

  Please note using TRUE  is a very risky as it may give u unexpected results.

  Regards

   

  Abhishek 

Can u tell me how can we merge cells and is painting is possible in excel.

 Apurva 

U can merge cells through using the merge icon in excel . or by 

FORMAT -> CELLS -> ALIGNMENT -> MERGE CELLS,

I personally avoid using merge cells . In case u r merging Column there is a better way to do.

FORMAT -> CELLS -> ALIGNMENT -> HORIZONTAL-> CENTRE ACROSS SELECTION,

 This process will show the data in the centre of the range u have selected through above.

YES WE CAN PAINT IN EXCEL - INFACT I USED TO HAVE A ADDIN THOUGH WHICH U CAN DRAW A PICTURE IN EXCEL (Although not very good but still u can).

regards

Abhishek

(duplicate post...deleted)

edited on 30/05/2008

thanks for this thread abhishek ji.

Can u pls teach me how to use the "vlookup" formula ??

Coz, we export TB from our accounting software to excel and have a manually keyed in TB file. This will help us update our manual file with juz inserting or deleting.

 Bhuvana

Vlookup is a command thru which u can search a particular data from a list . The list should be in Row . (Account code will in anyway be in rows)   

The syntax of Vlookup is as follows :

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Lookup_value  The value to search in the first column of the table . Lookup_value can be a value or a reference.

These values can be text, numbers, or logical values.

Table_array  :  It is one or more columns of data. U can select the range to include data which u want to search and the data  which u want to return .

Note : The values in the first column of table_array are the values searched by lookup_value.  

Col_index_num : Column Index is the no. of columns (including the first column from which the value has to come

Example :   Column  A   B  C 

                                  Column A has Account code (This is what u have to search) Coumn B has Dr Balance & Column C has Credit balance 

So ur Vlookup will be 

= vlookup(Sheet1!A1,A:C,2,false)

If vlookup finds the value of Sheet1!A1 , in Column A , then it will put the value of B (2nd Column from A) as result.

The "false" is used so that there is an exact match of the value u r searching.

Note column Index No cant be negative &

Column index No cant be more than the no of columns u have selected  like in the above example it cant be more than 3 . 

Range_lookup  A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match:

Regards

Abhishek

In case u are not able to understand then i will send u an example file . bye

 Hi

Nagaraju 

I am not able to understand ur query . Is it u Group multiple sheets and then format the same ? or is it something else .

Regards

I work on no. of sheets simultaneously. Some times when i try to do any editing/ formatting, then message box appearing with info of  ”Too many different cell formats"

Please advise me

thanQ  abhishek ji...will try using the formula and will get back.

Dear Abhi,

Just Simple query,

Tell me what to do if we want  - (dash) on typing 0 (Zero)

Regards

Mehul

right click the mouse botton, select Format cell  ---> select Accounting

Thank you buddy,

its so easy......