Posts by Hans Pottel

    Here is some code that changes the contents in cell F3 as soon as the contents of cell F2 is changed. However, there may be a problem with the DDE, which I can't try out, but the code below is based on the Worksheet_Change event, which may not be triggered when the cell content is changed via DDE.

    1. Private Sub Worksheet_Change(ByVal Target As Range)
    2. Dim OldPrice As Double
    3. If Target.Address = "$F$2" Then
    4. OldPrice = Target.Offset(1, 0)
    5. If OldPrice <> Target.Value Then
    6. Target.Offset(1, 0) = Target.Value
    7. End If
    8. End If
    9. End Sub

    The code above checks if the target cell (which is F2) changes its contents. It this is the case, the code checks if the current content of cell F3 (offset 1 row, 0 columns) is different; if this is the case, the value in F3 is set equal to the value of cell F2.


    OK, here we go.
    I attached a workbook with an example. First you need to define the lists you'll use for validation. In the example I used a list of 3 vendors, in cells J2:J4 which I used as validation rules for cells A2:A17. This means that if you want to access one of the cells A2 untill A17, you will see a dropdown box with 3 choices: Vendor1, Vendor2 or Vendor3.
    Then I defined 3 other lists, one list for each vendor, with as an example, 3 products per vendor, which I called A1, A2, A3 for vendor1 (this list is in cells K2:K4), the products for vendor2 or B1, B2 and B3 and finally you can find the products C1, C2 and C3 for vendor3.
    Cell J6 contains a dummy formula which acts on column A. As a consequence, when you change a vendor in column A, this formula is automatically calculated (make sure the calculation is set to automatic, not to manual) and as such this triggers the Worksheet_Calculate event of the sheet. In the Worksheet_Calculate event, you can find the following code:

    This routine, which you can find in the sheet1 module, runs through all cells in the range A2:A20 and depending on the contents of the cell (cel.value) it changes the validation list accordingly, using another subroutine which is stored in the general module1.
    The code of this routine looks like

    The code in this routine is mainly recorded when setting validation rules and then adjusted to make it more general.

    I hope this helps you setting up your own dynamic validation rules.


    As far as I know, you can only do this using macros, and even then, it is not an easy thing to do. The reason for this is that when changing the entry in e.g. cell A2, using the data validation list you defined, there is no event triggered. You can easy see that by simply recording a macro while you are changing the entry via the validation rule. The macro will only record the selection of the cell, not the choice of your entry. There is a workaround via a trick, but I don't know if you are familiar with macros. If you are interested, let me know, I'll try to explain, but you need some VBA background.


    why don't you just keep track of the value each day on a separate sheet?
    You could organize it as follows: one column for the date and one for the price. Everytime you open your spreadsheet, let a macro run to check your last entry, check the date and add another line if necessary. You could put that macro in the workbook_open event.


    Don't know if I correctly understand this, but here is a macro that allows to select a range to copy, and select a destination range. Then only the values or pasted, using the pastespecial method.


    I don't know if this is of any help, but you should check the functions in the Analysis Toolpak Add-in, which is normally pre-installed but not activated during a normal installation. Go to Tools &gt;&gt; Add-ins and check the Analysis Toolpak checkbox. Now you should have a bunch of additional (engineering and financial )functions, which might be of use for you.


    the best you can do is to use the NORMDIST function. Suppose you want to plot the probability density function for a mean of 60.2 and a stdev of 4.026. You want to plot the pdf in the range [40 - 80]. Then put 40, 41, 42, ... 79, 80 (or smaller intervals if you wish) in one column (e.g. in cells A1 to A81) and next to that (e.g. in B1 till B81) calculate: NORMDIST(A1, 60.2, 4.026, FALSE) for the pdf or NORMDIST(A1, 60.2, 4.026, TRUE) for the cumulative pdf. Now plot column A vs B in an XY scatter plot.


    you should use the


    which has a Type property, which can be set to 8 allowing to select ranges from your spreadsheet

    1. Sub test()
    2. Dim R As Range
    3. Set R = Application.InputBox("Select your Range", "Trial version", , , , , , 8)
    4. MsgBox R.Address
    5. End Sub


    I don't know if this might be of help. You can use the GetOpenFilename method from the application object to select multiple files. The names are stored in an array, which can be used to further process the files (opening, deleting etc.)
    Modify the code below according to your specific needs.


    Maybe the old Excel4 Get.Document(76) and Get.Document(88) might be interesting for you. You can use it (indirectly) as a worksheetfunction by going to Insert &gt;&gt; Name &gt;&gt; Define; type in an appropriate name (e.g. NameOfFile) and type the macro formula =GET.DOCUMENT(76) in the "Refers to:" edit area. In the Excel spreadsheet you can then use =NameOfFile as a worksheetfunction. No VBA required.

    =CELL("FileName") gives the path + workbook name + sheet name
    =GET.DOCUMENT(76) gives the workbook name + sheet name
    =GET.DOCUMENT(88) should give the workbook name alone

    Or you can use:


    or for the filename without extension


    To get only the path:



    I don't know how it works, I can only guess but it seems to be an interative one-dimensional algorithm. If you want to be sure that the result is the same, you can add the goal seek tool in your VBA function: e.g. if you want the formula in D6 to become equal to 2.2 by changing the cell in D7, you can use something like this:

    Range("D6").GoalSeek Goal:=2.2, ChangingCell:=Range("D7")