Posts by btadams

I've created an add-in with some functions. If I Go to Tools -> Add-ins and load it up there are no problems. However, when I close and re-launch Excel there is an error message about editing a hidden workbook and the line that seems to be causing the error is:

Application.MacroOptions macro:="INTERPOLATE", Category:=4

any ideas as to why? Thanks,

I don't know if a custom number format can do that but here's a formula that will convert it:

=INT(A1/10) +RIGHT(A1,1)/8

also, if cell A1 contains the log10 value of 0.69897, then in cell B1 enter the formula =10^A1

this may work. You may need to change the Range("A1") to whatever cell you want to start the selection in.

Sub NameCells()
Dim cell As Range

Range("A1", Range("A1").End(xlDown)).Select
For Each cell In Selection
cell.Name = cell.Offset(0, 1).Value
Next cell
End Sub

This example will copy the usedrange of a worksheet to a hidden worksheet each time the workbook is closed. It also puts the date and user name above the copied data. To implement this, right-mouse click on the Excel icon next to the File menu and choose View Code. Then change the drop-down that says (General) to Workbook. Then in the drop-down next to it, select BeforeClose and then paste this in:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Sheets("Hidden Sheet").Visible = True
Sheets("Hidden Sheet").Activate
If Range("A1").Value = "" Then
Range("A1").Select
Else
Range("A1").End(xlDown).Offset(1, 0).Select
End If
ActiveCell.Value = Date
ActiveCell.Offset(0, 1).Value = Environ("Username")
Sheets("Data Sheet").UsedRange.Copy
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Hidden Sheet").Visible = False
End Sub

You need to have a formula like this:

=ADDRESS(2,H4) &":"&ADDRESS(100,H4)

where cell H4 is the linked cell for the first drop-down (p.s. are you using the drop-downs from the Forms toolbar?)

Then you need to create a named range (Insert -> Name -> Define) called MyRange and in the Refers to: box put the formula

=OFFSET(INDIRECT(Sheet1!$H$6),0,0,COUNTA(INDIRECT(Sheet1!$H$6)),1)

where H6 is the cell containing the Address functions

Abbey,

to see all of Excel's statistical functions, go to the Insert menu and choose Function then when the Insert Function window pops up click on the Statistical category. If you then select a function you can click on the little yellow question mark to open up the help file for that function

Have you tried replacing it with

Application.WorksheetFunction.Max(searchRange)

Aladin Akyurek gave me this formula:

=INDEX($B$1:$D$1,MATCH(MIN(B2:D2),B2:D2,0))

where $B$1:$D$1 is the range of cells containing vendor names and B2:D2 is the range with the prices

Have you tried using a pivot table?

That should be asign with 2 s's. It appears that this board automatically replaces an a followed by 2 s's with the word bottom. What's up with that?

One way is to put buttons from the Forms toolbar for each custom view, then use the macro recorder to generate code like this:

Sub MyView1()

ActiveWorkbook.CustomViews("MyView1").Show
End Sub

and then right-mouse click on the button and assign the macro to the button

as an example say we have a regression equation y = mx + b where m = 3 and b = 100 and we want to know what value of x will give us a value for y = 300. Enter the formula =3*A1 +100 in cell B1 and in cell A1 enter 1. Then go to Tools -> Goal Seek and select cell B1 (our formula) as the Set Cell, equal to 300 by changing cell A1

I think the only way to do this is to put some code in the Workbook_Open event. With the workbook in question open, Right-mouse click on the Excel icon to the left of the File menu and choose View Code. Then in the drop-down menu where it says (General) select Workbook. By default the Workbook_Open event pops up. Put a line like this in there:

Sheets("Sheet1").Activate

Right-mouse click on the attachment and choose Save Target As and then drop it into a folder on your computer

I made some modifications to Dave's file. Still could use some smoothing out

not sure if this is any help but there is probably a file in your C:\Program Files\Microsoft Office\Office\Samples folder named solvsamp.xls that has an example of using Solver for scheduling

Need a better explanation. Do you want an inputbox so a user can enter a name and the database will filter on that name and copy some stuff to another worksheet?

Do you have any add-ins or personal macro workbook loading up automatically?

You'll want to set the range references to absolute when you fill the formula down:

=INDEX(Sheet2!$A$1:$A$20,RANK(Sheet2!B1,Sheet2!$B$1:$B$20))