User Profile
JosieL
Brass Contributor
Joined Apr 10, 2020
User Widgets
Recent Discussions
Problems sharing an Excel 365 workbook between Windows and Mac
Hi, I constructed an excel 365 spreadsheet on my Windows laptop which included the indirect formula within it. It all worked well. i shared the workbook with a colleague who uses Excel 365 for Mac on a Macbook Pro. He was also able to use the workbook without any problems - until last week. Now the sheet has lost functionality and when I investigated all the indirect references within the formulae had changed to the following - =_xlfn.ANCHORARRAY(INDIRECT(D4)) Why has this happened. I tried correcting the formula in his copy but this does not work. How can this issue be resolved700Views0likes0CommentsRe: Help with data calculations
mathetes I am back again!!! I hope you won't mind if I ask for some further advice regarding this sheet. I have made some changes to the original sheet and it has been in use by my friends and they have found it extremely useful. However they have asked for a change which has meant I need to expand the "salmon coloured" table on the Tables tab to include another column. When I do this I cannot get the indirect data validation to work if that column is selected. The column I have added is Column AV on the tables tab. On the Programmes tab when 96 is selected in Column F the appropriate rep schemes do not show up in the data validation list - cell highlighted yellow on the attached. I assume that I have not made the indirect link. I have spent hours looking at You tube videos and other tutorials but I cannot see how to expand the columns in a table and make it work with the indirect function. I really cannot get my head around this function and would be extremely grateful for any clarification2KViews0likes0CommentsRe: Help with data calculations
mathetes Hi Sorry to bother you again. I have spent my free time going through the spreadsheet carefully and I have been able to replicate what you did and understand it. The only thing I cant get my head around is how the reps scheme cell in column G connects to the correct array table. Think I have brain fog from being in lockdown too long!! I want to make the data validations static because there are no other schemes that will be added but because i dont understand how column G works, I cant seem to do it. If you could point me in the right direction I would be so grateful. Thanks4.2KViews0likes2CommentsRe: Help with data calculations
mathetes Riny_van_Eekelen I spent yesterday evening going through the changes to the spreadsheet and filling in the other required cells. It seems to work exactly as they want it too. I will hopefully send them the completed worksheet today for feedback -Fingers crossed. Thanks for all the help. Hopefully later I will be giving you a final thanks. I have learnt so much going through this4.3KViews0likes4CommentsRe: Help with data calculations
Riny_van_Eekelen Thank you for your idea. Although it may not help me on this occasion I think that I will find it useful and i will be playing around with it as part of my learning. I have not worked with arrays before and this has all been really helpful. Thank you very much.19KViews0likes0CommentsRe: Help with data calculations
mathetes Hi. I have just caught up with all these messages. I haven't looked at what you have done on the spreadsheet yet. I will do that next. I just wanted to say how much I appreciate your help. This is a very steep learning curve for me so I am very grateful. With regard to the awkward reps e.g. 2-4 ,15-20 etc. I have spoken to them about this issue this morning because it was something I was thinking about a lot last night. We have agreed that we can use the largest number in each of those ranges. However I do think that your idea of having some cells that they can fill manually would be the way forward in reality. They are bound to want to do something that doesnt fit at some point!!19KViews0likes0CommentsRe: Help with data calculations
mathetes Hi again!!!! I have looked at the sheet now and the Rep Scheme column does not change in the rows below the top row. It only links to the number of reps set in the top row. For example if there is 4 in the Sets of Reps column in the top row then no matter what is selected in the lower rows, you still get the list of 4 reps to select from. Does this also need a "helper" column to make it work? If it is simpler to make the sheet work through all the rows by making it Static how would that be done? Thanks19KViews0likes10CommentsRe: Help with data calculations
mathetes Hi - yes I get what you are saying about the design of the spreadsheets to meet the needs rather than wants. However in this case i believe the layout as finalized (they have agreed to the tables all being on one sheet rather than separate sheets) does meet their needs as we discussed on our conference call last week. It will allow them to track a clients progress through the months and adjust the programme as the individual client needs. I have attached the sheet so you can see what we have agreed to work with. My next task is to attach the "Tables" sheet to this so they can pick up the reps schemes as they want. I have left the therapist screen sheet in but i will be deleting that. I have started that today. I have managed to link the data validation lists to all the rows in Sheet 1, workout 1 but I cannot get the reps schemes to link to the lookup table based on the strength quality chosen. It works in the top row but then doesnt change for subsequent rows. This means than total reps cannot be calculated based on rep schemes for any other rows. I have tried to change the formulae to include all the rows and i assume this should be a dynamic array - or should I be using a different function? It could be that i just wrote the formula incorrectly. I do think that learning by doing is a very good method. Thanks again for your help.19KViews0likes18CommentsRe: Help with data calculations
mathetes Hi again. Thanks for your thoughts. Basically each workbook is for one client. The separate sheets are for logging the workouts as they progress. The trainer keeps these workbooks to monitor progress, they are not given to the client. The table layout is set out as they require it which I think is fine. I have asked them if we could put the tables all on one sheet either going down or across so there is just the one active sheet. Have not had a response yet. I am also self taught when it comes to spreadsheets. This is the most challenging one I have had so far because I haven't used arrays before. I do love spreadsheets though and I am learning such a lot from this one. I am taking up a lot of your time though which I hope isn't a problem. I will read the clip you have attached. Thanks19KViews0likes20CommentsRe: Help with data calculations
mathetes Hi. I have had a long conference call with my friends to confirm exactly what they want from this sheet. It is so much more long-winded without being able to meet face to face at the moment. However i have got to an understanding of what they want to do. It is more complicated than they originally described. i have set up a draft workbook covering their requirements and i have put explanatory notes on the first sheet explaining the layout they are looking for. All five sheets need to work in the same way. There is a salmon coloured text box with some questions from me. i have not tried to put any calculations/formulae in yet. I have also spent some time looking into the INDIRECT function which I had not ever used. I can see how useful that can be. if you have any time to look at my questions on the sheet, it would be great. I will attempt to construct what they need then. Thanks20KViews0likes22CommentsRe: Help with data calculations
mathetes Thank you for this. I will go through it carefully to understand what you have done. I have a conference call with my friends tomorrow so I will have a good understanding of how they want to use this information and what they want it to look like. Again thanks for your help JosieL19KViews0likes0CommentsRe: Help with data calculations
mathetes Hi I am sorry to bother you again but I am definitely missing something from your instructions. I have set up the other columns to match what you had done with the first one i.e. Relative Strength but when I try to make a matching formula following what you had done in Sheet 2, column F I get an error message. i really cant see where I am going wrong but I obviously am!!! If you could add any pointers i would be really grateful. Thank you17KViews0likes25CommentsRe: Help with data calculations
mathetes Hi this is looking good. I haven't quite got the full understanding of what you have done especially about the data validation but I will have a play around to see if I can work it out. My query with the validation is that it only refers to one cell reference but I will work it out. Thank you17KViews0likes28CommentsRe: Help with data calculations
mathetes Hi. I have looked at the "Simpler" idea you had but it doesnt really work for what they want. They wont be adding any other rep schemes. I have typed in all the rep schemes on the screenshot I was given. Because there are so many, I had a thought about how to locate the one they want more easily. I have divided the rep schemes into the same columns as provided in the screenshot i.e. Relative strength, functional hypertrophy, Hypertrophy, strength endurance. These are known as strength quality. I have added a column in the table on sheet one called strength quality and put in a data validation list naming these options. I have also put in a column to identify the number of reps e.g. 3 (5,3,3) etc. Is it possible to select the correct lookup table by linking to the strength quality and number of reps e.g. SE, 3 , so it would select Strength Endurance list and highlight all the 3 rep schemes. I couldnt work out what to do with the ones that said things like 3-5 which would basically be one set of between 3 to 5 reps so I have typed them in as the highest number in the option i.e. in this example 5. I have attached spreadsheet to show what I have done.17KViews0likes32CommentsRe: Help with data calculations
mathetes Hi. Thank you for this. If I understand you correctly I need to make a table on sheet 2 of the workbook you sent me to include all the rep schemes on that screenshot i was given. Is that correct? I cant quite get my head around the revision that https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176&data=02%7C01%7C%7C77712a81ce72478500fc08d7dfc3d6ae%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637223904531733846&sdata=lFG7DjOIS5gf5vybwjdpzNit0JxMsXr9nnyLcLutWto%3D&reserved=0 sent. I have asked again for a check about the percentages but everything I have been told indicates that they are not required for the calcs. This is very exciting - I am learning so much more about Excel. I will make the table up17KViews0likes35CommentsRe: Help with data calculations
Hi Further to my reply on Saturday, I have spoken to my friends to get more information on the numbers of rep schemes. There are a large number of variations and the choices are related to what the required outcome of the training is. I have attached a screenshot she has sent me showing the full list. I was wondering if by adding an additional column to show what the training was designed to achieve e.g. relative strength, functional hypertrophy, hypertrophy or strength endurance as per the screenshot, this could make it easier to select the appropriate rep scheme and therefore the calculation as specified in previous communication. Does this help or not, thanks17KViews0likes39Comments
Recent Blog Articles
No content to show