Posts by r_hind

    The simplest way to deal with this would be to maintain a list elsewhere (another sheet or area ..other than below the month number in row 35) and use a look up table.

    The challenge is finding the values where you want them located.

    I would question whether you're making it hard for yourself with the spreadsheet structure. The best rule in spreadsheet design is to keep the data entry as simle as possible then let the spreadsheet do the work (analysis) for you. The moment you have to think about where you enter the data (and move to get to the entry point) you're going against this principle.

    Anyway bear with me ...working to a solution...

    The code in my previous posting was to designed to list a series of files that matched a specification (including wildcards)...the files were listed in a named range within an excel worksheet.

    in the following example the named range "xlVar_FileString" (in excel) could contain say
    "*same.xls" where the letters same represent the component of the file name that doesn't change.

    Public vba_CriteriaList(), vba_FieldList(), CurrentFileName As String, vba_FileListArea(), vba_RecordNo As Integer, TempData(), vba_FieldNo As Integer, vba_CriteriaNo As Integer, X As Integer, Y As Integer

    Public Sub ListFiles()

    ' zero value in Range Name "xlVar_FileCount"
    Range("xlVar_FileCount").Value = 2

    ' Clear FileList Display area
    Worksheets("Control Sheet").Activate

    Application.ScreenUpdating = False

    ' Get Folder value from Range Name "xlVar_Folder"
    vba_Folder = Range("xlVar_Folder").Value
    ' Get file name string (wildcards allowed) from Range Name "xlVar_FileString"
    vba_FileString = Range("xlVar_FileString").Value

    ' Do file search based on Parametres above
    Set fs = Application.FileSearch
    With fs
    .LookIn = vba_Folder
    .Filename = vba_FileString
    If .Execute(SortBy:=msoSortByFileName, _
    SortOrder:=msoSortOrderAscending) > 0 Then

    ' display dialog box showing total number of files returned
    MsgBox "There were " & .FoundFiles.Count & _
    " file(s) found."

    ' Position at Named Range "xlVar_FileList"
    ' so offset positions results
    For i = 1 To .FoundFiles.Count
    ActiveCell.Offset(rowOffset:=i - 1, columnOffset:=0) = .FoundFiles(i)
    Next i
    ' Store total number of files returned in Range Name "xlVar_FileCount"
    ' Value in Range Name "xlVar_FileCount" is used to re-size
    ' Dynamic Named Range "xlVar_FileListArea"
    Range("xlVar_FileCount").Value = .FoundFiles.Count
    MsgBox "There were no files found."
    End If
    End With
    Application.ScreenUpdating = True

    End Sub


    If you're going to dual post how about letting people know of the other post/s.

    There is nothing worse than spending time trying to help someone only to find someone else has already given the answer elsewhere.

    Remember people are trying to help ...don't abuse their generousity.

    I've just realised the numbers you refered to (that appeared alongside the data) was not the number next to the drop down list.

    The other numbers are used as an index to the cells in the data area. The item returned by the list box gives you the column, the numbers down the side refer to the row the data comes from.

    When you're using a drop down list from the "Forms" toolbar (as opposed to the "Control Toolbox") the value returned is the number representing the position (of the selected item) in the list (ie 1 = 1st in list abd so on).

    Yes this is one of the values you would normally hide (either behind the drop down list or in a hidden cell or worksheet).

    I've no idea why you can't save the file to your computer.

    Are you logged onto OzGrid using a logon and ID?

    Do you have any security set up in internet explorer or windows that prevents you saving?

    I've never worked with regression calculations but I gather your'e looking to summarise only the final rather than intermediate results, and perhaps for a number of scenarios.

    If this is correct you will probably find that Data Tables will assist.

    Take a look at

    If you're able to determine where the end results will appear (cell reference or offset etc) it will help ...but even if you can't and a macro or search can achieve the answer (cell positio) then the Data Table approach should work.


    Try this out...

    Concepts used include
    Named Ranges
    Dynamic Named Ranges
    Array Formulae
    Drop Down List Box

    I've deliberately left this untidy and with values that could be hidden do want to end up doing this yourself don't you??!!

    Hi excelnewbie

    THe example you've posted suggests that you always have the same pieces of information (same quantity, same subject, same type of value).

    Is this correct?

    Do you enter the $ and Quantity totals or are they calculated?

    Your answers will help us understand the best solution for you.