Advancing cell referencing each month

[worthm]

Hello,
I am using Office 2010 and I am not a programmer. I have created a dashboard and each month I have to move the cell referencing to focus on the most recently passed month. For example, on tab Rankings right now I have cells AG9:AG19, AH9:AH19, and AI9:AH19. They are all currently referencing cells on another sheet called Calls Common Area. That sheet has call data for each employee for each month. Right now, those previously mentioned calls are pointing at April's data but when June starts I need to move them to focus on the May data. Is there any way I can make it do this automatically?

For example, on tab Rankings I have cell AG9 referencing as such: ='Calls Common area'!J11

When June starts I'll need to move the referencing as such: ='Calls Common area'!J12

I ask because this happens in a lot of places on my dashboard and a VBA solution would save me a lot of work.

I've attached my dashboard. Thanks in advance for any help

[mperrah]

In AE7 I put a data validation of the month names from CallsCommonarea C8:19 and named it ccMo,
I put the following formula in Rankings AG9 the copy down...

=INDEX(CallsCommon[#All],MATCH(Rankings!$AE$7,CallsCommon[[#All],[Month]],0),MATCH(Rankings!AE9,CallsCommon[#Headers],0))
now you can change the drop down month date and the values will adjust accordingly,
the nice thing of doing it this way, you can look at past months very easily

note: on the Rankings sheet you have David, on the calls Common area its Dave, change one so they match...

[SamT]

Place this code in a standard Module.

In your Worksheet formulas, Use "strPreviousMonth" instead of a month name, or number String.


Function strPreviousMonth() As String
'Returns the name of the previous month as a string
' Use "M" to return a single digit String, "MM" for a 2 digit String,
' "MMM" for the Month's abreviation, and "MMMM" for the Month's Full Name
'Currently set to return the full name

PreviousMonth = Format(DateAdd("m", -1, Now), "MMMM")
End Function

[worthm]

Thank you for your help mperrah. I really appreciate it!

It looks like I need to add it to my dashboard.

I'm getting an error message on your Index coding. It looks like some of that coding is approximate. What does #All do? I really don't understand how your piece of code works

[worthm]

Thank you SamT. It looks like your idea only returns the prior month name. I don't think this answers my cell referencing question.

[mperrah]

I'm sorry, I forgot to mention I made the calls per month section a Table
Go to the Calls Common area sheet
select the cells C7:V26 and form the "Insert" tab choose "table"
then from the "formulas" tab choose"Name Manager"
you'll see a "table" in the list of named items.
Click that table and choose "edit"
then name it "CallCommon"
the "All" is referring to this table

[worthm]

Thank you again mperrah I'll work on adding it to my dashboard

[mperrah]

Did this work as you were hoping?

[worthm]

I haven't gotten it working yet. Your cell range for the table makes no sense to me. C7:V26 includes the month names but it also includes some extraneous data and also some of the next table is included in the range. Perhaps I need to add another column for the months directly to the left of the employee calls data

[mperrah]

Typo (C7:V20 was intended, sorry old eyes here)
You could use C7:V19 to skip the totals row
But the lookup will find the match either way, so not a problem.
It finds the match for the Month in column(C),
then the Agent Name match in Row(7)
and where they meet is the result in the Rankings sheets formula

the part for $AE$7 is looking at the drop down for the month name to search, so it stays the same as you drag the formula down.
the AE9 part is the Agent name it will find , it changes to each rows agent as you drag the formula down.

[SamT]

My bad, sorry. The function must go in a standard module.

As an example, in D7 of the 'Rankings' Sheet of the 'Team - Dashboard' attachment: the formula would be

=strPreciousMonth()

I use Excel 2003, so I don't know what this formula in D9 of that sheet is supposed to return

=TEXT(TODAY()-DAY(TODAY()),"MMMM")

In any formula where you need the name of the previous month, an example

=Concatenate("Last month was ",strPreviousMonth())

[worthm]

Thanks again SamT,

The string =TEXT(TODAY()-DAY(TODAY()),"MMMM")
returns the prior month. This is May so it shows April. In June it will show May, etc. I use this so I don't have to update the month every month

[worthm]

Mperrah, thanks for all of your help but I cannot get it working. I get a result of 0 in every cell to which it applies

SamT, thank you as well but I still think your code only returns the month and does not change the cell referencing to pull the data for the new month

Im stuck

[mperrah]

13546
try this. I checked the formula all the way done in the 2015 Phoenix Data area.
I would assume there are more areas that need updating to this formatting.
What other areas are calculated by the call common area values?

[worthm]

Wow, that actually works. And I just realized that the reason I was getting zeroes for the May data is because those fields are blank.

To answer your question, What other areas are calculated by the call common area values? the answer is that is all of it. The AHT by CSR section is a year to date calculation and that is reflected on the Rankings tab already.

My question is this: I need to do the same thing with the FNOL #s column which is cells AH9:AH19. The data for those fields comes from the tab called CSR Claims area cells H5:H8, and H10, H12, H13:H17. How do I drag it over to the FNOL column?

Thanks again for all your help

[mperrah]

looking more closely at your data.
the calls common area has data arranged across by name and vertical by date,
but the FSOL area is across bt date and vertical by name,
is it possible to get the dates vertical on all the area pages?
that would make pulling the data for the Ranking much easier

[worthm]

OK, sorry it took so long. I've mostly completed the change to the CSR claims area tab. Here is the upload

Do you know if there is an easy way to turn a table of data on its side?

Thanks again for your help

[worthm]

I just realized that I have some employees on that table who are no longer with the company. They are there because they affect the count. Hopefully you have an idea on how to ignore them? They are Danielle, Liz, Reed, and Rebecca

[SamT]

I had a power outage due to a thunderstorm and I had a bit of a time figuring out what was going on with the code I was trying to use.
Anyway this is tested. When you use this function, Insert it with the FormulaBars's Icons. I wrote it so that you can click on Cells to use their values, or you can type String Vaules in as parameters.

Unfortunately, You used different values in cell(D7) on the Ranking sheet and Column C on the Calls Common area(sic) sheet. In the example book I downloaded, Rankings uses the month full name, and Calls uses the month abbreviation. :banghead: You also used different speeling of the employee names on the two sheets.

If you use the strPreviousMonth UDF edited to return Abbreviations in Cells like Ranking (C7) or use full months elsewhere, this function will work for you. For example On the Rankings sheet, I edited D7 to read "Apr" and in Q9 I used the Formula

=fncGetCalls($D$7,O9)
and copied it down to Q20. all cells (with good EmpNames, filled with the proper values from the Calls sheet.

I tried to make the Function as easy to understand and modify as possible for you.

Important! You are going to have to be consistent with names and like values across the Workbook for any VBA function like this to work.

Option Explicit

Public Function fncGetCalls(ForMonth As Variant, EmpName As Variant) As Variant

Dim MonthName As String
Dim NameEmp As String

Dim MonthRng As String
Dim MRow As Long
Dim NameRng As String
Dim NCol As Long

Dim LastRow As Long
Dim LastCol As Long

'Converts string and Cells (values) to strings
MonthName = ForMonth
NameEmp = EmpName

'Stop endless loops
With Application
.Calculation = xlCalculationManual
.EnableEvents = False
.ScreenUpdating = False
End With

'Note the dots before the VBA and Excel Objects below
With Sheets("Calls Common area")
LastRow = .Range("C7").End(xlDown).Row
LastCol = .Range("C7").End(xlToRight).Column

MonthRng = Range("C7:C" & CStr(LastRow)).Address
NameRng = Range(.Range("C7"), .Cells(7, LastCol)).Address

'Find the Month Row and Name Column
MRow = .Range(MonthRng).Find(MonthName).Row
NCol = .Range(NameRng).Find(NameEmp).Column

fncGetCalls = .Cells(MRow, NCol).Value

End With

'Restore Excel to it's normal operating state.
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With

End Function

[SamT]

Oh yeah, Put this sub in a standard module if you are going to attempt to write code. Sometimes when you run code under construction, it resets the state of excel, then breaks before it restores the state. You can always run this Sub by clicking inside the code and pressing F5.


Sub RestoreApplication()
With Application
.DisplayAlerts = True
.Calculation = xlCalculationAutomatic
.EnableEvents = True
.CutCopyMode = False
.ScreenUpdating = True
End With
End Sub

[mperrah]

To pivot or transpose a table or grouping of cells,
select and copy, then select an empty cell in an empty area,
right click and under paste choose paste special and advanced/special has a bunch of radio check buttons,
bottom right is transpose. Leave everything else default and choose transpose.
This will pivot the data. Some formulas don't rotate well but raw data does beautifully. I've started this on your fnol page

[worthm]

Sorry to hear about your weather. This is going to take some time to understand and implement. Thank you once again for all of your help!

[worthm]

Thank you for this mperrah. It saves me several hours of work!





To pivot or transpose a table or grouping of cells,
select and copy, then select an empty cell in an empty area,
right click and under paste choose paste special and advanced/special has a bunch of radio check buttons,
bottom right is transpose. Leave everything else default and choose transpose.
This will pivot the data. Some formulas don't rotate well but raw data does beautifully. I've started this on your fnol page

[worthm]

SamT,

I don't understand what this means: When you use this function, Insert it with the FormulaBars's Icons. I wrote it so that you can click on Cells to use their values, or you can type String Values in as parameters.

[worthm]

SamT and mperrah,
I get confused as to who is giving me what. Will these two different pieces of code work together? Do they fulfill the same role or different?

[SamT]

I don't understand what this means: When you use this function, Insert it with the FormulaBars's Icons. I wrote it so that you can click on Cells to use their values, or you can type String Values in as parameters.

My Bad. You don't have to use the formula bar.

When you are entering a formula in a cell and have the Formula Bar visible, (Available in the Options menu,) you can click on the "fx" and insert any Excel Function and any non-private function in any standard module. Edit fixed references to fixed cells so they have the dollar signs.

If you manually write a formula, you can click on a cell to use it as a parameter in the function,(add dollar sign as needed,) or you can use an actual name or month, (in quotes.)

I am attaching the copy of your book that I used. it may be that most of it won't work, but the formulas in Rankings Phoenix, top 5 CSRs, MTD should.

Experiment with the formula in Cell Q9,


Change the first parameter to "Mar."
Change the second parameter to "Brianna"
Change the first parameter to D7, and copy the formula down a couple of cells.
Select or Highlight D7 in the formula bar and click on Cell E7
Change it back to $D$7
Edit Cell D7 to April

[worthm]

SamT,
I'm really not a programmer so I'm not certain what this piece of code will do. Where do I put it? I know how to get to the VB. Do I just put it on the Ranking tab? Will it work for Ranking tab cells AG9:AG19 and AH9:AH19?

Thanks again for all of your effort




I had a power outage due to a thunderstorm and I had a bit of a time figuring out what was going on with the code I was trying to use.
Anyway this is tested. When you use this function, Insert it with the FormulaBars's Icons. I wrote it so that you can click on Cells to use their values, or you can type String Vaules in as parameters.

Unfortunately, You used different values in cell(D7) on the Ranking sheet and Column C on the Calls Common area(sic) sheet. In the example book I downloaded, Rankings uses the month full name, and Calls uses the month abbreviation. :banghead: You also used different speeling of the employee names on the two sheets.

If you use the strPreviousMonth UDF edited to return Abbreviations in Cells like Ranking (C7) or use full months elsewhere, this function will work for you. For example On the Rankings sheet, I edited D7 to read "Apr" and in Q9 I used the Formula

=fncGetCalls($D$7,O9)
and copied it down to Q20. all cells (with good EmpNames, filled with the proper values from the Calls sheet.

I tried to make the Function as easy to understand and modify as possible for you.

Important! You are going to have to be consistent with names and like values across the Workbook for any VBA function like this to work.

Option Explicit

Public Function fncGetCalls(ForMonth As Variant, EmpName As Variant) As Variant

Dim MonthName As String
Dim NameEmp As String

Dim MonthRng As String
Dim MRow As Long
Dim NameRng As String
Dim NCol As Long

Dim LastRow As Long
Dim LastCol As Long

'Converts string and Cells (values) to strings
MonthName = ForMonth
NameEmp = EmpName

'Stop endless loops
With Application
.Calculation = xlCalculationManual
.EnableEvents = False
.ScreenUpdating = False
End With

'Note the dots before the VBA and Excel Objects below
With Sheets("Calls Common area")
LastRow = .Range("C7").End(xlDown).Row
LastCol = .Range("C7").End(xlToRight).Column

MonthRng = Range("C7:C" & CStr(LastRow)).Address
NameRng = Range(.Range("C7"), .Cells(7, LastCol)).Address

'Find the Month Row and Name Column
MRow = .Range(MonthRng).Find(MonthName).Row
NCol = .Range(NameRng).Find(NameEmp).Column

fncGetCalls = .Cells(MRow, NCol).Value

End With

'Restore Excel to it's normal operating state.
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With

End Function

[SamT]

I'm really not a programmer
Let me address that with a very brief overview of Excel from a programmers point of view. IMO, all advanced Excel Users should have at least this knowledge, even if they never code a line. (Unless context indicate otherwise all capitalized terms are programming terms)

The Excel Application is an Object oriented thing. Objects have Properties and Methods, and Events occur to them. The Application (Excel) is an Object as is the Workbook, Worksheets, and Ranges and Cells, Rows and Columns. Columns have the Property of width. Ranges and Cells have the Properties of Interior and Borders. Some Properties return Objects of the same name that have Properties. The Range.Interior Property Object has a Property called Color. Other Properties of the Range Object are Value and Formula. ie, put the number 4 in a Cell and it's Value is 4. If its Formula Property is = 2 +2, its Value is also 4.

Range is an unusual Object because it has many specialized forms. Cells, Cell, Rows, Row, Columns, and Column are all Ranges. Range and all the plural forms are Collections, meaning they can be None, 1, or Many cells.

Object Methods are the way things get done. They are procedures inherent to the Object. ie the Range has the Delete Method that deletes that Range.

Events are things that happen to Objects. Go to a different Sheet and the Sheet_Activate Event is Triggered, which can be used to run a Procedure, (aka, Sub or Macro,) or it may trigger a Method. The Range has a Selection_Change Event. This is a commonly used Event with programmers and coders. Click on a certain Cell and it makes things happen.

VBA was designed to be easy to read and easy to code. To access and use an Object's properties, and Methods, it uses the Dot system. The following codes will set cell C3 on Sheet2 to red.

Sheets("Sheet2").Rows(3).Cells(3).Interior.Color = RGB(255, 0, 0)

With Sheets("sheet3")
.Cells(3, 3).Interior.ColorIndex = 3 'The index number of the red color in Excel's color picker
End With

Sheets("Sheet3").Range("C3").Interior.Color = vbRed 'Red is one of VBA's named Constants
About the VBE (VBA editor)
The first thing to do is click on the Menu >> Tools >> Options and in the form that appears check All the boxes in the Code Settings Frame and set your desired Tab Width. In the Window Settings Frame, I checked Default to Full Module View and Procedure Separator. On the Editor Format tab, you can set the colors of different type of Key words, and your preferred Font and Font Size. On the General Tab, Check at least Break on All Errors.

Next use the View Menu or Press Ctrl+R to see the VBA Project Explorer. the most used on the many Windows the VBE has for programmers.


13562
At the top of this image of the Project Explorer you will see two VBA projects. The (Team dashboard2015 - AZ.xlsm) is from your Workbook that I downloaded. This shows all the Objects in the Project contained in the Workbook. Note that many have two Names. In this Window, the Object,Name(s) are not in Parentheses, but the Tab and Files names are. In the Properties Window they are reversed and you can change any of the Object and Tab Names therein. In Code, you can reference Sheet Liz as Sheets("Liz") or Object Sheet10. ie, Sheet10.Range("A1").Value

IMO, Object Name(s) should not have spaces, but I would strongly consider renaming the major Sheet Objects to resemble their current Tab Names, ie, Sheet1 to TeamRankingsTrending. in a Project this size, there is no guarantee that after the code is done, somebody will decide to change a Tab name. a changed Tab Name can Break the code, but only a coder will change an Object name.

By Right Clicking in the Microsoft Excel Objects folder Area, I inserted an empty Form and Class Module, (advanced subjects,) just so you could see them. the standard Module Module3 was already in the Workbook. It contains the code for sub "Button9_Click" which exports the Workbook as a PDF File. I would rename the Module3 Object to modButtonClickSubs. (I rename all objects with meaningful Names, ie Button9 would be cbutExportPDF.




Other than renaming Objects, the VBE Properties Window is a must for designers of UserForms.



I'm really not a programmer so I'm not certain what this piece of code will do. Where do I put it? I know how to get to the VB. Do I just put it on the Ranking tab? Will it work for Ranking tab cells AG9:AG19 and AH9:AH19?
Press F4 to show the Properties Window, then Right Click in the Project Explorer Window, Excel Objects Folder and insert a new Module. In the Properties Window, rename the new module to modCustomfunctions.

Double Click on the Module to ensure that it's Code Pane is visible, (look at the VBE Title Bar at the top, it should end with "(modCustomFunctions)") and paste the code in the Visible Code Pane. Double Click on Module3 to see what I mean.

It will work in any formula that gets its data from the Table on Sheets("Calls Common area") in the Table that currently runs from Range("D7") down to ("D20") and across to the next blank Column to the right.



Don't worry, after you have designed 40 or 50 of these projects, the coding becomes much simpler. :D


Did you know that you can use Ctrl+H to change all instances of "Apr" to "April?" Or vice versa.

If you will ensure that all names in the workbook are used (spelled the same) correctly, and attach an Unprotected version, (or PM me the password,) it would not be hard for me to write a function that would work on any Table on you pure Data Sheets.

[mperrah]

Excellent dissertation on VBA samt. I see why you are referred to as the guru.
I have enjoyed Excel form version 4 I think was the first I used, it wasn't until 97 I discovered macros.

worthm. The code I have presented has all been formula to be entered directly to cells in Excel.
much (if not all) could be done from VBA so no formulas are needed. It really depends on user preference and skill level.
your project has a lot of data with many calculations going on. There are many ways to get to the end result. There can be drop downs, toggle buttons, user forms, hot-keys and more.
as samt said, consistent formatting and intentional naming schemes are essential for programming and functions to work properly.

glad the transpose is helping. I have most of the fnol table functioning with the month drop down. I can continue to help if you like, or if samt VBA is making sense we can follow his path. I'm happy to help either way

[SamT]

I rarely use any but the simplest formulas. I was laid up for a year at 50yo and spent that time learning the Excel Object Model as a way to keep my sanity and to prepare a construction office system for when I got out of that predicament. Well, I never got out, but kept an interest in programming.

When I was considering the worthy one's question about using that function elsewhere I looked at his entire book, and his Tables are well layed out. You seem to be familiar with Excel's new Tables. If you and he can come up with a Naming scheme, he should be able to do it all with Formulas and Tables.

All my Finction really needs is the same naming conventions for Ranges. Then feed it the Range Name, and the two reference values. At that point it is just another formula function that imitates the functionality of Tables. At least as I (poorly) understand them.

Did you know that, according to the Excel 5 and VBA Project Manager, that before 5, Excel was supposed to mostly used as a formula driven spreadsheet, but that with 5, MS decided to make it very much more of a List manipulator? Now we get thse wonderful data driven Apps like worthm's workbook.

[mperrah]

I'm a Padawan in the scheme of things, but very much enjoying the ride.
i wonder if we use indirect and match we can code every table with a similar structure. For the most part he deals with agents and date ranges, then calculates specific data from the raw data areas. I bet we could restructure the rankings page using indirect for the names of the ranges and use a data validation list of the fields he wants to track.
Otherwise I am considering using sumproduct with drop down options for the fields he wants to track. I worked on a project using sumproduct to track a group of employees proformance based on start and stop dates in a chart, and using the same data and same start and stop dates track an individual on a second chart. My progress was intirely done through this forum. I could upload the workbook for review

[Paul_Hossler]

I know I'm coming to this party late, but I've been following the discussions, esp. SamT's tutorials

Excel's pivot tables with Slicers and pivot charts are also very nice ways to make a clean dashboard

[mperrah]

Paul, way to pull us out of the trees to see the forest.
I tend to follow the path of the OP in finding a solution, but in the case a Pivot table makes great sense.
We would still need to restructure some of the raw data, sorting vertically by date, then having the agent names across the top (current and ex-agents)
We could then show any date range and any agent or group based on the autofilter style drop downs.
I would hazard a guess that the op hasn't used Pivot tables before based on the amount of effort in formulas so far...

Under the menu ribbon on insert section chose Pivot table.
you will be asked what range/table to pull data from.
then you can start choosing and arranging what part of the data you want to display and what functions to preform (sum, avg, min, max etc)
You can make a new worksheet to test the scenarios out, but I agree with Paul this is a great solution and involves the lest amount of coding.
the only major task is restructuring our raw data, then we can rock this project.

[Paul_Hossler]

FWIW, I thought the worksheets and charts were very nice looking, but not what I would really call a dashboard.

The worksheets and charts in the attachment are very detailed but seem to lend themselves more to analysis and drill-down which is why I mentioned pivot tables



I have a copy of "Excel 2007 Dashboards & Reports For Dummies" and it's a nice read



A dashboard is a visual interface that provides at-a-glance views into key measures relevant to a particular objective or business process. Dashboards have three main attributes:
1. Dashboards are typically graphical in nature, providing visualizations that help focus attention on key trends, comparisons, and exceptions.

2. Dashboards often display only data that are relevant to the goal of the dashboard.

3. Because dashboards are designed with a specific purpose or goal, they inherently contain predefined conclusions that relieve the end user from performing his own analysis.

[worthm]

Gentlemen,
Thank you all for the input. It's a lot to consider. I have, to the best of my knowledge, corrected any differences in month names and agent names. While I do have some of mperrah's coding on my spreadsheet, it seems easier to use SamT's idea.

SamT,
I don't have any passwords on the dashboard. I have a password page but it is used once I dump all of the sheets to PDF. Are you getting a pssword screen or something? I am forced to dump to PDF because the employees don't have the full version of Excel. They have a viewer only and I don't want them seeing each other's pages so Im stuck using the PDF route. For the same reason I don't have any actively working bullet graphs, speedometers. etc. All they get is an image of each sheet.

I have uploaded my edited spreadsheet. The only issue is that I have two dashboards that are linked to each other and you may have problems getting it to work properly but I am grateful for any help

Also, yes its true that Ive never used pivot tables.

[SamT]

More makework. Well, it would be makework for me and I might not suit your vision. It has to be done and I would just and soon have somebody else do it for me.

Please read and understand the notes in the Notes sheet., I moved the busy data sheet to the front since the Notes refer to it often. When you have completely modified any blank month as required, (All formatting label formulas added columns, etc,) copy it and paste it down until you have 12, then just change the month label Formulas as needed.

[worthm]

Thanks for your help. Im marking this as resolved

[SamT]

You are welcome and I am very sorry we could not reach a satisfactory conclusion for you.

[worthm]

SamT, you made it pretty clear you didn't really want to help so I'm relieving you of the burden of having to deal with it.

[SamT]

SamT, you made it pretty clear you didn't really want to help so I'm relieving you of the burden of having to deal with it.

I am sorry that you got that impression.

I asked you to change the formats because they don't really effect the human usage of the book, but they make it possible to write one function that can access all your data tables. In order to write a function that will access a table with dates as column heads would require quite a bit of head scratching; And your would need two different functions.

Some of the suggestions I made, such as a cell named Current year were merely so that you would not have to change table titles every year. Just copy the book, change one cell to the new year, and all Titles would be updated for you.

Using CurrentMonth means that your formula would read like =SamT'sFunction(EmpCellAddress,CurrentMonth)

If you wanted a different month, then =SamT'sFunction(EmpCellAddress,"April")

[mperrah]

I have researched index(indirect(match))) combinations and have worked out another possibility.
I realize this is marked solved, but I had made a lot of progress so I went further.
On the "new" Rankings page, click in AO7 and choose the date you want to review,
all the cells in the new dashboard will update.
I copied some sheets and renamed them and reformatted data to match the formula better.
Some tables have added and named, not all are used though.
If you like this method I can explain it in more detail.
but the beauty is the formula can be dragged over cells and it still works,
it looks at the column header and row header for the sheet source and row and column calls,
so that's why I altered names slightly of the sheets and table naming schemes.

I found major inspiration from this link:
https://breakingintowallstreet.com/biws/advanced-formulas-in-excel-index-match-indirect/

13588

[SamT]

As long as you have a solution that works for you, we are all happy.:beerchug: