Hi all! I posted the other day to get assistance with a feature of this interactive Excel database I am programming as part of my internship. Its use will be data inputting and then reporting the data in a way that makes interpretations and reports them. This is my first Excel programming project and I am thoroughly enjoying learning features, however, I have reached the point of specificity whereby searching online is no longer working. I will separate each request to make it easier to reference each individually.
I have attached the workbook to this post. I will warn you it does have macros and these are for a multiple-option list feature in one of the sections, so don't think I'm trying to send harmful material. It is not necessary to enable them to look at this specific issue, so don't worry about that.
(1) Variable row feature
Essentially, all my code on the 'Group _ Details' uses SUM and COUNTIFS functions for the data on the 'Group _ Learner Details' between rows 4 and 197. This is not a massive issue, and 197 entries will never be made, however, it would be better if I could replace it with a feature that would adjust the range of observations as a new row of information is filled out.
Screen Shot 2018-08-02 at 11.44.01.png
I saw a very simplified version of this with an OFFSET feature, then using RAND(), but I couldn't quite figure it out (or maybe I did but I didn't test it correctly). How can I adjust it so the code looks for additional entries in the database if needed? If if can't detect new row entry, I could lock it specifically to adding a 'Stage' (ie new entries in Column B)?
And, is there anyway to bulk edit and replace my data (like a find and replace feature) to replaces all the $D$4:$D$197s in all my formulae with this variable code, without doing it painstakingly one by one?
(2) Set three worksheets as templates
Now, I have three sheets I would like to group, namely 'Group _ Details', 'Simple Group _ Details' and 'Group _ Data Report', and set as templates such that one can add a new one for each new teaching group that comes in. How would I go about doing that?
Is there, then, any way that I can have the '_' replaced with a number that increases by 1 every time a new template (of the three sheets) is added?
Similarly with the previous problem, since these three sheets interact, is there a way for the code to automatically change the sheet reference in the code for the new template?E.g. 'Group 26 Details', 'Simple Group 26 Details' and 'Group 26 Data Report'
*New Template*
'Group 27 Details', 'Simple Group 27 Details' and 'Group 27 Data Report'
(3) A combination of the above two problems in an annual report
Additionally, I have two more sheets for annualised data: 'Annual Summary' and 'Funders Data Report'. I would like these pages to collect data from all the individual 'Group _ Details' sheets, which have a variable number (could be 5, could be 25). Is there a way to add a new row for each new template added in the 'Funders Data Report', collecting the corresponding data from the 'Group _ Data Report'?
Then, for the 'Annual Summary', is there a way for it to duplicate the (already extremely long) COUNTIFS formulae I have in the By Demographics section (already one full A4 page of code per cell, imagine that times 25 for one singular cell) so that it collects the data on the whole for the year?
Since I am a relatively intermediate user of Excel, I am not sure whether these requests are realistic and/or workable, however they seem logically possible. I'd prefer the most concise method that doesn't require me to edit all my existing code bit by bit, but if it needs doing to get the result, I'll commit the hours. Please let me know if you need any more information on any aspect of this as it is a pretty specific request.
Any and all help will be greatly appreciated. Thanks in advance.
Bookmarks