Monthly Report

[eyepods]

Hi All, I have been learning some excellent tricks and tips using excel from this forum (Thanks to all). I have a dashboard that captures and displays values (scores) against individuals into an array. This array is captured using a data_form and held in a 'single' Sheet (data) Unfortunatly I did not concidered how to record multiple sheets (Monthly values) for each individuals to buildup a true record over a the year. As I need to change individual vuales each month how can I create/save a monthly 'data sheet' and include an option to view each month into the dashboard...? At present I simply copy and rename the whole worksheet to indicate Month.... Not the best solution as this generates a number of files...

Any help appreciated. Mike

 

[chirayu]

sample data please. normally however you would only keep 1 data sheet & add an extra column at the end with e.g. month name so Jan, Feb, Mar. Then use that as part of criteria for main dashboard sheet e.g. using SUMIFS/COUNTIFS etc or pivot

 

[eyepods]

Hi Chirayu. Please find attached sample xls file. I have a single data tab which records individual scores from User_form but I need to included/record Monthly scores and view in dashboard. (i.e Jan, Feb, Mar, etc) No date is used in this version, let me know if you have any questions.

Mike

 

[chirayu]

See attached file. I have made quite a few changes in the file with regards to named ranges & formulas so you may need to edit your macro accordingly.

Here are the named range changes.

DrDataName - Removed
Nos - Removed
OffsetValue - Renamed to OffsetVal & changed range
Rnames - Removed
EndRow - Added to calculate End of Month Range from data sheet
StRow - Added to calculate Start of Month Range from data sheet
MaxRow - Added for the 1st table calculation in Dashboard
NumberRng - Added for the 1st table calculation in Dashboard
Names - Added for Resident Dependency Report
NamesRng - Added for Resident Dependency Report
SelMonth - Added for Month dropdown

Also changed all formulas in file. Anything highlighted yellow in Data, Calculation, Ratio sheets is used in formulas.