+ Reply to Thread
Results 1 to 74 of 74

How to Layout my Table

  1. #1
    Registered User
    Join Date
    06-07-2018
    Location
    Vancouver
    MS-Off Ver
    2016
    Posts
    43

    Red face How to Layout my Table

    Hi,

    I am trying to create an Excel sheet where it will help track the progress of each exercise using a chart.

    I wish to create a dynamic chart where it will change per exercise and show the progress through the weeks.

    I have tried creating a table, but I'm not sure how to lay my columns out exactly as there are conflicting columns.

    Any help is greatly appreciated!

    Brendon

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to Layout my Table

    Hi, welcome to the forum

    The 1st step in pretty much any excel adventure, starts with laying out the data entry/capture/storage part 1st! Many new users start the other way round, but creating what they want the output to look like, and then try too figure out how to enter/capture the data - this almost always ends in tears as they suddenly find out that to get what they need, they need to do what they dont want to, or that it cant even be done that way.

    We always advocate designing the data-entry side 1st - the rest simply follows on from that. The recommended way to capture the data is a simple 2-D table, with (probably, in your case) dates going down, and exercises across the top.

    Can you show us what you have in mind/have so far?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    06-07-2018
    Location
    Vancouver
    MS-Off Ver
    2016
    Posts
    43

    Red face Re: How to Layout my Table

    Thank you for the reply!

    I have attached the excel file for what is supposed to be my training program.

    I hope to input data and it will have an automatically updating dashboard where it will help keep track of the exercises!
    Attached Files Attached Files

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to Layout my Table

    I would use real dates instead of week numbers, and turn your table 90 degrees
    Also, if the reps each set will be the same, just have a single column for them?

  5. #5
    Registered User
    Join Date
    06-07-2018
    Location
    Vancouver
    MS-Off Ver
    2016
    Posts
    43

    Re: How to Layout my Table

    Thank you very much for the suggestions.

    I have rotated the table and changed the weeks to just numbers, but I seem to still have trouble creating meaningful charts and graphs out of the data.

    Should I make the entire data a table and work from there?

    Sorry, I do not have much experience with excel other than creating simple graphs.
    Attached Files Attached Files

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to Layout my Table

    I have made a few adjustments to your table to streamline it a bit more, make it a bit smaller and make the data entry a bit simpler. Let me know what you think?

    1 step at a time. If you are OK with the layout, we can move on to the next step to start putting charts together
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    06-07-2018
    Location
    Vancouver
    MS-Off Ver
    2016
    Posts
    43

    Re: How to Layout my Table

    Wow.

    I'm not sure how I can thank you for your work...

    The data looks a whole lot simpler to work with.

    Thank you so much!

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to Layout my Table

    I thought you might like it - all part of the service, and thanks for the feedback

    Now, once you have got a feel for the new layout, we can start working of some charts and/or summary tables.
    (did you notice I used a different formula for the Volume?)
    Bed time now though, its just gone 1 am

  9. #9
    Registered User
    Join Date
    06-07-2018
    Location
    Vancouver
    MS-Off Ver
    2016
    Posts
    43

    Re: How to Layout my Table

    Ah, yes. I was desperately trying to figure out that SUMPRODUCT function... haha.

    Also, due to my limited knowledge, I was too scared to create dropdowns for exercises, which led me to create an A and B version of the workouts.

    Would it be possible to put the exercises into categories and just have one "upperbody" and one "lowerbody"? (Ex. Benchpress and dumbbell incline as one dropdown.)

    I really appreciate all the help and hope you have a great night!

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to Layout my Table

    We could do that, yes, but how would you summarize something like that? Wouldnt different tasks end up getting mixed up?

    Anyway, I got to go now, need to study or a blood test I need to have, tomorrow (well, today, really). I will pick this up again later

    Also, dont be "scared" to try anything, or to ask anything here. We are all really friendly, and are here to help - else we wouldbt be here. We all had to start learning at the bottom, and work our way up, so most of us still remember how intimidating that can be. Think of us as a safety net

    the SP works by taking each item in teh 1st range and multiplying by the corresponding item in the next range - pretty much what you had done manually

  11. #11
    Registered User
    Join Date
    06-07-2018
    Location
    Vancouver
    MS-Off Ver
    2016
    Posts
    43

    Red face Re: How to Layout my Table

    I have attached an excel workbook that gave me the inspiration to make a much more effective and better looking one. (The program itself is very lacking in my opinion)

    The one attached has an option to dropdown and choose from exercises for the week.

    This one just grouped everything together, but it would be much better split it up to only have 2-3 exercises per order of the exercise as there are exercises that are perfectly fine interchangeable.(if that makes sense.. )

    Maybe giving the option to change the exercise every 1, 2, 4 or even 8 weeks.

    And last question!

    The excel sheet i attached above has a limit of "34 sessions." Is it possible in excel to have it automatically add as more data is inputted? (have the graphs grow with the data "infinitely")

    Sorry if I was not clear on anything and wish you all the best on your blood test!
    Attached Files Attached Files

  12. #12
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to Layout my Table

    I see that the sheet is protected, do you have the password, by chance?

    Adding dropdowns is actually pretty simple, once you get the hang of it.

    Also, I often find that working from some-one elses template, means that you are doing it the way THEY want it done. If that coincides with how you want it done - great, but more often than not, "you" want it done differently.
    I guess what Im saying is that if that file works for you, good, but otherwise we can take concepts and ideas from it, and make 1 that suites YOU

  13. #13
    Registered User
    Join Date
    06-07-2018
    Location
    Vancouver
    MS-Off Ver
    2016
    Posts
    43

    Re: How to Layout my Table

    Unfortunately, I do not have the password.

    But I definitely want to work out of my own template.

    Wanted to explain the concept in an easier way

  14. #14
    Registered User
    Join Date
    06-07-2018
    Location
    Vancouver
    MS-Off Ver
    2016
    Posts
    43

    Re: How to Layout my Table

    I went ahead and made an attempt at creating a dropdown in cell: E1!

    Just wanted to make sure I did it correctly before I went ahead on all the other cells

    P.s. will it still be possible to track each item in the dropdown in the charts later?
    Attached Files Attached Files

  15. #15
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to Layout my Table

    there is nothing magical about a drop down (Data Validation). It is exactly the same as if you had typed the entry in manually, the only differences being that a DD can save you some typing, help avoid typos and help restrict entry to only a specified "list". Other than that, there is basically no difference. You would refer to it in formulas etc the exact same way as if you were referencing a manually entered cell.

  16. #16
    Registered User
    Join Date
    06-07-2018
    Location
    Vancouver
    MS-Off Ver
    2016
    Posts
    43

    Re: How to Layout my Table

    I see.

    Should I have the referenced list on another sheet or keep it on the same on just somewhere out of the way?

  17. #17
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to Layout my Table

    Hmm looking at your file, the DD looks perfect, except for 1 tiny little point.

    If you select Bench Press for 3 sets for week 1, then select dumbbell PB for the next week, how will excel know that the 1st week only had regular BP's?
    Perhaps a code for each type, next to each entry, instead of the heading?

  18. #18
    Registered User
    Join Date
    06-07-2018
    Location
    Vancouver
    MS-Off Ver
    2016
    Posts
    43

    Re: How to Layout my Table

    Yes. That was what I was wondering. I'm not exactly sure what you mean by coding but I was maybe thinking of having it in 4 week blocks?

  19. #19
    Registered User
    Join Date
    06-07-2018
    Location
    Vancouver
    MS-Off Ver
    2016
    Posts
    43

    Re: How to Layout my Table

    So I created the DD for all the sections and made an attempt to clean the sheet up a bit. (Put all the exercises on another sheet.

    If it is not possible to make excel track each exercise when it is chosen for the weekly progress graphs, we can revert it back to A and B and have set exercises!
    Attached Files Attached Files

  20. #20
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to Layout my Table

    What I was thinking, was a 3rd column to each category. Something along these lines....
    Attached Files Attached Files

  21. #21
    Registered User
    Join Date
    06-07-2018
    Location
    Vancouver
    MS-Off Ver
    2016
    Posts
    43

    Re: How to Layout my Table

    Hmm.. I'm not sure what layout would be the best as I'm not fully sure how it will interact with the graphs.

    I was thinking that the exercise is chosen at week 1 and the same one carried throughout for the 8 weeks.

    Would that make it fine to leave the DD at the top?

  22. #22
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to Layout my Table

    Yes, if you wont be changing the heading for the "duration", then having the DD in the heading would work great. I was just concerned if you changed 1/2 way through the period - if that wont happen, then there wont be a problem

  23. #23
    Registered User
    Join Date
    06-07-2018
    Location
    Vancouver
    MS-Off Ver
    2016
    Posts
    43

    Re: How to Layout my Table

    I think for now, having DD at the top is fine
    Should I turn everything into a table now?

  24. #24
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to Layout my Table

    If you intend to add extra weeks at the end, maybe, but if what you have, is all you intend to have, then I dont really see a need for that.

    So your "schedule" is an 8-week period?
    Do you intend to repeat the "table" below for the next sched?

  25. #25
    Registered User
    Join Date
    06-07-2018
    Location
    Vancouver
    MS-Off Ver
    2016
    Posts
    43

    Re: How to Layout my Table

    It would be ideal to have it "infinitely growing" but if that's too complicated, I'm perfectly fine with a 8 week period!

  26. #26
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to Layout my Table

    My only concern with it constantly growing is that the heading determines what that column contains, and if you start with 1 thing, then change somewhere down the line, everything in that column will be seen as the new item

    (supper time)

  27. #27
    Registered User
    Join Date
    06-07-2018
    Location
    Vancouver
    MS-Off Ver
    2016
    Posts
    43

    Re: How to Layout my Table

    Ya

    Is there a way around that problem?

    Have a good meal!

  28. #28
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to Layout my Table

    The only way around that I can think, is to have that 3rd column (with a DD) to select the item

  29. #29
    Registered User
    Join Date
    06-07-2018
    Location
    Vancouver
    MS-Off Ver
    2016
    Posts
    43
    I think being able to choose different exercises while it tracks could definitely be very useful so I’ll give that a shot 😊

    Do you think there’s a better way to organize the data with the third column and is there a way to make set 2 and 3 stay consistent with the exercise of set 1?

    Many thanks!

  30. #30
    Registered User
    Join Date
    06-07-2018
    Location
    Vancouver
    MS-Off Ver
    2016
    Posts
    43

    Red face Re: How to Layout my Table

    I added the third columns for everything, changed the DD accordingly, added "workout volume" using SUM and changed the format the slightest bit.

    Let me know if you would make any changes and if I did everything correctly!

    Thanks!
    Attached Files Attached Files

  31. #31
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to Layout my Table

    Thats looking really good, I really like the flexibility to change the type each week, I think it makes it easier to use. The main thing is - is that what YOU want? Doesnt really matter what I - or anyone else - wants, this has to work for YOU. I/we can guide you on how best to achieve this, but it needs to be your process

    As far as making set 2/3 the same as set 1, I have made a small change to each set 2/3 so they take whatever set 1 was (A quick way to change all the cells/formulas foe something, is to hide the weight/rep columns so you only need to move over 1 cell, instead of 2)
    Attached Files Attached Files

  32. #32
    Registered User
    Join Date
    06-07-2018
    Location
    Vancouver
    MS-Off Ver
    2016
    Posts
    43

    Re: How to Layout my Table

    If the type column will allow me to keep track of each exercise while the weeks grow, that is exactly what I want!

    Another small problem I had was while I was copy and pasting the type column, I believe the text is now on top of the following cell... what is the fix for this?

    I believe I now have everything I need for the charts! What would be the next step

    Also, the column hiding trick is very handy. Thank you

  33. #33
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to Layout my Table

    I noticed that your text overlapped the adjacent cell. I was going to suggest using initials instead of full words. Or maybe (where you can( give a top heading of, say, Bench Press, then in the DD's have BP, IBP, DBP etc.

    I think next would be to make a summary table/s to base your charts off. How do you visualize the chart/s looking?

  34. #34
    Registered User
    Join Date
    06-07-2018
    Location
    Vancouver
    MS-Off Ver
    2016
    Posts
    43

    Re: How to Layout my Table

    I see. I could also just extend the cells bigger to fit the full words right?

    I was thinking of having a line chart with weeks on the x-axis (a dynamic one that can grow with the weeks if possible?), one chart each for: exercise volume, 1-RM and workout volume (y-axis being in pounds or volume) and make the charts interactive so you can switch between exercises!

  35. #35
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to Layout my Table

    Quote Originally Posted by brendon0224 View Post
    I see. I could also just extend the cells bigger to fit the full words right?
    Yes you could, but your table ill become very wide. Another option would be to use "Wrap Text" and make the rows higher - again, making your table bigger.
    You would have probably noticed that I locked the headings and 2 side columns, so they are visible when you scroll down/across

    I was thinking of having a line chart with weeks on the x-axis (a dynamic one that can grow with the weeks if possible?), one chart each for: exercise volume, 1-RM and workout volume (y-axis being in pounds or volume) and make the charts interactive so you can switch between exercises!
    OK that is doable. maybe create a few summary tables 1st, hook them up with a DD so you can switch, then use them for the charts?

  36. #36
    Registered User
    Join Date
    06-07-2018
    Location
    Vancouver
    MS-Off Ver
    2016
    Posts
    43

    Re: How to Layout my Table

    Yes. I did notice the locked columns and rows! (makes the data much easier to understand )

    Creating the summary tables is where I seem to not be able to figure out... I've been looking at some tutorials online, but have yet to find one that seems to help me.

    Should I turn the whole thing into a table?

  37. #37
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to Layout my Table

    I changed some of your formulas in the 1st 2 items, to values so I could test my formulas.
    Then I added a new sheer - Summary - to start, well, summarizing. I just did the 1st column, see if that is heading in the right direction?
    Attached Files Attached Files

  38. #38
    Registered User
    Join Date
    06-07-2018
    Location
    Vancouver
    MS-Off Ver
    2016
    Posts
    43

    Re: How to Layout my Table

    Unfortunately, I have no idea how the formulas in the summarized sheet work unfortunately but that is definitely in the right direction!

    If you could explain how the formula works, I will finish the rest of the exercises up. (where should I add the columns for the other exercises?)

    Thanks a lot

  39. #39
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to Layout my Table

    Sure, no problem - sorry, I should have included an explanation in the file.

    Column A
    This is just the sequential week number.

    So you dont have to type them in each week, I added a formula, which checks if you have a Type there yet.
    =IF(B3="","",A2+1)
    All this does is test if col B is empty, if it is, show nothing, else add 1 to the value above

    Column B
    This lists each and every type in that column, and come to think of it, seeing as you are using a compound score for WO volume, maybe that needs so go on a separate table?
    =IFERROR(INDEX('Sheet1 (2)'!$C:$C,MATCH(ROWS($B$1:B1),'Sheet1 (2)'!$A:$A,0)),"")
    Looks complicated, but it's not really. As with most nested formulas, you need to work from the inside out.
    ...ROWS($B$1:B1)...
    This just generates a sequence (1, 2, 3) as you copy it down - to match with the week number)

    ...MATCH(ROWS($B$1:B1),'Sheet1 (2)'!$A:$A,0)...
    This takes that generated number and searches for it in column A, to see which row it is in. The ,0 at the end is to tell excel to look for an exact match

    ...INDEX('Sheet1 (2)'!$C:$C,MATCH(ROWS($B$1:B1),'Sheet1 (2)'!$A:$A,0))...
    INDEX is a very useful formula to know and understand, it returns the cell contents from a defined range (C:C in this case), that is at the intersect of x-rows down and y-columns across. In this case, I used a single column for the range, so I dont need to specify the column

    =IFERROR(INDEX('Sheet1 (2)'!$C:$C,MATCH(ROWS($B$1:B1),'Sheet1 (2)'!$A:$A,0)),"")
    Finally, I wrap the whole thing in IFERROR to catch the errors for when there are no more week numbers

    Column C (and D/E)
    This pulls the total for each type, and these are the columns that will need to be repeated sideways, to pick up the other types.
    ...MATCH($A2,'Sheet1 (2)'!$A:$A,0)...
    again the MATCH function to ID the row number

    +COLUMNS($A:C)...
    In this case, we want to drop down a few rows from where the week number is, like the ROWS() function I used, this creates a sequence as you copy across, but I am adding it to the MATCH to go down extra rows
    +COLUMNS($A:C)

    hmm you know what, I am going to scrap the COLUMNS() and just use numbers instead, I will show the NEW formulas below and in the attached file, which will allow for 52 weeks

    OK
    ...INDEX('Sheet1 (2)'!$D:$D,MATCH($A2,'Sheet1 (2)'!$A:$A,0)+3)...
    Once again the INDEX function to return what it finds at the designated row (+3 in this case, because Ex Vol is 3 extra rows down from the week number(

    =IF($B2="","",INDEX('Sheet1 (2)'!$D:$D,MATCH($A2,'Sheet1 (2)'!$A:$A,0)+COLUMNS($A:C)))
    Finally, I do the same test to check if B is empty

    OK, so here are the revised formulas...
    C2=IF($B2="","",INDEX('Sheet1 (2)'!$D:$D,MATCH($A2,'Sheet1 (2)'!$A:$A,0)+3))
    D2=IF($B2="","",INDEX('Sheet1 (2)'!$D:$D,MATCH($A2,'Sheet1 (2)'!$A:$A,0)+4))
    E2=IF($B2="","",INDEX('Sheet1 (2)'!$D:$D,MATCH($A2,'Sheet1 (2)'!$A:$A,0)+5))

    Now, though this whole thing, the ONLY adjustment you need to make, is to the range in the INDEX part (bolded) This needs to correspond to the Type you are referencing

    To pull the Type...
    =IFERROR(INDEX('Sheet1 (2)'!$C:$C,MATCH(ROWS($B$1:B1),'Sheet1 (2)'!$A:$A,0)),"")
    change to F, I, L etc

    =IF($B2="","",INDEX('Sheet1 (2)'!$D:$D,MATCH($A2,'Sheet1 (2)'!$A:$A,0)+3))
    Change to G, J, M etc

    Probably the quickest way to do this is to copy B2:E2 to F2, then change F2 from C:C to F:F, then change G2:I2 from D:D to G:G - then copy all 4 formulas down
    I have done the 2nd type for you, see how you make out with the rest (shout if I havnt explained properly)
    Attached Files Attached Files

  40. #40
    Registered User
    Join Date
    06-07-2018
    Location
    Vancouver
    MS-Off Ver
    2016
    Posts
    43

    Re: How to Layout my Table

    Thank you so much for taking the time to explain all the formulas.... Can't thank you enough and made me realize how little I know about excel...

    So I tried to add the summarized version of the second exercise, but it does not seem to update properly.. (I moved workout volume to the end since there is only one per week!)

    Thank you once again! I will need to sit down with a cup of coffee to fully understand these formulas
    Attached Files Attached Files

  41. #41
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to Layout my Table

    OK, thats why I changed the COLUMNS() function to be just 3 and 4 - as you copy it across, the answer gets progressively larger. Give it a try and see how it works. Put $A:A) into a cell, then drag/copy it across, you will see that it starts with 1, then 2, 3 etc. So by the time you got to F, instead of 3 (extra rows down from the week number), you were actually at 6 already. In my upload in post 39, I had already made that change.

    It may be an idea to keep WO vol in the column right after week num, so it's right at the start - but right at the end also works, it's whatever your preference is

    Enjoy your foffee, and get back to me if I need to explain anything more/better/etc (I am very patient lol)

  42. #42
    Registered User
    Join Date
    06-07-2018
    Location
    Vancouver
    MS-Off Ver
    2016
    Posts
    43

    Red face Re: How to Layout my Table

    I believe I figured it out somewhat!

    I filled it out for all the exercises and was wondering what would happen past week 8?

    Attached Files Attached Files

  43. #43
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to Layout my Table

    Looks great, good job

    I filled it out for all the exercises and was wondering what would happen past week 8?
    Try it and find out

  44. #44
    Registered User
    Join Date
    06-07-2018
    Location
    Vancouver
    MS-Off Ver
    2016
    Posts
    43

    Re: How to Layout my Table

    Might be a very dumb question, but how do I test it out?

  45. #45
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to Layout my Table

    Just another week. Copy the whole of week 8 (rows45:50) down to row 51, then just change the (new) 8 to 9.

    You could probably set up the whole year if you wanted to, using blank entries where the data needs to go

  46. #46
    Registered User
    Join Date
    06-07-2018
    Location
    Vancouver
    MS-Off Ver
    2016
    Posts
    43

    Re: How to Layout my Table

    Oh wow. That is neat :D !!

    Is everything ready to make the charts now?

  47. #47
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to Layout my Table

    I believe so, yes.

    What did you have in mind for them?

  48. #48
    Registered User
    Join Date
    06-07-2018
    Location
    Vancouver
    MS-Off Ver
    2016
    Posts
    43

    Re: How to Layout my Table

    3 line charts in total: Exercise volume, 1-RM and Workout Volume

    If the exercise volume and 1-RM chart can have a function to click between all the exercises to see its weekly progress, that would be amazing so we wouldn't need a chart for each exercise

  49. #49
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to Layout my Table

    Let me think about how best to do that.

    What about those Types that have multiple entries in them? eg col B

  50. #50
    Registered User
    Join Date
    06-07-2018
    Location
    Vancouver
    MS-Off Ver
    2016
    Posts
    43

    Re: How to Layout my Table

    I was wondering if it would be possible to switch between the exercises in the DD types too?

    Basically all the exercises in the "exercises" sheet..

    Thank you once again

  51. #51
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to Layout my Table

    I am having a bit of a brain fart on this ATM, so I have reached out to the other experts for some input

    Here the latest update on the file, so far
    Attached Files Attached Files

  52. #52
    Registered User
    Join Date
    06-07-2018
    Location
    Vancouver
    MS-Off Ver
    2016
    Posts
    43

    Re: How to Layout my Table

    No worries! I realize I am asking for a lot...

    Thank you for your patience with an excel beginner like me. Means a lot

  53. #53
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to Layout my Table

    We all had to start somewhere, and excel is HUGE in terms of what it can do and how much there is to learn - we are all here to try and help with that

  54. #54
    Registered User
    Join Date
    06-07-2018
    Location
    Vancouver
    MS-Off Ver
    2016
    Posts
    43

    Re: How to Layout my Table

    Hi! I was wondering if anyone got back to you about the problem yet?

  55. #55
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to Layout my Table

    Would it be possible to give each TYPE column a unique heading? Based on that, I can pull each week/item from the selected column - we can then chart just that

  56. #56
    Registered User
    Join Date
    06-07-2018
    Location
    Vancouver
    MS-Off Ver
    2016
    Posts
    43

    Re: How to Layout my Table

    Yes! I named them in the Summary sheet but not the Data sheet. (Let me know if I should!)

    Also just changed weeks to sessions instead
    Attached Files Attached Files

  57. #57
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to Layout my Table

    Maybe you uploaded an older version of the file? The headings still all say TYPE?
    A
    B
    C
    D
    E
    F
    G
    H
    I
    2
    Week Type Exercise Volume 1-RM Type Exercise Volume 1-RM Type Exercise Volume
    3
    1
    Bench Press
    3400
    183.67
    Barbell Row
    1800
    192
    Standing Military Press
    540
    4
    2
    Bench Press
    3400
    183.67
    Barbell Row
    3400
    183.67
    Standing Military Press
    0

  58. #58
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to Layout my Table

    It's the headings in cols B, E, H Im talking about

  59. #59
    Registered User
    Join Date
    06-07-2018
    Location
    Vancouver
    MS-Off Ver
    2016
    Posts
    43

    Re: How to Layout my Table

    Sorry, I did upload the wrong file
    Attached Files Attached Files

  60. #60
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to Layout my Table

    OK, I have made a very basic chart for us to start with.

    I added a new sheet called Chart, and I have a DD in B1 that will let you change the TYPE as needed. This will, in turn, update the chart accordingly.

    This is just a very basic, 1st stab, at giving you what you wanted, so we can build on it from here
    Attached Files Attached Files

  61. #61
    Registered User
    Join Date
    06-07-2018
    Location
    Vancouver
    MS-Off Ver
    2016
    Posts
    43

    Re: How to Layout my Table

    Ahhh I see. That is very neat. Crazy how all the sheets still work with each other!

    Do you think it is possible to have DD to have all of the exercises instead of the type name since if the exercises change from one week, it will not show in the graph? (I was thinking maybe having a chart for each type and the DD can be the exercises under it?)

  62. #62
    Registered User
    Join Date
    06-07-2018
    Location
    Vancouver
    MS-Off Ver
    2016
    Posts
    43

    Re: How to Layout my Table

    https://www.youtube.com/watch?v=K74_FNnlIF8

    This was a video I was looking at before I found this forum. The interactive dashboard in this video is something that I hope to work towards in the end

  63. #63
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,424

    Re: How to Layout my Table

    I am thinking that you may be reinventing the wheel. It seems as if the weight lifting regime you are charting is very similar to one developed by Dr. Byod Epley at the University of Nebraska. This thread includes a workbook (post #6) that has some charts on the WSOC Annual sheet (rows 27 - 37).
    That said perhaps a pivot table and chart might provide the flexibility that you want.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  64. #64
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to Layout my Table

    That is indeed, a very impressive dashboard. I think JeteMc has made a good start in that direction for you. Just goes to show that you (me) can sometimes get blinders on, and forget to look outside the box

  65. #65
    Registered User
    Join Date
    06-07-2018
    Location
    Vancouver
    MS-Off Ver
    2016
    Posts
    43

    Re: How to Layout my Table

    Wow... that is a crazy dashboard. Thank you very much for sharing that. I can't believe how helpful people are in this forum and can't thank you guys enough

    This will definitely allow me to take the program to another level.

    I will most definitely have questions, but I will try my best to understand what is going on first!

  66. #66
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: How to Layout my Table

    @Ford:
    hats off to you for persisting through 5 pages of guidance. i'm just going to post this 1 file and scurry away. =D

    well brendon. Ford called for assistance as he mentioned and i almost did not want to read 5 pages to understand the whole thing. but somehow i did browsed through and it seems like you had no idea how to store the data at the start. which is good news because I can lay it however i think it would work best. you probably have to work on changing the data worksheet though. It is difficult to extract data like that. I made a final layout on how i think it is easier for doing PivotTable. Jete used that straightaway for it, but i thought it still can be refined further.

    basically, you would want all items under the same category to be in 1 column. that would be your exercise volume and 1-RM. Notice it keeps repeating in different columns. i don't know how you want to label "Chest Main", "Back Main", etc but they all should be under 1 column. I named it Category 2. Category 1 is upper or lower body. you then can have all sorts of manipulation with ease.

    to create a pivottable by the way.
    click on cell A1.
    go to INSERT tab -> PivotTable
    click OK
    on the right side of the Excel window is the PivotTable Fields. that controls what you wish to see in the report
    click and drag the Exercise Field to the FILTERS area (to present in a FILTER)
    click and drag the Session Field to the ROWS area (to present each in ROWS)
    click and drag the Exercise Volume and 1-Rm Field to the VALUES area

    go to the DESIGN tab -> Report Layout -> Show in Tabular Form (each ROW fields in separate columns and label your header)
    go to the DESIGN tab -> Subtotal -> Do not show Subtotals

    go to the INSERT tab -> Slicer. Choose Category 1 and Category 2.

    create your charts and select the Slicer for it to change accordingly.
    Attached Files Attached Files

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  67. #67
    Registered User
    Join Date
    06-07-2018
    Location
    Vancouver
    MS-Off Ver
    2016
    Posts
    43

    Re: How to Layout my Table

    Hi benishiryo,

    Thank you for taking the time to take a look at my endless issues.

    I compltely agree with the fact that organizing the data has been my weakest point. Your advice definitely helps me understand it a bit better!

    Thank you

  68. #68
    Registered User
    Join Date
    06-07-2018
    Location
    Vancouver
    MS-Off Ver
    2016
    Posts
    43

    Re: How to Layout my Table

    Hi guys,

    I realized that the excel sheet I was working on was way too complicated for me so I started on an easier routine to get the hang of the fundamentals first and get back to the other one.

    I have attached a much simplier version. If I could get any advice/ feeedback on how to lay out my data and how to organize the data on the summary sheet for a pivot table, I would highly appreciate it.

    Thanks!
    Attached Files Attached Files

  69. #69
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,424

    Re: How to Layout my Table

    This really looks very similar to what the people at the University of Nebraska were doing. Before investing too much more time, you might want to do a search for their product, I do not work with or for them, "Husker Power" and see if they don't already have a spreadsheet that will do what you want.
    Let us know if you have any questions.

  70. #70
    Registered User
    Join Date
    06-07-2018
    Location
    Vancouver
    MS-Off Ver
    2016
    Posts
    43

    Re: How to Layout my Table

    Their workouts are more for performance for their athletic students at their school while I am going more towards bodybuilding.

    And with all respect, even if they did have one, I wish to learn how the excel spreadsheet was made so I can create my own that is much simpler to use that fits me.

  71. #71
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to Layout my Table

    Quote Originally Posted by benishiryo View Post
    @Ford:
    hats off to you for persisting through 5 pages of guidance. i'm just going to post this 1 file and scurry away. =D
    Thanks for that and the feedback. It was actually quite fun to work with Brendon, he has been 1 of the most willing and respectful members I have helped. I would often post something, go work in my wood shop for a while, then scurry back to see if he had replied

    I had thought "tables" and PT right from the start, along with a data sheet like you have now, but was trying to not stray too too far from the revised original (see the file in pots #1, I think). I do like how you have laid this out now though.

    Thanks for the assist.

    @ Brendon, if you take a look at the bottoms of benishiryo's signature, you will see a link to a thread with an awesome TIPS file in it. Take a look and see what you think.

  72. #72
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to Layout my Table

    Quote Originally Posted by brendon0224 View Post
    Their workouts are more for performance for their athletic students at their school while I am going more towards bodybuilding.

    And with all respect, even if they did have one, I wish to learn how the excel spreadsheet was made so I can create my own that is much simpler to use that fits me.
    My 2c's worth - while that suggested file may indeed be exactly what is needed, I have always felt that creating something yourself, even with inspiration from other files, is sometimes the better route to go...especially if you are still learning something like excel.

    Still, a very good file to either use or use as inspiration, thanks for that

  73. #73
    Registered User
    Join Date
    06-07-2018
    Location
    Vancouver
    MS-Off Ver
    2016
    Posts
    43

    Re: How to Layout my Table

    @FDibbins thank you very much for the kind words

    I will check out the TIPS files and I have marked the thread as solved as it is getting quite long

    I will attempt to get as far as I can from what I have learnt from here and post a new thread when I come across a new problem!

    I hope to work with you again in the future!

  74. #74
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to Layout my Table

    Thank you. What is of interest in this, is that the others who also helped, all too a slightly different approach, showing that with excel, there are almost always a few different ways to arrive at the same result. Often, it boils down to personal preference - stick around here long enough and you will see some members favor PT's others swear by tables, some prefer the VBA approach and some mainly take the formula route. No 1 approach is right or wrong, and more often than not, none are worse or better than another, either

    Look forward to assisting you again

+ 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. Replies: 5
    Last Post: 03-08-2018, 08:14 AM
  2. Change Table Layout
    By g4m8l3r in forum Excel General
    Replies: 1
    Last Post: 08-22-2015, 12:34 PM
  3. Different layout of a table
    By alfredkri in forum Excel General
    Replies: 3
    Last Post: 12-09-2010, 04:48 PM
  4. Pivot Table Layout changes
    By tek9step in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-24-2010, 12:11 PM
  5. Changing a Table layout
    By Mike2 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-28-2008, 06:47 PM
  6. Pivot Table Layout Help
    By mardman in forum Excel General
    Replies: 1
    Last Post: 08-23-2005, 06: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