Posts by dodger7

  • Autosort

    Re: Autosort


    click post reply, then click on the paperclip to the right of the smiley face.
    a new window will open. click on the top "browse" button, find your file, click open then click upload. once uploaded you can close the window.

  • Autosort

    Re: Autosort


    if you place the following code behind the relevant sheet module it should work:


    Code
    Private Sub Worksheet_Change(ByVal Target As Range)


    Range("B2:C43").Sort Key1:=Range("C2"), Order1:=xlAscending, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal


End Sub


    this is presuming your data has a header and you want to sort by score

  • Find Name In Column & Return Left Corresponding Value

    Re: Name Based On Ranges In Another Table


    you could paste this formula into "B2" and fill down


    =IF(A2>'Condition Table'!$C$6,'Condition Table'!$A$6,(IF(Data!A2>'Condition Table'!$C$5,'Condition Table'!$A$6,(IF(Data!A2>'Condition Table'!$C$4,'Condition Table'!$A$5,IF(Data!A2>'Condition Table'!$C$3,'Condition Table'!$A$4,IF(Data!A2>'Condition Table'!$C$2,'Condition Table'!$A$3,'Condition Table'!$A$2)))))))[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]or do what Stephen said! easier

  • Lanch Internet Explorer, Input And Copy Data

    Hi Guys


    wonder if anyone can help. I have a list of Staff ID's in column A (around 200)
    im trying to launch an intranet site, enter the staff number into an input box and search.
    Once the results appear, put the results into the cell next to the staff number (column B).
    i realise this is very difficult to assist me with, since obviously you can only access an intranet site from within
    the company but If I had to post the source code can someone point me in the right direction?


    Or am I trying to do the impossible?


    Thanks in advance


    Jamie


    source code:


  • Formula Reference To WorkBook With File Name In Cell

    Re: Reference To Book With Variable Name


    you could do it in VBA by inserting the following routine:



    then place the routine behind a button.


    I have uploaded an example, as theres a few presumptions (sheet names, cell refereces etc)
    HTH

  • Dynamically Add Controls To User Form Macro Code

    Re: Dynamically Adding Controls To User Form


    if you change

    Code
    Set ctl1 = Me.Controls.Add("Forms.Label.1", ctl1, True)


    to

    Code
    Set ctl1 = Me.Controls.Add("Forms.Label.1")


    and add a caption:


    Then it should work.


    HTH

  • Lookup By Partial Text Match

    Re: Filtering By Vba


    also, when you are creating a new thread the rules clearly state you put what your problem is, not what you think the solution to be. Some people who know many formulas but not VBA would probably avoid looking at your thread, when vba is not necessary.


    HTH in future

  • Return Random Cell From Range

    Re: Creating A Random Button


    Hi Tony


    Yes the #NAME? error means that you will need to install the analysis toolpak. unfortunately there is no way round this (for randbetween anyway)
    Im sure there is probably a way to do it using just rand() that maybe someone else can maybe help you with.
    Are you sure you cant just go to tools>add-ins and check the analysis toolpak box?


    Jamie

  • Return Random Cell From Range

    Re: Creating A Random Button


    you can create a button and assign the following macro:


    Code
    Sub Button1_Click()


Sheet1.Range("E15").Value = "=INDEX($A:$C,RANDBETWEEN(1,COUNTA($A2:$A65536)),RANDBETWEEN(1,3))"


End Sub


    where "E15" being the cell you wish to display the random value, and "A:C" within the formula being the range of columns you wish to search for the random value.


    Please note you will require the analysis Toolpak add-in to be installed for the "randbetween" function to work.


    see http://www.ozgrid.com/Excel/excel-random-pick.htm for more info (this was the first result when I searched "random cell")


    HTH

  • IF & Lookup In Different Workbook

    Re: Vlookup Multiple Columns In Different Workbooks


    presuming that "jack" is on A2 of sheet 1, workbook 1, you can put this formula in B2:


    =IF(VLOOKUP(A2,'[workbook 2.xls]Sheet1'!$A$2:$D$7,2,FALSE)>0,VLOOKUP(A2,'[workbook 2.xls]Sheet1'!$A$2:$D$7,2,FALSE),(IF(VLOOKUP(A2,'[workbook 2.xls]Sheet1'!$A$2:$D$7,3,FALSE)>0,VLOOKUP(A2,'[workbook 2.xls]Sheet1'!$A$2:$D$7,3,FALSE),VLOOKUP(A2,'[workbook 2.xls]Sheet1'!$A$2:$D$7,4,FALSE))))


    You can then copy the formula down the column. you will need to change the name of the workbooks, range etc.
    Note this will only work if theres only one number for each person. It appears your data could be better laid out. See Dave's thread on efficient spreadsheet design:


    http://www.ozgrid.com/Excel/ExcelSpreadsheetDesign.htm


    HTH
    Jamie