Replies

I haven’t taken any of the classes, but I do get Chandoo’s RSS feed. Often there are valuable nuggets worth investing some time in to learn a new technique or a formula that on it’s face makes no sense to me. His follow up to questions is very good.

Mark

The authors of WealthTec are the people who you need to ask this question to.  As a commercial package, I doubt you would be able to access the code modules to look at any possible interactions using the VBE.

Rory,

You are correct, however there are several features in Name Manager that are only made available once you purchase FastExcel.  I just checked and I have the latest release, 4.2 Build 621, and it still has these limitations. 

Mark

Have a look at Name Manager by Jan Karel Pieterse at http://www.jkp-ads.com.  There is a free version you can checkout that has many of the features you may want to use disabled, but you’ll at least be able to see what it is capable of doing for you.

Mark

I’ll add  one comment to the global and local named range issue.  I frequently use the same name in multiple worksheets that are identical in design and data structure with no problems at all, but will never use the same name locally and globally.

I get the opposite results of what you did, but I opened Book1 first to make the names available to Book2 as soon as it opens.  The vlookup of Book2 results in in the display of the globally named range, not the Sheet1 range of Book1.

To link to the range name MyRange1 defined for Sheet1 of Book1, I changed the vlookup formula to =VLOOKUP(A2,[book1.xlsm]Sheet1!MyRange1,2).

As you know, you probably need to change your global name which is easily accomplished in the Name Manager to something that reflects the purpose of the name.

Daniel,

If you use a linear trendline, the mathematical equations used to plot that line are only accurate for that line which may or may not approximate the scatter plot. There are lots of trendlines with varying mathematical approximations, so maybe you need to select a different trendline till you find one that looks close to the scatter plot. That will give you equations that more closely match each other.

I’ve attached a chart that shows the plot of some temperature data over time that has a 4th order power trendline added. Their equations won’t be close at all, but a averaging trendline will more closely match that of the scatter plot.

Marty,

RetiredGeek provided some excellent suggestions for resources. Let me add a few that I use.

For books by the author who finally made vba make sense to me, choose any of John Walkenbach’s books. For vba, “Excel 2007 Power Programming with VBA” would be my choice. It exists for 2002, 2007, and now for 2010. Walkenbach also has great books on formulas and charting. “Excel Hacks” is a treasure trove from the authors at Ozgrid. Ozgrid has an almost free newsletter ($1.00).

Chuck Pearson at CPearson has excellent formula and vba tips. Allen Wyatt’s “ExcelTips” at ExcelTips has a Tips newsletter and site in for both the Menu and Ribbon versions of Excel.

The resources are boundless; start reading and practicing.

To open the Visual Basic Editor (VBE), the correct keystrokes are “Alt + F11”. “Ctrl + F11” will open a new Macro sheet reminiscent of old Excel.

To start making some easy sense of some tasks you already have a good grasp of, use the Macro Recorder and then study the code in the VBE. It won’t be optimized, and will be verbose, but it will get you going.

Good luck in your adventures with vba. It can be a blast.

If I understand you correctly and you want the median wait of each group of wait times (0-1 & 2-3) for example, then you must list all forty “waiters” and the length of their waits to calculate the median.  The same would be true for each cohort.

=SUM(INDEX(A1504:CQ1510,0,95)) should do it nicely.

Andrew is right on about using show.  To add a thought to that, instead of closing the form as you apparently are now when you think you’re through with it, use hide instead.  As Andrew said you can then click on a button to show it again and it should remain populated with the data it had when you hid it.

First I named the columnar data ranges to make the formulas more concise.  For the items I chose “Items”; for Actual and SOW I chose “Category”; and finally for the amounts I used “Amounts”.

For Total Actual I used =Sumif(Items,”Actual”, Amounts) to arrive at the answer.

For Total SOW I used =Sumif(Items,”Actual”, Amounts).

If you are truly only looking for 36.50, then you can take the range B1:B52 and create a conditional formatting rule that highlights any cell in that range that contains that value. Create another rule that sets the font to white if the values are zero. That eliminates the clutter.

On the other hand, if you are really wanting to test for non-negative values, then instead of, or as well as rule one, create a rule thats test cell values for values greater than zero (or non zero).

I too successfully printed with no movement of the drawing objects. I used a Brother Color Laser printer and achieved the output expected.

Because both Catharine and I did not experience the problem that you are seeing, I would suspect your printer driver’s interaction with Excel is the problem. See if your printer has a new driver available, or re-install your current driver to see if you can find a resolution.

I have data in 2 columns, with date in column C and values in column  D.  I am using the following array formula to determine the minimum for each water year. Column G has the water year reference.

{=MIN(IF(YEAR(DATE(YEAR($C$3:$C$333),(3+MONTH($C$3:$C$333)),1))=G3,$D$3:$D$333))}

How do I
(1)    Determine the date of each year’s minimum value?
(2)    Determine the cell address of each year’s minimum value?

Thanks!

Great example that I looked at with great interest.  I am perplexed by one piece of the formula though.  Why the 3+Month rather than just Month?

thanks,

Mark Trevithick

I know of no way to do what you’re asking with a formula, but it can be done in vba.  Below is code that I modified from http://www.ozgrid.com.  Copy and paste it into your Worksheet_Change event.

I hope this helps.

Mark Trevithick

‘—————————————————————————————
‘ Procedure : Worksheet_Change
‘ Author : http://www.ozgrid.com/VBA/run-macros-change.htm
‘ Date : December 03, 2009
‘ Purpose : Add Target plus Offset Cell
‘—————————————————————————————
‘
‘modified by Mark Trevithick

Private Sub Worksheet_Change(ByVal Target As Range)

‘Do nothing if more than one cell is changed or content deleted
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub

‘Set your Range to the appropriate Range _
A Named Range would be best

‘If Not Intersect(Target, Range(“A1:A10”)) Is Nothing Then
If Not Intersect(Target, Range(“myRange”)) Is Nothing Then

‘Ensure target is a number before multiplying by 2
If IsNumeric(Target) Then

‘Stop any possible runtime errors and halting code
On Error Resume Next

‘Turn off ALL events so the Target * 2 does not _
put the code into a loop.
Application.EnableEvents = False

‘Add the Target plus the cell to the right
Target = Target + Target.Offset(0, 1)

‘Turn events back on
Application.EnableEvents = True

‘Allow run time errors again
On Error GoTo 0

End If

End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub