Software Search, Categories and Specials Contact us:  raina@ozgrid.com

  click here for our Excel Newsletter Archives
 

Ozgrid, Experts in Microsoft Excel Spreadsheets
Javascript DHTML Drop Down Menu Powered by dhtml-menu-builder.com

Active DataXL - Download

 

AnalyserXL - Download

 

DownloaderXL - Download

 

Smart VBA - Download

 

TraderXL - Download

OZGRID EXCEL NEWSLETTER

 

DOWNLOADS FOR XL - EXCEL TRAINING - EXCEL TEMPLATES - EXCEL DASHBOARD REPORTS

CODE VBA - FREE DEMO DOWNLOADS - MORE SOFTWARE

CLICK HERE TO VISIT OUR FREE EXCEL 24/7 HELP FORUM

Note - This Newsletter will be published mid-month, every month on the Website and via the FORUM.  If you would like to subscribe to it, please email: newsletter@ozgrid.com and we will put you on the mailing list and send it directly to your email.  We will never send anything other than the Newsletter to you. 

Welcome to the relaunch of the OzGrid Newsletter. This newsletter was published every month for 10 years from 2001 to 2010 by OzGrids founder, Dave Hawley, until such time as illness made it impossible. 

As most visitors to this website are aware, Dave passed away from a rare brain disease in July 2013. He battled the disease for 10 years.  First it took his speech, then his balance and then eventually everything.  Dave lost the ability to talk within the first 6 years of the disease and could not speak at all.  For the last 3 years of his life he was totally wheelchair bound and fed via a feeding tube.  As his  body grew stiffer and stiffer he was eventually unable to move anything other than his index finger on his right hand. 

Dave loved OzGrid and the community that it had become.  Up until the very end he ran OzGrid through me.  Whilst he still had some use of his fingers he used a speech machine for basic communication. In the end, I asked him questions and he blinked.  Two blinks for "Yes" and one for "No".  Eventually even this became impossible.

The dedication that Dave had to OzGrid was phenomenal.  He loved the website and had put many thousands hours of work into it over the years.  He loved the interaction with the many friends he had made and in particular the OzMVP's and Forum Moderators.  It kept him going and gave him purpose.  On the internet no one knew how disabled he really was and he could be normal.

After mourning Dave for the last few years I have realised that he would want OzGrid to live on.  I intend to upgrade and revamp the website to ensure its longevity.  Part of this is the relaunch of the Newsletter.

I hope you enjoy the first edition........

OZGRID CODE GOLF

So we can have a bit of fun, OzMVP S O has instigated Code Golf for Excel.  This will take the form of a mixture of Excel and VBA Code. Every month we will post a couple of Questions in this Newsletter.  Any member of the Forum is entitled to answer the questions. If you would like to become a Member, you can JOIN HERE

Here are the Rules:

Questions:

1.  The question must clearly state whether a VBA or FORMULA answer is required.

2.  The question must include a target compatibility configuration. (e.g. "Must work on Windows 10/Excel 2013")

3.  There must be at least one known valid answer to the question.

4.  The question must supply all the required information for an answer (e.g. Example data set supplied)

5.  The question must comply with the forum rules. (e.g. No questions about password breaking)

6.  The question cannot give permission to amend any of the other rules for this game.

7.  Do not cross post the question looking for an answer on other forums

Answers:

1.  Answers to be posted on the OZGRID CODE GOLF Sub-Forum.

2.  Members can only post 1 answer per question, any further answers will be deleted and will not count.

3.  Once posted, answers will be "soft deleted" to prevent other from seeing.

4.  VBA answers must supply all the code required to fully execute the answer without error. (no undeclared UDFs!)

5.  VBA answers must only contain one executable statement per line, use of colons (":") is not allowed.

6.  Formula answers must be worksheet formulas only and cannot make use of any VBA or UDFs

7.  The member that sets the question is not allowed to answer.

8.  All answers must comply with the forum rules. (e.g. Use [CODE][/CODE] tags for all VBA answers.)

General:

QUESTIONS:

QUESTION 1 (VBA)

Create a user-defined function to return the desired UK tax year as a string from a given date.
The UK tax year runs from 06th April - 05th April, and should be displayed in the format "YYYY/YY" as it falls over 2 years. For example, 06/04/2013 - 05/04/2014 would be the "2013/14" tax year.
The function should work in a spreadsheet, be compatible with XL2010 on Win7, and must use the following declaration:
 

VB:

Public Function GetTaxYear(varDate As Date) As String
 

e.g. the date 25/01/2013 should return "2012/13" whereas the date 20/04/2013 should return "2013/14"

QUESTION 2 (Formula)

Create a formula to return the column letter(s) for a given column number, the formula must return #N/A if the number supplied falls outside the number of columns in the spreadsheet being used. Must be compatible with XL2003 - XL2013

POST YOUR ANSWERS HERE

____________________________________________________________________________________________________________________________________________________________________________

SUMPRODUCT using MULTIPLE CRITERIA

Supplied by Forum Member - gregh

 DOWNLOAD EXAMPLE

You can use the SUMPRODUCT Function to use the data in Table 1 below to create the results in Table 2. SUMPRODUCT allows multiplies corresponding components in the specified ranges, and returns the sum of those products.  This Function uses cell ranges as its arguments (the parts of the formula that make it work). It multiplies together the items in the ranges, and then sums up the result.

 Enter this Formula into cell D24 in Table 2

=SUMPRODUCT(($A$3:$A$18=$A24)*($D$1:$O$1=D$23)*($D$3:$O$18))

TABLE 1

Quarter     2015Q1 2015Q1 2015Q1 2015Q2 2015Q2 2015Q2 2015Q3 2015Q3 2015Q3 2015Q4 2015Q4 2015Q4
Sales Cat Code Total Jul-14 Aug-14 Sep-14 Oct-14 Nov-14 Dec-14 Jan-15 Feb-15 Mar-15 Apr-15 May-15 Jun-15
agr 646 435 0 0 0 374 61 0 0 0 0 0 0 0
fis 1367 190 0 0 0 0 313 -123 0 0 0 0 0 0
oth 1512 481 0 0 0 0 0 0 0 0 0 204 0 276
eng 1619 0 0 0 0 0 0 0 0 0 0 0 0 0
Fis 1935 0 0 0 0 0 0 0 0 0 0 0 0 0
OTH 1061 643 0 0 0 643 0 0 0 0 0 0 0 0
fis 1258 2,143 0 1,980 60 0 103 0 0 0 0 0 0 0
eng 1847 0 0 0 0 0 0 0 0 0 0 0 0 0
oth 1751 0 0 0 0 0 0 0 0 0 0 0 0 0
har 1437 0 0 0 0 0 0 0 0 0 0 0 0 0
dri 1693 0 0 0 0 0 0 0 0 0 0 0 0 0
har 1615 0 0 0 0 0 0 0 0 0 0 0 0 0
har 1571 14,083 0 0 0 0 10,228 0 3,855 0 0 0 0 0
har 1565 1,866 0 0 98 899 869 0 0 0 0 0 0 0
har 1582 1,860 0 0 0 0 0 0 0 1,860 0 0 0 0
har 1687 0 0 0 0 0 0 0 0 0 0 0 0 0
TOTAL   21,702 0 1,980 158 1,916 11,575 -123 3,855 1,860 0 204 0 276

TABLE 2

    Total 2015Q1 2015Q2 2015Q3 2015Q4
agr   0        
dri   0        
eng   0        
fis   0        
har   0        
oth   0        
Total   0 0 0 0 0

 

There are a couple of things to bear in mind when using SUMPRODUCT:

The range arguments must have the same dimensions. If they do not, SUMPRODUCT returns the #VALUE! error value.

SUMPRODUCT treats range entries that are not numeric as if they were zeros.

 

TIMER WHILE EDITING A CELL

Supplied by OzMVP/Moderator - cytop

 DOWNLOAD EXAMPLE

This example contains a 'Switch' button in Sheet 1 used to start and stop a timer

It displays a small userform that shows the time ticking down from a defined value. If the timer gets down to below another defined value, or less, the time display goes red with white text - If the timer runs out, it simply stops.

If any key is pressed in a certain range (tested with A1:D25 on sheet1) then the timer resets to the initial time. For simplicity, it does not query which key was pressed. Using the arrow keys to move the cursor though a monitored cell will reset the counter.

The code also checks the interior colour of cells. So you can add more cells simply by copying a monitored cell and then using PasteSpecial/format, even while the timer is running. Code for either a range or colour remains.

The values for Max Time, 'Red Flag' time and the Address/Interior Colour of the range to monitor are defined in the module modPublics so you can change them without diving into the code.  NOTE if you use a defined range, it must be one contiguous block (Easily changed).

The only way to actually monitor keypresses while Excel is in Edit mode is to use the Windows API, the same for the Timer. Excels' timer will not fire while a cell is being edited - so this example uses a Windows timer set to fire every second and a keyboard hook to read key presses at a lower level than Excel. Put simply, Windows itself is running both the timer and keyboard processing, not Excel, and Windows doesn't really care what Excel is doing.

WARNING - Because of this, the code is a little complicated and very dangerous (to your workbook at least, and can potentially crash Windows) if you do not know what you are doing. If the timer is active, you must stop it and unhook the keyboard monitor before closing the workbook.

If you try to modify the code, save it before every run. Any unhandled error in either the timer or keyboard procedures will crash Excel. That probably also applies to any unhandled error in any other procedure as well.

If you have any suggestions for improvements to this Newsletter, please email me - raina@ozgrid.com.  Thank you.

SEE THE WORK THAT THE DAVE HAWLEY FOUNDATION IS DOING HERE

 

EXCEL TRAINING - EXCEL HELP FORUM - EXCEL FORMULAS - EXCEL VBA MACROS - VBA RESOURCES - MORE FREE STUFF

 
See Also: Excel Templates Index Or, All Software

Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft