Posts by dodger7

    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.

    Re: Autosort

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

    Private Sub Worksheet_Change(ByVal Target As Range)
        Range("B2:C43").Sort Key1:=Range("C2"), Order1:=xlAscending, Orientation:=xlTopToBottom, _
    End Sub

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

    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

    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


    source code:

    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)

    Re: Dynamically Adding Controls To User Form

    if you change

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


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

    and add a caption:

    Then it should work.


    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

    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?


    Re: Creating A Random Button

    you can create a button and assign the following macro:

    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 for more info (this was the first result when I searched "random cell")


    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: