+ Reply to Thread
Results 1 to 106 of 106

Template, Cross-Sheet Referencing and OFFSET Function Assistance

  1. #1
    Forum Contributor
    Join Date
    07-31-2018
    Location
    London, England
    MS-Off Ver
    MS Office 365
    Posts
    104

    Template, Cross-Sheet Referencing and OFFSET Function Assistance

    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?

    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'
    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?

    (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.
    Attached Files Attached Files

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    The function RAND() is to generate random numbers< my guess is that that is not what you're looking for.
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Forum Contributor
    Join Date
    07-31-2018
    Location
    London, England
    MS-Off Ver
    MS Office 365
    Posts
    104

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    Quote Originally Posted by Keebellah View Post
    The function RAND() is to generate random numbers< my guess is that that is not what you're looking for.
    Yeah I knew that and that's mainly why I didn't understand how that OFFSET interaction works.

    Any suggestions for the other problems?

  4. #4
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    It has nothing to do with the Offset function.
    To determine that last filled row you had to use the Index function to determine the last filled row, I do this (when I use something similar) a separate cell where the result is calculated and refer to this cell using INDIRECT to use it in a formula

  5. #5
    Forum Contributor
    Join Date
    07-31-2018
    Location
    London, England
    MS-Off Ver
    MS Office 365
    Posts
    104

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    /Bump Still having trouble with no solution.

  6. #6
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    Your formula is dynamic, the rest, I don't really get what you need but this for starters
    The dynamic formula will allow for a max of 10000 rows.
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    07-31-2018
    Location
    London, England
    MS-Off Ver
    MS Office 365
    Posts
    104

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    Quote Originally Posted by Keebellah View Post
    this for starters
    The dynamic formula will allow for a max of 10000 rows.
    I honestly do not see any changes made. Where have you made them?

    Quote Originally Posted by Keebellah View Post
    the rest, I don't really get what you need
    So these three sheets 'Group _ Details', 'Simple Group _ Details' and 'Group _ Data Report' I want to save as a template.

    Then, possibly using macros, have a button that will duplicate all three sheets again as many times as needed, each time replacing the '_' in the titles with a number that is x+1 (1 higher than the previous). For example:
    'Group 25 Details', 'Simple Group 25 Details' and 'Group 25 Data Report'
    *Press button*
    'Group 26 Details', 'Simple Group 26 Details' and 'Group 26 Data Report'
    *Press button*
    'Group 27 Details', 'Simple Group 27 Details' and 'Group 27 Data Report'
    After which, I want the 'Funders Data Report' sheet to detect the addition of a new set of those three sheets, and import the data in a new row. The 'Annual Summary' sheet will also detect the addition of a new set of the three sheets, and include them in its calculations.

    Is that clearer for you? Please let me know, thanks.
    Last edited by doubleuson; 08-03-2018 at 10:00 AM.

  8. #8
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    The dynamic formulas are in the 'Funders Data Report' sheet.
    Like I said I did nothing else just also got rid of the DIV0

  9. #9
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    And what are you going to name the sheets ?

  10. #10
    Forum Contributor
    Join Date
    07-31-2018
    Location
    London, England
    MS-Off Ver
    MS Office 365
    Posts
    104

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    Quote Originally Posted by Keebellah View Post
    The dynamic formulas are in the 'Funders Data Report' sheet.
    Like I said I did nothing else just also got rid of the DIV0
    Ah I see it now, is there a way to bulk change formulae in Excel instead of editing each one by one?

    This, for the Funder's Report is not what I wanted. What I meant was this would detect the addition of a whole new sheet rather than the dynamic range.

    Quote Originally Posted by Keebellah View Post
    And what are you going to name the sheets ?
    They are going to have the same name, just with a number one higher than the previous in the place of the underscore

  11. #11
    Forum Contributor
    Join Date
    07-31-2018
    Location
    London, England
    MS-Off Ver
    MS Office 365
    Posts
    104

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    Quote Originally Posted by Keebellah View Post
    Your formula is dynamic, the rest, I don't really get what you need but this for starters
    The dynamic formula will allow for a max of 10000 rows.
    So I started replacing formulae with OFFSET & COUNTA style formulae, however, they are coming up as #VALUE. An example of a formula is here:

    Screen Shot 2018-08-03 at 16.11.25.png

    Is this because the OFFSET function is volatile or because there is an error in the formula that I cannot detect?

  12. #12
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    Error in the formula.
    Seeing the complicated construction I think you'll be better off with a macro doing all that

  13. #13
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    Could you attach a sample with a worksheet added and the name given?

  14. #14
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    I took a look at the group data report and the long formulas are the ones that are going to make you work to change it all.
    I've attached the file again with my changes so far. The table where the totals are shown, I modified those formulas too, you also duplicated the sum in the second column not necessary, I corrected that too.

    In the top rows I used the header info to be used in the Countifs, in that way if you decide to change a filed name all you do is modify the header and it works too without having to edit all the formulas.

    I still do not see where the OFFSET is you mention all the time.

    Another question, why to you use TRANSPOSE to test if a cell is empty or not?

    I did if with Find and Replace but you will have to use you imagination there to make it all work since you're referring to a series of columns and than again, the formula needs a test to see if the divider is not 0 to calculate and avoid the DIV0 errors

    The portion of the formula that you posted is incomplete thus the VALUE error.

    I don't know right now how to approach this for you and I still don't understand what you mean by 'template' sheets and what the new names are to be, well, the whole thing is hazy to me.
    You're the one with the knowledge of how you want it to be but don't expect an outsider to understand the whole thing in one go.

    With the way the formulas are built you might even run into a maximum of characters allowed per formula and that will botch the whole thing.


    It's bedtime her.

    Good night
    Attached Files Attached Files

  15. #15
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    I woke up with an idea and tried it out.
    To simplify the formulas I added Named ranges for the columns.
    Take a look and see if this helps you on.
    You will have to get rid of the pesky DIV0 but I'm sure you can do that.
    Attached Files Attached Files

  16. #16
    Forum Contributor
    Join Date
    07-31-2018
    Location
    London, England
    MS-Off Ver
    MS Office 365
    Posts
    104

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    Thanks for the help. Sorry, I work Monday to Friday 9-5 and this was on my work computer so I didn't see it until now.

    I didn't attach the OFFSET file because it is not working correctly. I haven't implemented OFFSET I was asking whether I should.

    And let me explain the templating thing. There will be multiple groups over the course of the year and this database is for one entire year. Additionally, there are not a set number of groups that will be added. Thus, I want to make three sheets 'Group _ Details', 'Simple Group _ Details' and 'Group _ Data Report' into preset templates that one can press a single macro button to generate a new set for another group to be inputted.
    Last edited by AliGW; 08-06-2018 at 05:58 AM. Reason: Unnecessary quotation removed.

  17. #17
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    That's okay but what will be the name of the 'new groups' ?
    What will it look like, the formulas are okay but the macro will then correct the formulas for each new group

  18. #18
    Forum Contributor
    Join Date
    07-31-2018
    Location
    London, England
    MS-Off Ver
    MS Office 365
    Posts
    104

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    Quote Originally Posted by Keebellah View Post
    That's okay but what will be the name of the 'new groups' ?
    I'd like to implement a feature that allows the user to input a number which will find all the '_' in the titles and formulae, and replace them with the number the user inputted. Have you any idea how to go about doing that?

  19. #19
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    If you use a Table on the Group_Details sheet, the range will automatically expand with it.

    Also, you can simplify your formulas on say the GroupDataReport sheet by having the values in column C match those in column R on the Details sheet. Then for example, you could replace this:

    =COUNTIFS('Group _ Details'!$B$4:$B$197, "=Achiever", 'Group _ Details'!$R$4:$R$197, "=Mixed/Car")+COUNTIFS('Group _ Details'!$B$4:$B$197, "=Units", 'Group _ Details'!$R$4:$R$197, "=Mixed/Car")+COUNTIFS('Group _ Details'!$B$4:$B$197, "=Fail", 'Group _ Details'!$R$4:$R$197, "=Mixed/Car")+COUNTIFS('Group _ Details'!$B$4:$B$197, "=Learner W3", 'Group _ Details'!$R$4:$R$197, "=Mixed/Car")+COUNTIFS('Group _ Details'!$B$4:$B$197, "=Learner W2", 'Group _ Details'!$R$4:$R$197, "=Mixed/Car")+COUNTIFS('Group _ Details'!$B$4:$B$197, "=Learner W1", 'Group _ Details'!$R$4:$R$197, "=Mixed/Car")

    with this:

    =SUM(COUNTIFS('Group _ Details'!$B$4:$B$197, {"Achiever","Units","Fail","Learner W3","Learner W2","Learner W1"}, 'Group _ Details'!$R$4:$R$197, C26))

    or even:

    =SUM(COUNTIFS('Group _ Details'!$B$4:$B$197, {"Achiever","Units","Fail","Learner*"}, 'Group _ Details'!$R$4:$R$197, C26))

  20. #20
    Forum Contributor
    Join Date
    07-31-2018
    Location
    London, England
    MS-Off Ver
    MS Office 365
    Posts
    104

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    I have attached the most recent version of the database with this post, including the changes for #DIV/0! errors.

    I now want to use a combination of the COUNTIFS and MIN/MEDIAN/MAX/AVERAGE/MODE functions to determine the lowest/median/highest/mean/modal age of participants who are "Achievers", and also participants who are learners (ie all Achievers plus all Fails, plus all Units, plus all Learner W1/2/3s). How would I best approach that?

    Thanks for all the help by the way, this is doing wonders.

  21. #21
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,365

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    This seems to be a fresh query, and as such requires a new thread with an appropriate title. By all means, provide a link back here if you think the background relevant. Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  22. #22
    Forum Contributor
    Join Date
    07-31-2018
    Location
    London, England
    MS-Off Ver
    MS Office 365
    Posts
    104

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    Quote Originally Posted by AliGW View Post
    This seems to be a fresh query, and as such requires a new thread with an appropriate title. By all means, provide a link back here if you think the background relevant. Thanks.
    I mean, my query in first case of this thread was quite broad, and I already have the attention of somebody who a) understands my database and my idea more than most and b) has the knowhow to alleviate my issue and c) I am still in the process of rectifying the original issue, so is it entirely necessary to do so?

    I can; but surely that is somewhat counter-intuitive?

  23. #23
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    This new file you uploaded throws overboard my idea of named ranges.
    Is the file as it is working as it should?
    So only the macro for the new groups? I think this is going to be more than you're begging for as goes to programming and maintenance time.

  24. #24
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,365

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    Do as you wish. I have been a member here for five years and a moderator for around two: I have a fairly good idea of what is the best policy for posting on these forums. If I advise you to do something, it is based on my five years' experience of participating and moderating here. Furthermore, the person helping you is also a seasoned member and will not abandon you beacuse you have (sensibly) started a new thread for the new query, whilst continuing to solve the original query here. But it's entirely up to you whether or not you act upon this advice.

  25. #25
    Forum Contributor
    Join Date
    07-31-2018
    Location
    London, England
    MS-Off Ver
    MS Office 365
    Posts
    104

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    Quote Originally Posted by Keebellah View Post
    This new file you uploaded throws overboard my idea of named ranges.
    Is the file as it is working as it should?
    So only the macro for the new groups? I think this is going to be more than you're begging for as goes to programming and maintenance time.
    Named ranges would work but apparently if it is formatted as a table, the range becomes dynamic regardless. Also, I didn't know how to replicate the named ranges in the updated version I had.

    The file is working, I am currently simplifying the COUNTIFS formulae for the Data Report section. Going forward I need to figure out the new function things, which I shall post in a new thread.

    I didn't truly realise how much work a feature I logically deemed pretty simple. I have minimal idea about VBA coding so I can imagine I am asking a lot with regards to a 'template and new set of template for each group' macro button. Do you think you would be capable of doing so or at least prodding me in the right direction?

    Have to build this for work and my deadline is the end of this week, so I'm not sure how long it might take.

    EDIT: I attached the most recent copy of the file to this post, in case you want to make any changes so I don't have to import them from another source. Genuinely appreciate the help loads.
    Last edited by doubleuson; 08-06-2018 at 11:35 AM. Reason: File Attachment

  26. #26
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    If you've got a deadline, well, good luck.
    Yes it is a table which in many ways makes it easier to work with and you could even simplify the formulas, but .... that's too much for your deadline.
    If you can tell me exactly what you want to rename write it down, one formula with what is is and what you want.
    I'm off tomorrow for two days but will take a look, no guaratee.

  27. #27
    Forum Contributor
    Join Date
    07-31-2018
    Location
    London, England
    MS-Off Ver
    MS Office 365
    Posts
    104

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    Quote Originally Posted by Keebellah View Post
    If you've got a deadline, well, good luck.
    Yes it is a table which in many ways makes it easier to work with and you could even simplify the formulas, but .... that's too much for your deadline.
    If you can tell me exactly what you want to rename write it down, one formula with what is is and what you want.
    I'm off tomorrow for two days but will take a look, no guaratee.
    I just want to create a macro button to create the set of three sheets for a new group, then change all the '_' in the three sheets to a number, which preferably can be inputted then and there.

    From there, I would like the Funders Data Report to automatically extract the information from the 'Group _ Data Report' for that group and enter it in as a new row beneath the last.

    Finally, I would like to create an annual version of the 'Group _ Data Report' which has a dynamic range and can auto-extract from the individual reports.

    I appreciate this is a lot to ask, but I hope you can help me else I may be absolutely screwed.

  28. #28
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,365

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    We do have a Commercial Services section where you can buy points in order to 'pay' a member to take on the whole project for you, however, this would not guarantee your getting it done any faster and it would rely on someone being prepared to do it all. It would also mean closing your threads in the free section in favour of the paid section which is manned only by senior members of the forum.

    If this need of yours is really urgent, then perhaps you should consider seeking help from an agency who offer paid services rather than from a forum manned by volunteers in their spare time giving their help for free. In all honesty, if this is work-related and you have deadlines to meet, you shouldn't be relying on free help. This advice is not meant to be obstructive, rather constructive and realistic. I hope it will be taken as such.

  29. #29
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    I agree with @AliGW but here's something to help you on the way

    Two macros and one Function
    The first macro is to enter the old_Value and the new_Value
    The function is invoked to see if the old_Value exists if not you get a message telling you so.
    The Find_And_Replace_Formulas macro will use the two parateres to find the old_Value and replace it with the new_Value

    So if you want to change Group _ Details to Group_2_Details it will do so in all the occurrences found

    Please Login or Register  to view this content.

  30. #30
    Forum Contributor
    Join Date
    07-31-2018
    Location
    London, England
    MS-Off Ver
    MS Office 365
    Posts
    104

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    Quote Originally Posted by AliGW View Post
    We do have a Commercial Services section where you can buy points in order to 'pay' a member to take on the whole project for you, however, this would not guarantee your getting it done any faster and it would rely on someone being prepared to do it all. It would also mean closing your threads in the free section in favour of the paid section which is manned only by senior members of the forum.

    If this need of yours is really urgent, then perhaps you should consider seeking help from an agency who offer paid services rather than from a forum manned by volunteers in their spare time giving their help for free. In all honesty, if this is work-related and you have deadlines to meet, you shouldn't be relying on free help. This advice is not meant to be obstructive, rather constructive and realistic. I hope it will be taken as such.
    I agree. I probably should've done so. But I'd rather learn things about the process to expand my knowledge going forward. If Keebellah wants monetary compensation for his/her work, I can arrange something.

  31. #31
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,365

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    No, you can't, I am afraid. The only way you can pay is by going through Commercial Services. If Keebellah were to accept payment for work done in the free forum, he'd be in breach of our rules, as would you for offering payment here.

    As a forum expert, Keebellah should have access to the Commercial Services section: what you could do is ask if he'd be prepared to take on the entire project and move the conversation there, and agree on the points he'd want to do it. If you two have that conversation here and decide to do that, then I can facilitate the closing of your threads here in favour of that in the CS section.

    I do hope this is clear.

  32. #32
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    Hi AliGW: I is interesting but seen the deadline I cannot guarantee that I could solve it.
    I can also not guarantee that I will be able to dedicate myself fully on this project.
    Talking as a professional I would first need to know exactly what the end result is that the OP requires and have a basic schematic view.
    The request is (very) tempting though.
    I will try and offer extra assistance with this post and the other one as soon as I am at my system again.
    (Sent from my IPhone)

  33. #33
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,365

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    As I have explained, the minute any financial compensation comes into play, you have to take it to Commercial Services and do it properly (with the giving and receiving of points).

    Things are coming along here: https://www.excelforum.com/excel-for...le-sheets.html

  34. #34
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    @doubleuson
    Now that I know it's a table you can change your formulas to refer to the table and you can forget about the row used.
    I modified (I think all the formulas) so take a look a go on from there.
    This evening I'll try and see if I can help out (non-commercial) and no promises

  35. #35
    Forum Contributor
    Join Date
    07-31-2018
    Location
    London, England
    MS-Off Ver
    MS Office 365
    Posts
    104

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    The end result I would hope for from this would be a Landing Page which has a series of buttons: one to 'Funders Data Report' and another to 'Annual Summary' at the top.

    Beneath, there would be a button to create a new group, which you can input a number for the group (for example, Group 25). By *create a new group*, I mean creating replicas of the 'Group _ Details', 'Simple Group _ Details' and 'Group _ Data Report' sheets (as 'Group 25 Details', 'Simple Group 25 Details' and 'Group 25 Data Report') which contain all the current formatting, data validation and formulae in the templates, but with adjustments such that they feed off eachother (all the Group 25 sheets interact solely with eachother and not with any other Group number). Essentially, the most important part is for the macro to change the formulae in all of them "find and replace" style. This would be so that the user can input information for each group as appropriate, and the Simple Details sheet will transpose the information into a simplified sheet that can be printed, while the Data Report sheet will display the information in the required format. Preferably, the newly generated 'Group _ Data Report' will be locked, aside from cell G9 (which requires manual input).

    Upon the creation of the new set of sheets for the group, I want the 'Funders Data Report' to create a new row and import the data directly from the newly created Data Report (for example, a new row entitled "Group 25", importing the data from 'Group 25 Data Report' cells C5:I5 into 'Funders Data Report'!B:I, 'Group 25 Data Report'!C9:H9 into 'Funders Data Report'!J:O, 'Group 25 Data Report'C13:I13 into 'Funders Data Report'P:V (bearing in mind G13 and H13 are the opposite way round to T and U in the 'Funders Report') 'Group 25 Data Report'!C17:I17 into 'Funders Data Report'!W:AC (same issue with reversed titles)).

    Then, the 'Annual Summary' sheet will detect the addition of a new group and perform the identical calculations as the individual 'Group _ Data Report' sheets, but with the data on all of the 'Group _ Details' sheets.

    Finally, these new sheets for the group will create hyperlink buttons on the landing page to each of the sheets for easy navigation.

  36. #36
    Forum Contributor
    Join Date
    07-31-2018
    Location
    London, England
    MS-Off Ver
    MS Office 365
    Posts
    104

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    I see what you've done there with the formulae, so I shall attach the most up to date version of the file here before you make any further edits to an incorrect file.

    I will import the formulae on my version.
    Attached Files Attached Files

  37. #37
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    Do you want all this in one file?
    What I mean to say is if the Funder's Report worksheet shows all the available groups could have all the group XXX sheets in one file, it will mean that the file grows as the time passes.
    Let me know this too, please.

  38. #38
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    You do not need to import the formula, just Find and replace the formulas with the correct references

  39. #39
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,365

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    It occurs to me, as you are talking about the file growing over time, that now might be an appropriate moment to start backing up this file AND creating incremental copies of it as you add functionality. Workbooks can, and often do, become corrupt, so you need to have your insurance policy in place before that happens.

  40. #40
    Forum Contributor
    Join Date
    07-31-2018
    Location
    London, England
    MS-Off Ver
    MS Office 365
    Posts
    104

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    Quote Originally Posted by Keebellah View Post
    Do you want all this in one file?
    Yes, the aim is to keep all the functionality centralised. The landing page/menu is what will make the navigation easier. Because I want all the interactions, it could work across workbooks but it would require all the workbooks to be kept in the same place.

    The main file will be held in Dropbox.

    Quote Originally Posted by AliGW View Post
    It occurs to me, as you are talking about the file growing over time, that now might be an appropriate moment to start backing up this file AND creating incremental copies of it as you add functionality. Workbooks can, and often do, become corrupt, so you need to have your insurance policy in place before that happens.
    Yeah now I have completed the 'safe' part of the workbook (pre-macros), I have a saved version of the file and will save them by stages, in case of corruption. Thank you for the heads up.

    Quote Originally Posted by Keebellah View Post
    You do not need to import the formula, just Find and replace the formulas with the correct references
    Can this be done automatically within the macro, rather than manually? I am trying to make it as user-friendly as possible on the face of it as it will be operated by someone else and not me.
    Last edited by doubleuson; 08-08-2018 at 07:56 AM.

  41. #41
    Forum Contributor
    Join Date
    07-31-2018
    Location
    London, England
    MS-Off Ver
    MS Office 365
    Posts
    104

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    I should mention, also, that the 'Group _ Details' sheet contains a large amount of Data Validation lists that correspond to the 'Data Validation' sheet, that I would like to stay when the new sheets are created.

    There is also a macro for multiple, non-repetitive listing in the DATable1[IMPAIRMENT CATEGORY] section, which hopefully can be replicated also.

  42. #42
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    And don’t forget Murphy, drops along unexpectedly

  43. #43
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    Things got a little messed here and it's late now, so I'll start on it tomorrow.
    I understand the validation lists and all and I saw the macro too.
    The new sheet's names will only permit to enter a numeric value, correct?
    What's the starting number?
    Questions will come up as I go.

  44. #44
    Forum Contributor
    Join Date
    07-31-2018
    Location
    London, England
    MS-Off Ver
    MS Office 365
    Posts
    104

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    It's okay man whenever you get a chance. Yeah numeric inputs. But there is no set starting number since there are multiple courses running for which this will be used.

  45. #45
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    Okay, clear (for now )

  46. #46
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    Just to point out some issues, your formulas in the Reports are inconsistent.
    I will be editing them but I've attached the modifications so far for your own interest and to take a look at what's the real issue.
    You repeat series constantly and have for division by 0 checks, in my honest opinion that is sloppy formula.
    I'm sure others will do it in another ways but this is the way I do it.
    I renamed the worksheets and the table to 0 since these will have the function of template (like you originally asked) but they first need 'cleaning up' and be made consistent before taking the next step.
    If you check the Add Row macro which I modified, this the way to do it when it regard Excel tables, I have not added the increment row number but don't know why you want it.
    Will be added later on.
    This is as it is now, will work on it on and off

  47. #47
    Forum Contributor
    Join Date
    07-31-2018
    Location
    London, England
    MS-Off Ver
    MS Office 365
    Posts
    104

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    Yeah, I had to revise my formulae on the Data Report page several times painstakingly as I found more efficient ways to do so. The IF testing for #DIV/0! is because there is no guarantee that a every category will be covered and hence '0%' is more effective than '#DIV/0!' which could be misinterpreted by the wrong person as an error.

    Again, this was my first 'Excel from scratch' project, so I am more than happy to rework things to find the optimal way to do them.

    Screen Shot 2018-08-09 at 09.50.57.png

    I did get prompted with this warning upon opening, though, I assume this is because of the work in progress nature of the workbook.

    Thank you for your help so far.
    Last edited by doubleuson; 08-09-2018 at 04:58 AM.

  48. #48
    Forum Contributor
    Join Date
    07-31-2018
    Location
    London, England
    MS-Off Ver
    MS Office 365
    Posts
    104

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    Also, with the new 'Add Row' macro, if I use it to create more than one row, the spreadsheet crashes and I get an error report as such:

    Screen Shot 2018-08-09 at 10.00.03.png

    What is the likely cause of that?

    Thanks again.
    Last edited by doubleuson; 08-09-2018 at 05:29 AM.

  49. #49
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    First the DIV 0
    you can change the formula check from

    Please Login or Register  to view this content.
    to

    Please Login or Register  to view this content.
    The value will be blank instead of 0 percent
    Why the file crashes/ I don't know will test later.

    The opening error is because I have set the formulas to be checked and this is the result of inconsistent formulae
    You can use Formula Error to see which are the problematic ones, many

  50. #50
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    Quote Originally Posted by doubleuson View Post
    Also, with the new 'Add Row' macro, if I use it to create more than one row, the spreadsheet crashes and I get an error report as such:

    Attachment 585519

    What is the likely cause of that?

    Thanks again.
    The attachment is invalid

  51. #51
    Forum Contributor
    Join Date
    07-31-2018
    Location
    London, England
    MS-Off Ver
    MS Office 365
    Posts
    104

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    But, technically for the 'Learner %', if 0 'Learners' are, say, 'White/Brits', then the 'White/Brits' make up 0% of the learner population for that group. As such, a blank would maybe be useful for achievement rates or progression rates, but for % of learners, wouldn't '0' be the better option?

    Just wondering in general.

    Thanks again.

  52. #52
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,365

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    Your attachment didn't work. Try again, please.

  53. #53
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    Quote Originally Posted by doubleuson View Post
    But, technically for the 'Learner %', if 0 'Learners' are, say, 'White/Brits', then the 'White/Brits' make up 0% of the learner population for that group. As such, a blank would maybe be useful for achievement rates or progression rates, but for % of learners, wouldn't '0' be the better option?

    Just wondering in general.

    Thanks again.
    You can always modify, let's get it working first. The AddRow is indeed causing issues now I think it's because of the insonsitent formulae that are trying to update and do not work anymore.
    Back to the drawing board

  54. #54
    Forum Contributor
    Join Date
    07-31-2018
    Location
    London, England
    MS-Off Ver
    MS Office 365
    Posts
    104

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    Of course, I will retrospectively tweak as appropriate.

  55. #55
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,365

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    Quote Originally Posted by doubleuson View Post
    But, technically for the 'Learner %', if 0 'Learners' are, say, 'White/Brits', then the 'White/Brits' make up 0% of the learner population for that group. As such, a blank would maybe be useful for achievement rates or progression rates, but for % of learners, wouldn't '0' be the better option?
    It depends whether or not you want or even need 0% values in your summary data. In other words, if you want to know the ethnic make-up of a group of learners, are you interested in what makes up that group AND what doesn't, or are you only interested in the specific ethnic breakdown? As an example, a My Heritage breakdown analysis of my DNA defaults to just those ethnic elements that make it up, although I can expand the view to see all possible ethnicities that are tested for.

    It's the information that YOU (or rather, your BOSS ) want(s) to glean from the data that should drive the way you construct your spreadsheet.

  56. #56
    Forum Contributor
    Join Date
    07-31-2018
    Location
    London, England
    MS-Off Ver
    MS Office 365
    Posts
    104

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    This is true; it is pretty obvious that if a number of learners of a particular ethnicity =0, they will represent 0% of the learners. This is not even slightly an issue in all honesty. Just convenient for reporting standards.

  57. #57
    Forum Contributor
    Join Date
    07-31-2018
    Location
    London, England
    MS-Off Ver
    MS Office 365
    Posts
    104

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    Also, I forgot to mention in the ideal view I have, but can the inputted number for each group be Transposed into cell 'Group_XXX_Details'!G1, or, even, have 'Group_XXX_Details'!G1 as the cell for inputting the number to assign to those three sheets?

  58. #58
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    Can you explain what the code in the Group_Details is for? Why column 20 ?
    I don't get the reason for this code at least no in column 20 I would understand it if it where to check on duplicate entries in Columns C and D together
    The G1 I understand.

  59. #59
    Forum Contributor
    Join Date
    07-31-2018
    Location
    London, England
    MS-Off Ver
    MS Office 365
    Posts
    104

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    The check in Column 20 is so that multiple entries can be made from the Data Validation list for Impairment Types, since more than one can apply to an individual. It also prevents double listing from occurring (ie you can only put each entry once, but you can put as many entries as you like).

    The validation also works that, in column 19, if 'Yes' is checked, a full list of impairments is available, whereas, if 'No' is checked, the only option in 20 is 'None'.

    Whereas, for the remainder of cells, I want the standard "one selection" list for data validation.
    Last edited by doubleuson; 08-09-2018 at 09:09 AM.

  60. #60
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    Okay, clear.
    I corrected all the formulas but now have to see why the @#$&88 the file hangs when adding or removing a row

  61. #61
    Forum Contributor
    Join Date
    07-31-2018
    Location
    London, England
    MS-Off Ver
    MS Office 365
    Posts
    104

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    I see what you mean by the clarity with the formulae in Group_Data Report. Thanks for that.

  62. #62
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    Slowly building it up, not much action yet but this is the beginning

    Bed-time now

  63. #63
    Forum Contributor
    Join Date
    07-31-2018
    Location
    London, England
    MS-Off Ver
    MS Office 365
    Posts
    104
    I’d be screwed without you so I appreciate all your help.

    If it helps, it’s my birthday on Saturday? ♥️

  64. #64
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,365

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    A year older, a year wiser?

    Hang on in there!

  65. #65
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    I'm not very good a singing but HAPPY BIRTHDAY TO YOU, HAPPY BIRTHDAY TO YOU, HAPPY BIRTHDAY TO YOU, HAPPY BIRTHDAY HAPPY BIRTHDAY HERE'S A PRESENT FOR YOU

    I did my best for the basics. I would normally build in more fail safes and so but well, your deadline, you know... The only worksheet that will require a VBA module to be populated/updated correctly is the Annual Summary since it is going to accumulate all the data from the different Group Data Reports.
    There's a PDF file attached which explains the working and what I tested so far it seems to work.
    The three worksheets with group number 0 must be left as they are since these are the template worksheets, they're hidden.
    I think I covered all the formulas as goes for DIV0
    The ADD and DELETE row buttons work but I am not sure if the system hangs, did not test it any further.
    The Table starts with the 29 rows so you have 29 rows of data per group.

    I hope your boss likes you and have a Happy Birthday tomorrow
    Attached Images Attached Images
    Attached Files Attached Files

  66. #66
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    Minor cosmetic corrections and upped one version number
    Working on the Annual Report.
    Idea: If you are going to work with this I might think that you will have one file for every year.
    Suggestion: save the file as the same name with the PREFIX yyyy - and the filename
    If you use this then the first 4 characters can be used to show which year it covers.

    Hoping you're enjoying your Birthday
    Attached Files Attached Files

  67. #67
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,365

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    Happy birthday!!!

  68. #68
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    Hope you had a fine Birthday.
    Still working on some of the macro's, in the meantime the latest update
    This is the idea so that you work only with a file for the year and keep the template as template file
    Attached Files Attached Files

  69. #69
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    You favorite stalker with the last updated file. The version number is the same except the build number is 180812
    The macro to update the Annual Summary is written, I also added a macro to clear the file of all groups and added data to start a new, it's not under any button but you can find it in the vba module Groupsmodule04, the macro's name is appropriately clear_All_Input_Data
    Hope you will have a nice day showing this Monday to your boss

    Enjoy
    Attached Files Attached Files

  70. #70
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    The last time you were here was monday shorly after 12 o'clock.
    Wonder if you were able to do something with the file.
    I haven't uploaded the last working version where the add and delete rows macro work including some other modifications.
    Hope to hear something so I know the work was not for nothing.

  71. #71
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    I guess there was no appreciation for the result.
    The final version has gone into the archives. Hope you still hold the job and have received the compliments you 'earned'
    I hope it's not due to lack of appreciation, but a short message telling us how it was accepted is the least we would have expected.

  72. #72
    Forum Contributor
    Join Date
    07-31-2018
    Location
    London, England
    MS-Off Ver
    MS Office 365
    Posts
    104
    Hi Keebellah. Sorry I haven’t been online in ages, and it almost seems as though I haven’t been appreciating your work. Unfortunately, a close family member passed the weekend before last and I had to make an emergency trip to Russia, where I had no access to a computer and very minimal internet access altogether.

    I have not, as of yet, seen your progress but I can thank you very much for all your time and hard work. I will review it as soon as possible and let you know.

  73. #73
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    Hi, Good to hear from you, my sincere regrets and condolences.
    I hope you're doing great and forget my previous remarks, but I hope you understood my curiosity and fact I didn't hear anything (anymore)

    This is a version that seems to work as it should (I hope) and let's keep Murphy away
    All the best and well. keep in touch
    Happy coding.
    Attached Files Attached Files

  74. #74
    Forum Contributor
    Join Date
    07-31-2018
    Location
    London, England
    MS-Off Ver
    MS Office 365
    Posts
    104

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    Having looked at the V1.2, I think it is amazing! It seems like it has all the functionality I could've wanted. I will test it either tomorrow or the day after when I get to my home PC so I can see if it copes with bulk data entries.

    Is it possible to remove the group set cap (which is currently 25), as I believe the first group of the 2018/19 season will be 25 and then increasing from there?

    I will tell my boss, upon my return on Monday, that the complex aspects were written by yourself. By all means credit yourself somewhere.

    Quote Originally Posted by Keebellah View Post
    I hope you understood my curiosity and fact I didn't hear anything (anymore)
    Of course, I completely understand. It was unexpected and I did try to log in to check while on my phone but I could barely load the Google homepage, let alone this forum.

    Thanks for the help.
    Last edited by doubleuson; 08-23-2018 at 08:25 AM. Reason: Reply

  75. #75
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    Well, you can just set the value to something you need the mentioned worksheet where it now says 25
    I suggest you keep some control over the number of groups, even if Excel allows a large number of worksheets you must run into the issue that the file becomes too large an thus slow to open or process.

  76. #76
    Forum Contributor
    Join Date
    07-31-2018
    Location
    London, England
    MS-Off Ver
    MS Office 365
    Posts
    104

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    I will change that as appropriate, thanks.

    Just minor issues, but, why does cell B6 have no Data Validation when the template is replicated? Also, why does the first name box automatically input as =DATableXXX when a new row is added? And, finally, would it be possible to allow the number in Column A to increase and decrease as rows are added and subtracted? Just so that whoever looks at the Details page can see the immediate number of inputs.

    THanks again for all your hard work.

  77. #77
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    I modified the number of groups, it's now set to 150
    I checked here (the same version) but the validation in B6 remains when replicating the template (using the built-in macros (as instructed in the pdf file)
    The numbering you mention is updated too, as long as you do that using the two buttons on that sheet, you add or delete the rows in the worksheet with the table (with the two buttons you have already put there yourself) so my guess is that you're not using the last file I sent you.
    Here is is again.
    I also attached the 2018 file created with the template file
    Attached Files Attached Files

  78. #78
    Forum Contributor
    Join Date
    07-31-2018
    Location
    London, England
    MS-Off Ver
    MS Office 365
    Posts
    104

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    Hi Keebellah, long time!

    I hope everything is well with you.

    I was just wondering, would it be possible to tweak the "Save as 2018 Edition" button to be variable (ie create a sheet with an inputtable name) so that this can be reused many years over?

  79. #79
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    Next year it’s 2019 and thereafter 2030 etc etc, it’s entirely dynamic

  80. #80
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    Since we’re in 2018 the text is 2018

  81. #81
    Forum Contributor
    Join Date
    07-31-2018
    Location
    London, England
    MS-Off Ver
    MS Office 365
    Posts
    104

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    Awesome, thanks.

    Also, I wanted to add extra columns to the template file ‘Group _ Details’ sheet and transpose an additional column into the ‘Simple Group _ Details’ sheet, but the template file will not permit changes. How do i circumvent that?

    Additionally, I would like to create an extra file for a ‘VA’ Learner Details and not just the DA, as they are running a new course. The only difference would be they have two additional columns in the Data Report.

    Thanks in advance.

  82. #82
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    I guess you can better attach the file marking the 'new' things you want.
    The same with what you want for the VA version.
    You see, you giev the guy a finger and he takes the whole hand

  83. #83
    Forum Contributor
    Join Date
    07-31-2018
    Location
    London, England
    MS-Off Ver
    MS Office 365
    Posts
    104

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    Haha I guess so.

    So for both the VA and DA database I need to add the following columns titled: ‘EU’ and ‘Docs’ between the columns ‘Ref Comment’ and ‘Gender’ (they will become columns Q and R) with a data validation for ‘Yes’, ‘No’ or ‘N/A’. Preferably these columns will be in a different colour heading.

    Then, for both files, I want to transpose the information from the ‘Sus/Bri’ column into the Simple Group Details sheet.

    For the VA version, I would like to add two questions next to the column ‘Laptop’, for ‘Camera’ and ‘Editing’, with the same Data Validation (Yes/No/NA). Both of these for the VA would also need transposing into that version of the Simple Group Details. But these two columns are not needed for the DA version.

  84. #84
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    Attachments tell me more.
    Please do that, makes it easier and the positions are clear.
    I am not the designer but the coder

  85. #85
    Forum Contributor
    Join Date
    07-31-2018
    Location
    London, England
    MS-Off Ver
    MS Office 365
    Posts
    104

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    Yeah sorry it clears my message every time I attempt to edit it accidentally.

    Additionally, is there a way to allow the sorting of the Group _ Details table by the ‘Stage’ column, in the order of ‘Achiever’ at the top through to ‘Waitlist’ at the bottom?

    Thanks again.
    Attached Files Attached Files
    Last edited by doubleuson; 09-11-2018 at 05:59 AM.

  86. #86
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    First, is this the same as the final version I attached on August 23rd?

  87. #87
    Forum Contributor
    Join Date
    07-31-2018
    Location
    London, England
    MS-Off Ver
    MS Office 365
    Posts
    104
    Identical aside from an adjusted design on the Landing Page.

    I can either redo that or you can edit the version you have and I’ll sort out the design after.

  88. #88
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    First stage:
    I do not understand what you mean with 'not permit changes', can you be more specific?
    I can add any number of columns as I please, but why the transpose?
    This is not a simple table this is a ListObjcet table.
    When you right click in listObjects you can add rows or columns before or after or above and below.
    Working with listobjects is alltogether another train of thought.
    I guess it's time you read this site and learn how to work with list objects and how these work

    https://www.thespreadsheetguru.com/b...t-excel-tables

  89. #89
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    Seond Stage:
    Sus/Bri, you keep using the work transpose.
    In this case I think you mean insert it i the simple Group details sheet.
    Where?
    This will require inserting and modifying some of the VBA code

  90. #90
    Forum Contributor
    Join Date
    07-31-2018
    Location
    London, England
    MS-Off Ver
    MS Office 365
    Posts
    104

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    The template file comes up with a message preventing me from changing it.

    It turns out they have adjusted their data collection slightly while I was away, and the database just needs these extra columns for recording purposes. Nothing will be done with them, they are just a reference point.

    For the Simple Group Details, I mean insert into a new column between columns L and M in the current version of the file.

  91. #91
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    The VA file
    It's all in the name, you hardcoded it on the landing page but all you need to do if you want a different type is edit the vba code in the XIGlobalsBasic module

    Please Login or Register  to view this content.
    Change that to VA and then use SaveAs to save the file under the new name first, then close it normally.
    When you reopen all files will be VA etc. of course you still need the other changes to the columns. so you see, I tried to cover as many bases as I could

  92. #92
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    It doesn't prevent it just tells you itá a template file and because a 2018 already exists asks you to open that one.
    just type a 0 in the filed and the hidden sheets are opened.

  93. #93
    Forum Contributor
    Join Date
    07-31-2018
    Location
    London, England
    MS-Off Ver
    MS Office 365
    Posts
    104

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    Okay, so I adjusted this for the VA, but how do I go about changing the ListObjects table to add the required columns. Further, where can I add the data validation without the original sheet as a reference point?

    It's only a minor change that requires being made but it works really well for the actual data, so I guess I can only say thanks again.

    EDIT: So I get this error every time I want to add columns, regardless of the fact I'm editing there original file with no copies.
    Attached Images Attached Images
    Last edited by doubleuson; 09-11-2018 at 06:43 AM.

  94. #94
    Forum Contributor
    Join Date
    07-31-2018
    Location
    London, England
    MS-Off Ver
    MS Office 365
    Posts
    104

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    So I have managed to add a column for 'EU' and 'Docs' and put in a data validation for the table, however, I get the above error when attempting to put in a column for "Pass Returned" between the current W and X columns in the template sheet.

    I have also routed the Sus/Bri column into the Simple Groupt Details template, which is fine for now. For the DA database I just need to add the column and that should be a wrap, if it all works. Here's the file, can you diagnose why it won't let me.
    Attached Files Attached Files

  95. #95
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    Okay, now I got it what you mean. I was more thorough than I thought.
    There is a way to get around that but that requires you to enter the VBA modules, either you disable (temporarily) a set of commands or disable the Application Events.
    Probably handy if I added some code that would allow a person or persons in posses of a master password to allow editing.

    This is the code preventing you to edit
    Please Login or Register  to view this content.
    What is says is that if the active sheet's name is the one with 0 in it then it's the template sheet, which is exactly what it is

    I did better than I thought

  96. #96
    Forum Contributor
    Join Date
    07-31-2018
    Location
    London, England
    MS-Off Ver
    MS Office 365
    Posts
    104

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    It’s okay, I’ve actually managed to do the changes myself. I found a right click on and off glitch in Excel which allows me to edit the template. Cheeky glitch.

  97. #97
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    I changed it too and simplified the YESNO picklist

  98. #98
    Forum Contributor
    Join Date
    07-31-2018
    Location
    London, England
    MS-Off Ver
    MS Office 365
    Posts
    104

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    So these are my two versions of the files, with the additional columns formatted into both the Details and the Simple Details pages. They replicate themselves fine from the template, however, the multiple-selection code I wrote for the Data Validation of the 'IMPAIRMENT CATEGORY' column does not work. I then changed the TargetValue of the column to column 22 for DA and 24 for VA, respectively. However, they still do not allow me to select multiple choices simultaneously. Could you edit that into my versions please, if you can figure out the issue.
    Attached Files Attached Files

  99. #99
    Forum Contributor
    Join Date
    07-31-2018
    Location
    London, England
    MS-Off Ver
    MS Office 365
    Posts
    104

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    They are glitching and sometimes doing it when I change the target column retrospectively, but I cannot get it to work entirely successfully just once.

  100. #100
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    Okay, this happens when you hardcode column numbers in tables or whatever that may vary
    I try to avoid that so I added some code to take care of this

    In the module named TableModule001the function named getGroupColumn()

    Please Login or Register  to view this content.
    In the Group_0_Details worksheet (which will automatically replicated when adding groups) I add the following rows of code:


    Please Login or Register  to view this content.
    tCol will then hold the column number of the parameter passed which in this case was the column's name "IMPAIRMENT CATEGORY"

    And in the module named XIGlobalsBasic the reference for the worsheet

    Please Login or Register  to view this content.
    Attached Files Attached Files

  101. #101
    Forum Contributor
    Join Date
    07-31-2018
    Location
    London, England
    MS-Off Ver
    MS Office 365
    Posts
    104

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    Thank you for that.

    It is working fine on my computer, and the other Macs I have selected it on. However, it does not function correctly on the master Mac, bringing up the following error:

    Compile error:

    Can't assign to read-only property
    (ThisWorkbook 20:11)
    OR

    Compile error:

    Can't assign to read-only property
    (ThisWorkbook 11:11)
    It may be something to do with the way the computer opens Excel files, but it is crucial it works on this specific computer. Any ideas?

    Further, I am getting a Range error which, when debugged, indicates an issue with the line:

    Range("A1").Activate
    line of the Admin00 Landing Page Code.

    The new code for the IMPAIRMENT CATEGORY section is volatile, sometimes works, sometimes doesn't.
    Last edited by doubleuson; 09-11-2018 at 09:44 AM.

  102. #102
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    You never mentioned a MAC and I have never been able to get all thing working with a MAC.
    I gave up on that 11+ years ago and stuck to Windows.

    You can edit the code to not set RO, it's in the option when the file is opened for viewing and not for editing.
    All it does is select A1 when the landing page is activated.

  103. #103
    Forum Contributor
    Join Date
    07-31-2018
    Location
    London, England
    MS-Off Ver
    MS Office 365
    Posts
    104

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    Yeah but the issue is it works on all the Macs bar the master Mac. What would be the issue? Is there a path error in place?

    Why is it trying to open properties in read-only? Can I adjust the Excel settings in any way to prevent it?
    Last edited by doubleuson; 09-11-2018 at 09:59 AM.

  104. #104
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    If you google for that error you'll see several hits and some solutions.
    The only thing that comes to mind is a path error, but then that means that the path values which work on the others will not se on this MAC, you will have to debug it and go thorough that section of code step-by-step.
    I do not have any MACs at my disposition so it's all on your side to check.
    It's clear that it's an exception since only this system has the error and not the other ones.
    Troubleshooting will be the only solution

  105. #105
    Forum Contributor
    Join Date
    07-31-2018
    Location
    London, England
    MS-Off Ver
    MS Office 365
    Posts
    104

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    Turns out the error was due to an older version of Excel. We all good now.

    I am finally glad to say, /SOLVED

  106. #106
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Template, Cross-Sheet Referencing and OFFSET Function Assistance

    Great
    Thanks for letting me (us) know

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Excel cross referencing from one sheet to another using drop down box.
    By JJS241 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-25-2018, 02:14 PM
  2. Cross Sheet Referencing Issue
    By BeHereALot in forum Excel General
    Replies: 6
    Last Post: 04-09-2017, 01:45 AM
  3. Replies: 1
    Last Post: 04-27-2015, 03:08 AM
  4. combining IF in OFFSET function-Need Assistance
    By shuriyan0924 in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 01-15-2015, 09:24 AM
  5. A Challenging Question re: Cross-Sheet referencing
    By johnhasaquestion in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-05-2013, 09:58 PM
  6. Sheet Number Increments + Cross referencing.
    By ciaran182 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-20-2011, 11:12 AM
  7. Request assistance with cross sheet problem
    By andyofne in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-14-2005, 07:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1