Excel at Excel....!!!!!(Ask anything and everything)

 Dear Lakshminarayanan

u have a interesting query which I think i have a answer, please provide me the following details :

Are the no of emplyees fixed at each location , and there names are also fixed. ( i saw that Mr X was named twice in Mumbai)

Would u like ur solution to be Macro based or u want to use simple Excel.

In case of Simple Excel u will have to delete a few rows or copy a few rows down to get the formulla. Also u will have to insert 2 coulmns at the starting in ur master data which can solve ur problem. using macros will not require any such thing. 

Regards

CA abhishek agarwal

9911044625

abhishekagarwalca @ yahoo.co.in

dear khaleel firstly thanks alot 4 ur kind help. really u r doing a gr8 job by helpin people. i thught u wont reply me as there are so many queries b4 u 2 solve them. but u reply each n every query very well. u also replied me well. now i m seekin further i have created few macros to perform particular tasks. now i want that if i type any alphabate in a particular cell then immediately preassigned macro must be run. let me explain suppose i create a macro(name: unhide)  to unhide some predefined rows or columns. now if as i type any alphabate in the cell $A$10 immediately macro(unhide) should be executed and predefined rows or columns must be unhidden. and as i delete the alphabate from the A10 cell again those rows or columns must be hidden. hope u wud got my query n reply me. thanx

Hi Naveen,

The below program does just wat u need. Whenever the value in cell C23 changes to "a", it runs the macro called "Macro2"

*****************************************************************************

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$C$23" Then
If Cells(23, 3).Value = "a" Then
Call Macro2
End If
End If

End Sub

*****************************************************************************

Hope this helps.

Hi Lakshminarayanan,

Abhishek agarwal seems to have understood ur problem and has a solution for it, please discuss it out with him.

Nevertheless you can put in a mail to me too, even i would like to take a look.

Regards

Hi Hemant, Brijesh and others,

Please go thru the book i have uploaded in "Files" section,

File is called - "Excel for beginners - Important Basics".

Pivot tables is a very vast subject, after going through it, if you still have any doubts, i would be more than glad to help you out.

 Hi Naveen

I have seen that Mr. Khaleel has answered your query in a very simple way.

Nevertheless I also worked on your problem and i also have devised a small VBA.

It is a bit complicated  but still u can have a look at it also.

U will have to copy the entire macro in the Sheet module (Not n the Work book macro )

The macro uses the property of "Change" in the sheet . As soon there is any change in Sheet data , it will execute itself . Effectively meaning every time u press ENTER or Update anything in the sheet , The Macro will run.  

The macro will work as follows :

If u type anything in Cell A1 , it will hide the rows and columns as specified in macro Hide_Row_ Col .

If u delete the contents from A1 or the contents of A1 is blank it will unhide the cells by using macro unhide_Row_Col.

The macro is : 

Public Cell_Change, current_cell

Sub Worksheet_Change(ByVal Target As Range)

    Dim rg As Range

  current_cell = ActiveCell.Address

    Set rg = Intersect(Cells(1, 1), Target)

    If rg Is Nothing Then

    UnHideRow_column      ' Check if there is any data in cells A1

    Else

        If Cells(1, 1).Value = "" Then   ' Check if the Cell  A1 is blank

        UnHideRow_column

        Else

        hide_row_Col

        End If

    End If

End Sub

Sub UnHideRow_column()

    If Cells(1, 1).Value = "" Then

    Rows("6:8").Select  ' These rows will hide , you can change these as u like 

    Selection.EntireRow.Hidden = False

    Columns("C:D").Select   '  These col will hide  u can change 

    Range("C5").Activate

    Selection.EntireColumn.Hidden = False

    Range(current_cell).Activate

    Range(current_cell).Select  '  This will take u back to the cell where u where                                                                    'working  

    End If

    Exit Sub

End Sub

Sub hide_row_Col()

        Rows("6:8").Select

        Selection.EntireRow.Hidden = True

        Columns("C:D").Select

        Range("C5").Activate

        Selection.EntireColumn.Hidden = True

Range(current_cell).Select

End Sub

Regards

Abhishek

Lakshminarayanan

I have sent u the email as answer for ur query .

regards

Abhishek

abhishekagarwalca @ yahoo.co.in

Hi Abhishek,

Your code looks pretty neat.

As far as reducing IF statements goes, i think u can avoid IF inside

Sub UnHideRow_column()

because as such there is a IF condition for calling it.

Rest is cool. Really like the attention to detail that u have given, like returning to the current cell and all, nice work.!!

Dear Abhishek,

its really very nice. & thanx alot 4 replyin my query. i have no words to thank 4 ur kind support

CA Naveen Kumar Agrawal

+91 9216408902

hi

hi

please tell me that how can i convert text into numbers & vice versa

like 500000 to five lakh &

five lakh to 500000

if there is any formula for this please tell me

please tell me that how can i convert text into numbers & vice versa

like 500000 to five lakh &

five lakh to 500000

if there is any formula for this please tell me

My email id is :- niteshkhandelwal261 @ rediffmail.com