There was an error in this gadget

Tuesday, September 6, 2011

Excel problems for excel gurus

I have to work lot on excel and as a result try to shortcut lot of things but here are some problems which i encountered but couldn't find the best solution.


1. How to get all tab names or sheet names of my worksheet in one sheet of same workbook.

i got lot of

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,
LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))


but it just provide name of currently active sheet not other sheets.

Other options are vba requiring macro

but i dont want to create macro i think excel ppl shoudl create a command to get all the excel sheets names .


2. You want to copy filtered data but its copying entire stuff? worse it is giving error: " not able to execute command on merge cells ..something like that?

well dont worry solution to your problem is very simple.
Excel options->customize->in drop down "choose commands from " select "Commands not in ribbon" ->scroll down and add "select visible cells" command . add it to ur top bar

select visible cells looks like 4 black squares.

now when ur filter is set select the part u want click on this button it will select only visible cells and it will be copied easily.

if you face problem copying merged cells..just highlight them and click on merge and centre button they will be unmerged without losing ur data as same data will be copied on both rows.

then u can continue copying.

happy excel -ing :D



3. in excel how to get number of working days in a month.
simple
=NETWORKDAYS(start date,end date, [holidays])
for e.g.
where A4 = 2/1/2012
A38= 2/29/2012
for feb month
in any cell put following formula and press enter. ull get 21
i.e. there 21 working days in feb.

=NETWORKDAYS(A4,A38)


4. in excel how to get the day from a date.

e..g in cell A1 we have date 2/1/2012
and u want the day on this date to be displayed in A2
then simply write =A1 in cell A2
then change format of A2 cell to custom->dddd.

i.e. it will display wednesday.


5. One of the most beneficial formulas in store of Excel. Vlookup.

Imagine you have some selected data of 24 ids and you want to get data corresponding to some numbers from the table having huge data? will you manually  do ctrl + f? I dont think so...
Thats where Vlookup helps.

Only precondition.. what you have to ssearch via should be a unique number and those unique numbers should be in the first column of the table from which you want to get data.. example.

your data:
ram123
king456
sammy876

you want to search from the data base below:
User Id      User first name       user last name    user age

ram123         ram                        singh                   28
kan908         kanha                     verma                 30
rem976        Rema                       Gupta                29
king456        Saniya                      Shukla              78
sammy876   Samrat                      Dev                  87


and so on..... till 100 records.

so lets say you want age of ids in "your data" then you write formula in next cell
ram123    = Vlookup(A1,Sheet1!A1:D6,4,False)

here A1 is the cell where ram123 is written
Sheet1 is the tab where your database is present

Sheet1!A1:D6  ->A1:D6 is the area of table which you want to refer...you can select the whole sheet too

4 is the column number of the "user age" the value which you want to get

False-> it means you want exact match.


                ram123    = Vlookup(A1,Sheet1!A1:D6,4,False)
RESULT: ram123    28




No comments:

Post a Comment