Posts by dodger7

    Re: Removing Color Filter Utility


    You can either do as shg said above, or read my previous post carefully. You said "you cant see it there". I said ignore the box that pops up, and go to the Data toolbar as you would normally and delete it by right clicking on it and selecting delete. Then you can close the pop up box when you have finished.


    The utility is very helpful and someone was kind enough to share it. Just because you do not know how to remove it, does not mean that it is not a good tool.[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]or if you cant do that run this:


    Code
    Sub Remove_Toolbar()
    Application.CommandBars("Data").Controls("Color Filter").Delete
    End Sub

    Re: Removing Color Filter Utility


    have you tried View>toolbars>customise
    Ignore the box that pops up, and go to Data>right click on the item you want to delete, and select delete.
    Close Excel down and it should be gone.

    Re: Filter Data Across Multiple Sheets


    Dave,


    I couldnt see the benefit of having all the data in one sheet because there was more than 65,536 rows. If I am wrong I apologise.


    Derk, I would have liked to use Access but unfortunately my knowledge dosnt allow me. Thanks though.

    Hi guys


    I have a workbook with 236,000 rows of data (accross 4 sheets). Each sheet is identical layout with differnet data. An example of the data is:


    Policy Number Scheme Number Name Agent code Scheme Name
    Kxxxxxxxxxx Jxxxxx Mr A example 5/xxxx Example Scheme
    Kxxxxxxxxxx Jxxxxx Mr A N Other 6/xxxx Another Example



    To do a search for a scheme number for example I would filter the 4 sheets using the scheme number I needed, then copy and paste the info from the 4 sheets into a 4th sheet so that I could work with the data.


    I need to know if its possible to do the following:


    create a useform to act as a GUI to that if a scheme number is selected it will search/filter the 4 sheets and present the results in a 5th sheet


    I could upland a sample if ti would help


    Many thanks in advance


    Jamie

    Re: Changing The Font Color Of Formulas


    try Edit>Go To...


    click Special>formulas radio button


    click Ok.


    This will select all cells containing formulas. You can then change the font color and it will update all cells containing a formula.


    If your want to do it automatically you could place the following code behind the required sheet:


    Code
    Private Sub Worksheet_Calculate()
        Selection.SpecialCells(xlCellTypeFormulas, 23).Select
        Selection.Font.ColorIndex = 3
        ActiveSheet.Range("A1").Select
    End Sub


    HTH

    Re: Lookup/Reference Text From 1 Worksheet Into Another


    see daves links for full explinations. The drop down list needs to be pulled from a list on the SAME sheet. On my example this is the list starting "AA6" (I have changed the font color to white so that the user cannot see this list). If you also see Daves link on dynamic ranges this will explain how to have the drop down list automatically updated if you add to the list under AA6.


    Ive attached an example of a button that will clear your daily data. This deletes the food, portion, date and notes.


    Cheers
    Jamie

    Re: Conditional Formatting - Change Cell Based On 2 Conditions


    Conditional Formatting uses the first condition that is true and ignores the rest so you want to put the "blue" condition first.


    So condition 1 would be:


    Condition1 - formula is: =IF($B$1="Done","true","False") and format to be blue


    Condition2 - formula is: =IF($A$1="Top Priority","True","False") and format to be red


    HTH
    Jamie

    Re: Remove/Clear Duplicate Cells


    Hi Ger


    I will run this when I get to work and let you know how I get on.
    Thank you for your input


    Jamie[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Hi Ger


    The code run very quickly (1 minute exactly) however its not working as expected.
    The code returned around 25,000 unique ids (cant remember exact figure) however I know this is incorrect because;


    each column is a unique list, i.e there is no repeat of a user id in the same column. Each column averages around 35,000 user IDs so I would expect this to be the lowest figure returned from the code. To clarify, each column represents the Unique IDs logged in for that month.


    Thanks
    Jamie[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Hi Ger

    Re: Remove Duplicates


    Hi Ger


    Thanks for your reply. The code I have was from ozgrid search, remove duplicates. here it is:




    I may have a look at pivot tables, thanks

    Hi Folks


    I wonder if anyone can point me in the right direction. I have a workbook with 12 sheets, 1 for each month of the year. Each month has around 30,000 user IDs on it (this is a log of each time a user logs into our website).
    What I need to do is find out how many unique users have logged into our site for the whole year, or in other words, remove duplicate user ids accross the workbook.
    I realise that this will probably destroy my processor, so was wondering the best way to do it?
    I also have all this information in ONE spreadsheet if it would be easier? I just copied and pasted each month into a column (Jan is column A, feb in B, mar in C etc)


    I ran some code on this sheet to remove dupllicates from column A:E but this was taking AGES!


    Thanks in advance for any help


    Jamie

    Re: Extracting String Within Brackets From A Relative Cell


    you can do it by using data>text to columns... then choosing delimited, and using other character as "(". Then repeat for ")", This will split the text and ann the text between ( and ) will be in a separate column.


    you can place the following code in a module and assign a button to it:



    make sure you change ranges accordingly.


    HTH


    Added by Admin


    =MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1)

    Re: Autosort


    no your not stupid, its probably because we are using different versions of Excel. What you could do, which helps when learning vba, is this:


    * Go into the VBA editor (F11), view the code of the relevant sheet, and delete all the code except for sub and end sub. You will be left with this:


    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    
    End Sub


    * close the VBA editor, then go to tools>marco>record new macro. Sort your data in the normal way (data>sort..) then stop the recording.
    * go back into the VBA editor, and it will have created a new module. View this code.
    * copy all the code between sub and end sub, go into the relevant sheet code, and paste it between the worksheet_change sub and end sub.


    This code should then run each time a change is made to the worksheet.

    Re: Autosort


    your quite a gifted man... sorry joking aside...


    did you look at my example I uploaded for you? compare it to yours, make sure you are placing the code in the correct module - very important.


    To reiterate, pref F11 to go into the VBA editor. Under Microsoft Objects, right click on the sheet that your data is in and select view code. Then paste all of the code I posted earlier into here. This code runs everytime the sheet is changed, so its imperitave it goes onto the correct sheet module.