Managers want figures: as clear as possible and in series of months. Directorates often ask for an outturn of the most important data from the database, namely the final results. A correct overview proves to be a challenge for companies.
In addition, the present 13 months must be shown In this overview. 13 months? Yes, to also show the data with the same month of a year earlier. In addition, the month of January of the year is to be taken into account immediately. The cumulative count of the number of products starts again.
You can see a Fictitious formatting of figures about homeless people In Amsterdam. Of the homeless, the first Message Done, then they Active. A few come on Intake In the municipality and again part of the Withdrawn In a home. In 2017, a total of 11 of the 413 Wanderers were withdrawn in a home.
Eventually the overview looks like this.
How can you make this yourself? It is to do with a combination of Access and Excel. The example above is from Excel only. In the next blog I’m going to collect the numbers with Access and Vba, but first is Excel’s turn.
The months in the left column
You see that I have the thirteen months in the left column under have put each other starting with the month in which I started writing this blog. Because the months are dynamically displayed you will see Jan-17 on top of the next month and each month with the corresponding numbers, a line is pushed down.
You can also see that the month of January immediately jumps into the eye. When you open the sheet in January 2018, the month of December will also be a rule and Jan-17 still knows the same format. By using a rule in conditional formatting I show you how this works. I’ll be back in the blog later on. First the month listing in the left column.
Text(), date () and today ()
With a combination of the Excel functions Text() and date () and today () (hyperlinks to the Microsoft sites) allow you to dynamically drop the months.
We start with the function date (Year; month, day). If you fill a cell with =Date(2017; 11; 1), then the outcome in Excel: 1-11-2017. Excel immediately applied the formatting of this result to the date format. Of course, you already knew that the date is a number. It corresponds to the elapsed number of days since January 1, 1900. You can see that by the format of the cell on Normally To put: 43040.
Instead of numbers again a function
for =Date(2017, 11, 1) We now fill in = Date (year (today ()); Month (today ()); 1). If you have the hyperlink of Today() has already read, it is easy to see that with the functions year (), month () of today () in the date function the numbers 2017 and 11 will be passed.
It is also easy to 1-10-2017 To show. Just reduce the month of today with one, so =…, month (today)-1 ; 1).
Adjust the value of the cell by using Text()
Now we can with the function Text() Adjust the value by presetting this formula with = text (dezeFormule; ” MMM-JJ “), which eventually results in the text ‘ Nov-17 ‘ or with a-1, ‘ Oct-17 ‘.
The full function is for example for the month April.
= text (Date (year (Today()); Month (today ())-7; 1); ” MMM-JJ “)
By putting down in the formula in the left column the month each time to reduce one, you get them in the picture as you see above. If you take this over, it will no longer be possible to start the first line with Dec-17 😉. And that is precisely what the intention is. Management wants to have the state of affairs from the current month at the top.
If you want to have a more flexible piece, you can Today() replaced by the name of a field. I have in this example the cell E1 to give the name reference date. Here I placed the function =Today().
This cell I gave the name ‘ reference Date ‘. You do that as follows; Put the cursor in the cell E1 and type “Reference date” in the reference. The cell reference can be found at the top left of ‘paste’.
Then change the formula as follows:
= text (Date (year (Reference Date ()); Month (reference date ())-7; 1); ” MMM-JJ “)
Now if you enter a different date in the cell E1, for example 1-9-2017 then change the months immediately. Nice right?
In the “TblManagement” tab, I have the digits from the Access table. Monthly, this table is automatically filled when the database is opened on the 1e of the month. The small part of the table now looks like this (you see that I am writing this part of the blog in December 2017;-)).
For the example I recommend you to take this data on a new tab and give this tab the name ‘ TblManagement ‘. At the bottom, double-click ‘ Sheet 2 ‘ to change the name.
If you have copied the data, then choose in the menu bar for Insert and then for Table. You’ll see Data also appear in my format. Additionally, the following function is VERT. Search () cleaner to see.
The VERT function. Search ()
With this function we will retrieve the numbers from the TblManagement tab. Put the cursor in cell B4 and double-click the function character ƒx in the formula bar. The following window appears. In the “Search and refer” category, select, and then scroll to the box below to VERT. Search appears and double-click it.
Then the fill window for the function will appear. Fill in the data as I did. My compliments as the first time the right result emerges.
The formula looks in the cell rather than = VERT. Search (A4; Table1 [#Alles]; 2; false).
Now it may be that the retrieved cell has no value (NULL). In that case, the outcome of the Formulie is #NB. This can be avoided with the function = If. NB (); That allows you to enter an alternative value. The final formula looks like this:
= If. NB (VERT. Search (A13; Table1 [#Alles]; 2; false); “”)
Insert the cursor in the cell A4 and copy the formula by double-clicking at the bottom right of the cell and see that the other database data is also retrieved. In cell C4, you place the formula = as. NB (VERT. Search (A13; Table1 [#Alles]; 5; false); “”), which retrieves the fifth column of the tblManagement. You can also copy these. If necessary, use the F4 function key to make a (part) of the formula Absolute.
Change the reference date
The nice thing I find now is that all data will be adjusted immediately as soon as the reference date changes. As you can see is also The formatting is already adjusted to me. The month of January has a different color and the numbers of that month have become light gray. We do this with the ‘ conditional formatting ‘ in Excel.
The conditional formatting
At a glance, management wants to view the data of the month of January as it is the starting point of the cumulatives. With the conditional formatting rules We are also going to bring this together dynamically.
Select the cells A4: A16 and then choose the menu bar from the Start menu for ‘ conditional formatting ‘ and then for ‘manage rules’.
In This screen you choose a new line, and the next screen will appear. This was completed by me as below. Maybe search here, but very complicated it is not to fill the rule. Choose your own layout. If you want to use the purple color of Florijn.com, choose “More Colors” in the format. ‘ Custom ‘ and the RGB values (188, 68, 131) and click Ok a few times to go back. In the selected series you see the cell with the value ‘ Jan-17 ‘ in the desired color.
Now select the range BE: C16 in the adjacent columns. These cells will need to be dimmed if the month of January of the reference year is in column A.
The second line we fill in similar ways as the first, after we have chosen the type of rule: ‘Use a formula to determine which cells are formatted‘. In the format values that this formula applies to box, enter the text: = $A 4 = “Jan-” & Text (reference date;” JJ”).
If we choose the light gray color or Rgb(242,242,242) and click Ok a few times, we have the Conditional formatting Also applied to the chosen Range.
With this we have completed our Managementsmodel. In A next blog I explain how the figures can be created and brought in. Of course we like to hear if you have any questions or comments!
Do you still work with obsolete desktop software? An MS Access Developer /MS Access expert /MS Excel expert Van Florin easily builds your MS Access database to a Web application in the cloud, in which your data is completely safe, you do not store unnecessary data and you can easily choose who can join. You can also access your data from anywhere.
View Here How we can help you with Secure your business information.
Do you have questions? Feel free to call us: 020 6 254 254.