Posts by Hans Pottel

Have a look at this website:

http://www.cpearson.com/excel/…tm#HighlightingDuplicates

Clearly, I must have misunderstood your question. I don't know the INDIRECT method, (which I would like to know, by the way), and therefore I just presented the only way I know: via VBA.

Actually, I don't see how you can get rid of your lists; either you put them somewhere in your workbook or you hardcode them in VBA.

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.

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim OldPrice As Double
   If Target.Address = "$F$2" Then
      OldPrice = Target.Offset(1, 0)
      If OldPrice <> Target.Value Then
         Target.Offset(1, 0) = Target.Value
      End If
   End If
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.

Hans

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:

Private Sub Worksheet_Calculate()
   Dim cel As Range
   For Each cel In Range("A2:A20")
   If cel.Value = "Vendor1" Then
     Call SetValidation(cel.Offset(0, 1), "=$K$2:$K$4")
   End If
   If cel.Value = "Vendor2" Then
     Call SetValidation(cel.Offset(0, 1), "=$L$2:$L$4")
   End If
   If cel.Value = "Vendor3" Then
     Call SetValidation(cel.Offset(0, 1), "=$M$2:$M$4")
   End If
   Next
End Sub

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

Sub SetValidation(RVal As Range, RList As String)
    RVal.Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=RList
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

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.

Hans

Maskar,

If you first put Oldprice equal to the contents of cell F3 and then check if oldprice is different from the value in F3, how can you expect it to be different? I think there is something wrong there.

Hans

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.

Hans

replace the semi-colons by commas. This has to do with my regional settings.

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.

Hans

Try

=IF(ISNUMBER(O7);AVERAGE(C7:O7)/AVERAGE(C10:O10);0)

Hans

Did you set a reference to the solver add-in before using it in VBA? You should do this in the VBE via Tools >> References.

Hans

Maybe this text might help.

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.

Sub CopyPaste()
Dim Rinput As Range
Dim Routput As Range
Set Rinput = Application.InputBox("Select range to copy", "CopyPaste macro", , , , , , 8)
Set Routput = Application.InputBox("Select destination range", "CopyPaste macro", , , , , , 8)


Rinput.Copy
Routput.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

Hans

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 >> 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.

Hans

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.

Hans

you should use the

Application.Inputbox

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

Sub test()
Dim R As Range
Set R = Application.InputBox("Select your Range", "Trial version", , , , , , 8)
MsgBox R.Address


End Sub

Hans

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.

Sub OpenFilesOnNetworkDrive()
    Dim i As Integer
    Dim Filt
    Dim FiltIndex
    Dim Title
    Title = "Select files"
    Filt = "Excel files (*.xls), *.xls," & "Text files (*.txt), *.txt"
    FiltIndex = 1
    ChDir "\Serverserver_cMy Documents"
    FileName = Application.GetOpenFilename(FileFilter:=Filt, FilterIndex:=FiltIndex, Title:=Title, MultiSelect:=True)
    If Not IsArray(FileName) Then Exit Sub
    For i = 1 To UBound(FileName)
       Workbooks.Open FileName:=FileName(i)
    Next i
End Sub

Hans

Check the box next to Formulas in Tools >> Options >> View tab under Window options

Hans

as the inverse function of log10 , you can use the POWER function. e.g if LOG10(5) = 0.69897, then POWER(10, 0.69897) = 5.

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 >> Name >> 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:

=MID(CELL("Filename"),FIND("[",CELL("Filename"))+1,FIND("]",CELL("Filename"))-FIND("[",CELL("Filename"))-1)

or for the filename without extension

=MID(CELL("Filename"),FIND("[",CELL("Filename"))+1,FIND(".",CELL("Filename"))-FIND("[",CELL("Filename"))-1)

To get only the path:

=LEFT(CELL("Filename");LEN(CELL("Filename"))-LEN(NameOfFile))

Hans

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")