• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Quarter Reporting Determination

I pulled up your file but, I am not able to see the formula and where it's placed...


The helper column is listed on the field reports and as well on the detailed summary reports specific to department heads (I am going to hide the helper column at the detailed summary department head report level. The helper column data is orignally entered on the field reports-thus the point of linking these cell's to the summary detailed department heads report.


When the field reports are changed (this is where all information orignates and is altered as needed) this will change everything on up the reporting chain.


Field report data changes are linked to the summary detail and just filters through for dept heads so they can see in a quickly manor the changes that are made as long as they accept the update or refresh of the information on the report. As well once I have these reports finished I have yet another report that must be a consolidation of all detailed summary department head reports. This report will roll-up to the owner with a snap shot of each department and the owner can digress down to the department head and field report detailed level if need be by hyperlinks... It's quite and undertaking and I hope this is a sound approach since data bases were not an option at this time...


I am running into memory errors though, which just started happening at row 5,600... Apparenlty there's restriction of formulas... Any suggestions
 
Scratch my comment about not see the formulas in your posted example. I was really tired and didn't think to down load... thank you!
 
Sweet!


You don't need to link to the helper column.


It serves no purpose in the summaries.


It is only useful as an interim step in the initial calculation of the split Q1/Q2/Q3/Q4 amounts.


Will give you more thoughts next time, busy at the moment.

Could you upload your file or files again, so I can see if that resolves the issue I was having with being able toopen them? I can best give you advice if I can really see what you have.


Asa
 
I just updated the examples file (same link).


The only change was to the No/Qtrs column in the "Asa's Example #2" to increase the efficiency/speed a little.


That is the version of the formula that can handle situations where characters other than comma, space, and 1 2 3 4 were included in the Qtrs column (all other characters will be ignored)...


The effort to ignore extra characters may slow down calculations on the worksheet. It's valuable if you are concerned that other characters might end up there because the other formulas will result in miscalculated numbers for Q1/Q2/Q3/Q4 in those cases. If your worksheet does not stall during recalculation (i.e. when someone enters a new number in the qtrs column) then your worksheet is fast enough. If it does stall, you may value speed over the extra safety (and complexity) of this formula.


One alternative is to use a data validation rule for the Qtrs column that will warn users if they try to enter invalid characters. It does not, however, prevent them from getting the characters in there via copy/paste, nor does it automatically do anything about stray characters that were in there already before you instituted the data validation rule (although you can search for and correct them yourself).


Asa
 
Phew!

Updated examples again. Version 3 of examples has another update to "Asa's Formula #2".


You can ignore most of my previous comments.


I believe I have resolved any speed issues. This formula for No./Qtrs is shorter and simpler than my previous versions, and it should be about the same speed as the original by Jeremy. I don't believe there will be any performance hit to using this version.


My previous versions suffered from being "volatile" formulas that Excel would recalculate whether they needed it or not, and from doing more calculations to achieve their result than this new version does.
 
SUCCESS!!!! Thank You!


As well I am still having problems with securing (protecting) this mess of a report...


When I protect my sheet it won't let the groups roll-up or down. You know the group and ungroup feature in excel. If the spreadsheet is protected and locked these function do not see to work. Do you have...Any suggestions.
 
It let's me know to enable macros. I enable macros save and close and open again. Once I open it again I protect the sheet. I save the file again... Then I try to select the group drop down or rollup and it gives me and error message that this can't be done unless you unprotect the document....


This is what I pasted into the macro... Keep in mind I am just as clueless when It comes to macro's as I am with excel.


Private Sub Workbook_Open()


With Sheet1


.Protect Password:="Secret", UserInterfaceOnly:=True


.EnableOutlining = True


End With


What am I doing wrong... Because it's got to be me...
 
1/ In the Visual Basic Editor, make sure the above code is in the "ThisWorkbook" section. (Double-click "ThisWorkbook" in the project pane).

2/ Make sure the password listed in the code is the one you want.

3/ Change "Sheet1" in the code to the name of the worksheet you want to protect and allow grouping on.

4/ Make sure you have "End Sub" as the last line.

5/ Optionally password protect the macro so people can't view the password there:

Code: 
a.Right-click the VBAProject for your workbook in the Project Pane, and select "VBAProject Properties"

[code]b.Click the Protection Tab

[code]c.
Check "Lock project for viewing" and select a password (the same one for the worksheet might be a good idea). The reason is, that otherwise people will be able to view the password in the VB Editor

6/ Make sure your worksheet is UNPROTECTED.

7/ Save the workbook (make sure to save as .xlsm macro-enabled, .xlsb binary format, or .xls Excel 97-2003 format to preserve your macro). Close it. Reopen it. Save it again.

8/ It should now be protected. If the macro did it's thing. And you should now be able to Group/Ungroup.


~~~~

When you unprotect the worksheet in the future while working on it, be careful, when done, to always protect with the same password (or the macro will fail), or else just leave it unprotected, save, close, reopen (at which point the macro will reapply the protection if macros are enabled), and then save again.


~~~~

All the protection options will be Excel defaults, other than EnableOutlining, even if you protect it first with different options. If you want to allow/disallow other user actions, you need to specify all the options in the macro. For the available options, see http://msdn.microsoft.com/en-us/library/ff840611.aspx


For example, to allow filtering, change

.Protect Password:="Secret", UserInterfaceOnly:=True[/code]


to


.Protect Password:="Secret", UserInterfaceOnly:=True, AllowFiltering:=True[/code]


Asa
 
I am Sorry I am so consummed by my little project...12 hour days... Crazy!!!...Your guidance is greatly appreciated and I commend you on your patients and ability to provide me (newbe) guidance on such a foreign subject. Also your ability to over come my inadequate explaination/recap and quickly access my issues to provide an essential resolution. You ROCK! THANK YOU!!!
 
You must log in or register to reply here.
Back
Top