For anyone having trouble with the 'Indirect' part, remove spaces from your categories and select the whole list. So you'd type: =INDIRECT($A6:$A9) Very useful video. Thanks!👍
I think I found the issue. I was getting the same error, but I think that the film did not highlight that you selected the A6 cell as your reference cell for the indirect function at some point. I also had a few issues with language - my excel is in European portuguese, therefore I had to type INDIRECTO and not INDIRETO (Brazilian portuguese) or INDIRECT (English). Now it is working really well. I have to thank you because this tutorial is helping me a lot. Will play a bit more with this stuff and decide if I should go a bit deeper.
First people that's having trouble with the indirect function part of the video. You'll have to go into your name manager and have the lists under 1 column. For example: Core =Sheet1!$A$6:$A$9 and not =Sheet1!$A$6:$B$9, hence the letters have to be the same.
Great and simple tutorial man! Just one thing.. You should show us dudes who never used excel before how to export the Training Plans and how to save the layout so for every use, we use the same one.
Hi Ryan, a great option has just come up for what you asked. If you google the following "Mr Excel and excelisfun Excel Videos Play In Excel" you will see a video loaded on June 18th 2013 that does exactly what you were looking for John
Hi. I'm here after 9 years. I'm learning excel tricks. I did all until minute 3:53. But then after that, I don't know how did you get that $A$6. Where did you clic? Did you selected in the column with the categories, a mean selecting from A1 to A6? I can't do the same. Please help =)
Hi Sam. It can be a little hard to know what is going wrong for you but my guess is that you have a simple misalignment with names. You need to create names for lists such as "Push" and "Pull". If you enter the word "Push" in cell A5 and then write a formula =INDIRECT(A5) then it will try to find a named range called PUSH. Check the names of your ranges and let me know if this was a problem. If you need to then send me an email - my address is at the end of each video.
Hello! I love how you set this up! I am running into problems on my mac though. I can get the name ranges okay, and get them into the category section no problem. However, when I go to the exercises and try to use the "=indirect( " I am given an error "The Source currently evaluates to an error" So the category section is correct, my name ranges are all showing up in the define names section... Just the connected exercises on the second sheet are not pulling over. Could this be a change in the new Microsoft excel program??
F3 doesn't work on a windows laptop...what button do I need to press to bring up the source (i.e. upperbody, lowerbody, core etc) please? Thank you so much
I tried doing this word for word and whenever I try to do the indirect list, it wont allow me to select the correct category. When I type =indirect($A6) the drop down only gives me the option to select from whatever category is A6 at the time and not choose from the other lists I created. What is going on with this ?
Can I take the named lists into other excel sheets? Or do I have to save the lists and bring them to other sheets for different clients? I can save the template too and just fill it in I suppose.
Having a list in one sheet/file and the template in another works if both files are open but is ultimately a fragile system. I would suggest always keeping the lists in the same file as the template. Make a program, export as PDF and send is the best method.
Hi, thanks a lot for this useful video but don't know why cannot manage to complete the indirect function; it says that the source currently evaluate an error if you can hep me, could be great to finish my excel sheet! really appreciate it! thanks a lot,
macs still allow for all of the cool validation features but the process is different. Look at videos 18 to 21 on my channel and see if you can make it work,
Is there a way to easily generate a graph for each exercise, i.e compare the weight lifted in squat in workout 1 from week 1 in comparison to weeks 2, 3, 4 and see the increase? Thanks!
Firstly this channel has been a fantastic insight into advancing my programme templates and thank you for sharong your knowledge. Secondly, I have a an issue with the indirect fucntion that you have explained in this video. I have followed the correct protocol however when i press ok I get the message "The Source currently evaluates to an error. Do you want to continue?" And messes up the function. Can you help please!!!!!?????
Did you ever get this solved? Stuck at the same thing. Would appreciate any insight. I realize you typed this 6 years ago and the account may not even be active but thought I'd try. Thanks in advance.
Great Video. I have a question. I have a number of athletes. Each one has their own workbook. How can I create a database of exercises like you have and apply the data validation to each players workbook? I can link the Category to the master exercise database and the individual player's workbooks. But I can't get the indirect to work to find the exercises.
I have done everything absolutely identical to what you did, but I keep getting the error. I have checked spaces, spelling errors, all of it. The Category drop down for LowerBody, UpperBody and Core works fine, but as soon as I want to use the =indirect() function for the Exercises tab, I get the error that my selected area does not exist. I closed it, opened a new sheet and started over - same error over and over again
Can you answer any of the mac questions. I am having the same two issues F3-what will do the same thing (otherwise I figured that one out) . But I can't get the indirect to work either . NOthing shows up in my column.
As far as i know there is no alternative for F3 so you need to remember or write down your names so that you can type them in. The most common reason why indirect doesn't work is that your indirect target is not the same as your named range e.g. Upper Body v a named range called UpperBody
Hi, i also having the same issue with the "F3", MacBook here. And i also tried many times manually key in the exact same words "Category" but still not working. Please HELP!
Hi! This is beautiful! Just having a hard time with the indirect function!... It doesnt allow me to link the exercises with their category... Says: "Origin error, want to continue anyways?" I press OK then nothing shows when I press the arrow of the cell. Can you help me? THX!
+yep87 Yep! I figured it out. First of all, I think it doesn't work in mac. My advice is make EVERY step as shown, every letter, every space, every blank row left in the template. Seems like everything´s a big deal. I erased an extra blank row that I had and it worked. Keep trying!
Hi John, Excellent videos - thanks you! Questions: 1) You have gone Lower Body > To all LB exercises (Squat, Dead etc) which leads to two drop down columns on the Prog sheet. Is it possible to have more than two linked columns e.g. Lower Body > Squat > Double Leg > All DL exercises (4 in this instance) to help me filter the exercises more quickly without having to trawl through all LB possibilities? 2) Is there a cap on the number of exercises (cells) in a list? I notice on your website in the $30 package you mention a "list of 300 exercises possible", I assume this was just a parameter you set not a limit of Excel correct?
Yes you can have multiple levels of drop down box. It takes a little bit of setting up but is worthwhile if you have very big lists of exercise options. Excel will allow you to have as many exercises as you like in a list so there is not really any limits (other than your ability to scroll). In another project i have a drop down box with 800 items.
ExcelTricksforSports Hello, thanks for the video venomprotein.com.au/catalog/supleanmuscle.php Please visit this site and go to: Excercise Plan: Click here to download your Workout Plan. They have a workout plan, and I was trying to make my own like the one they have. Can you please make a video on how to make the workout plan provided by that website? You have practically done half of it in this video, only the one in this site has graphs linked to it. If not, thanks anyway for this video :)
@exceltricksforsports I am using Google Docs Excel and cannot get the exercises to register to the category chosen. The indirect formula doesn't seem to work. Any tips?
Hi there, i am not sure if there is an equivalent function in google sheets to allow drop down boxes to link to each other - when i googled this i got some hits that might be worth looking at "google sheets equivalent function to indirect"
This video has been invaluable to me. I have a question, though. If I have hyperlinks to all of my exercises (or categories) to UA-cam playlists or videos can I make them appear in the category lists as possible selections, and then show up in the cells as hyperlinks? I tried but it did not work. Thanks again for the great videos!
I followed everything step by step. When i get to the =indirect() function it says that the source currently evaluates an error. I started again only to have the same outcome. Please help. Thanks
Hi exceltrick you can explain me about this function =IF(FIND("woven",A6),($F$4*0.05+$F$4),IF(FIND("knitted",A6),$F$4*-$F$4*0.05)) when i type description -men's 100% polyester woven pants . the result number that's correct but when i type -men's 100% polyester knitted pants . the result that's #VALUE! please help me to explain about this function thank.
Hi @ExcelTricksForSports. I have everything complete, until I try to do the indirect fuction on exercises colume on my program sheet. I get an Error box stating: Source currently evaluates to an error. can you help me out please? Great Page...
I have a video on my page that outlines some of these concepts. I hope it helps it is ok if it "currently" evaluates to an error because when you select the category it will make it not evaluate to an error and it should work
@@JohnLythe Thanks mate! I tried that but couldn't get it to work. i used an = sign before category and it showed up. Now i have a "the source currently evaluates to an error" when trying to make an indirect. I'm such a rookie!
For anyone having trouble with the 'Indirect' part, remove spaces from your categories and select the whole list. So you'd type: =INDIRECT($A6:$A9)
Very useful video.
Thanks!👍
I can not thank you enough.... I hope you understand how much we appreciate this free help... Thanks infinitely!
I think I found the issue. I was getting the same error, but I think that the film did not highlight that you selected the A6 cell as your reference cell for the indirect function at some point. I also had a few issues with language - my excel is in European portuguese, therefore I had to type INDIRECTO and not INDIRETO (Brazilian portuguese) or INDIRECT (English). Now it is working really well. I have to thank you because this tutorial is helping me a lot. Will play a bit more with this stuff and decide if I should go a bit deeper.
Great tutorial. Exactly what I needed to learn to create my training journal. Cheers
First people that's having trouble with the indirect function part of the video. You'll have to go into your name manager and have the lists under 1 column.
For example: Core =Sheet1!$A$6:$A$9 and not =Sheet1!$A$6:$B$9, hence the letters have to be the same.
Amazing tutorial. Just looking up some ideas to journal my progressive overload because I don't want notebooks anymore lol
You’re a freaking legend for this🙌
This is brilliant mate, thank you!
This was just what I needed
11 years late and its still a great video amazing tutorial thanks
Great and simple tutorial man! Just one thing.. You should show us dudes who never used excel before how to export the Training Plans and how to save the layout so for every use, we use the same one.
Thank you so much for great help my friend.
Brilliant! Thanks for sharing!
Hi Ryan, a great option has just come up for what you asked. If you google the following "Mr Excel and excelisfun Excel Videos Play In Excel" you will see a video loaded on June 18th 2013 that does exactly what you were looking for
John
great video, i need it. thank you mister
Absolutely brilliant!
I have two different sheets and cannot seem to get mine to work while using the indirect, there is no space between categories
Just wondering why a6? Mines working only for lower body but won’t give me the rest ? Great video though thankyou so much
Excelente, uma grande dica,valeu...obrigado.
Hi. I'm here after 9 years. I'm learning excel tricks. I did all until minute 3:53. But then after that, I don't know how did you get that $A$6. Where did you clic? Did you selected in the column with the categories, a mean selecting from A1 to A6? I can't do the same. Please help =)
A6 is the cell that has the category/muscle group selection in it e.g. If cell A6 had UpperBody
tells me to enter a valid reference to go to. When trying to link the lower body to the lower body category
Hi Sam. It can be a little hard to know what is going wrong for you but my guess is that you have a simple misalignment with names. You need to create names for lists such as "Push" and "Pull". If you enter the word "Push" in cell A5 and then write a formula =INDIRECT(A5) then it will try to find a named range called PUSH. Check the names of your ranges and let me know if this was a problem. If you need to then send me an email - my address is at the end of each video.
2:35 how do you do this on a MAC since theres no F3
That's exactly
where I am stuck at right now...
You can just write the name of the list in the source, for example '=Category'
Hello! I love how you set this up! I am running into problems on my mac though. I can get the name ranges okay, and get them into the category section no problem. However, when I go to the exercises and try to use the "=indirect( " I am given an error "The Source currently evaluates to an error" So the category section is correct, my name ranges are all showing up in the define names section... Just the connected exercises on the second sheet are not pulling over. Could this be a change in the new Microsoft excel program??
Your problem is most likely due to spaces in the names - instead of upper body you need to type upperbody etc
@@JohnLythe That worked perfectly I had a few places confused, thanks so much!
F3 doesn't work on a windows laptop...what button do I need to press to bring up the source (i.e. upperbody, lowerbody, core etc) please? Thank you so much
+Todd Davidson Use Google Docs and Drive and click data name range
I tried doing this word for word and whenever I try to do the indirect list, it wont allow me to select the correct category. When I type =indirect($A6) the drop down only gives me the option to select from whatever category is A6 at the time and not choose from the other lists I created. What is going on with this ?
You need to have a category defined in cell A6 for this to work.
Can I take the named lists into other excel sheets? Or do I have to save the lists and bring them to other sheets for different clients? I can save the template too and just fill it in I suppose.
Having a list in one sheet/file and the template in another works if both files are open but is ultimately a fragile system. I would suggest always keeping the lists in the same file as the template. Make a program, export as PDF and send is the best method.
Good stuff
Hi, thanks a lot for this useful video but don't know why cannot manage to complete the indirect function; it says that the source currently evaluate an error if you can hep me, could be great to finish my excel sheet! really appreciate it! thanks a lot,
Did you find out how?
macs still allow for all of the cool validation features but the process is different. Look at videos 18 to 21 on my channel and see if you can make it work,
Is there a way to easily generate a graph for each exercise, i.e compare the weight lifted in squat in workout 1 from week 1 in comparison to weeks 2, 3, 4 and see the increase? Thanks!
Yes but you would need to create a data sheet to store loads lifted over time
John Lythe idk how to do that. I guess i could select each cell manually but that’s kinda long winded
Do you have this tutorial for google sheets?
Firstly this channel has been a fantastic insight into advancing my programme templates and thank you for sharong your knowledge.
Secondly, I have a an issue with the indirect fucntion that you have explained in this video. I have followed the correct protocol however when i press ok I get the message "The Source currently evaluates to an error. Do you want to continue?" And messes up the function.
Can you help please!!!!!?????
Did you ever get this solved? Stuck at the same thing. Would appreciate any insight. I realize you typed this 6 years ago and the account may not even be active but thought I'd try. Thanks in advance.
good vid
Great Video.
I have a question.
I have a number of athletes. Each one has their own workbook. How can I create a database of exercises like you have and apply the data validation to each players workbook?
I can link the Category to the master exercise database and the individual player's workbooks. But I can't get the indirect to work to find the exercises.
extremely helpful!
Hi how can I auto fill the cells when I type in for example a certain exercise so I don’t have to keep scrolling ?
Thanks
Named ranges cannot have spaces. Upper Body needs to be UpperBody etc. Then it should work just fine.
I have done everything absolutely identical to what you did, but I keep getting the error. I have checked spaces, spelling errors, all of it. The Category drop down for LowerBody, UpperBody and Core works fine, but as soon as I want to use the =indirect() function for the Exercises tab, I get the error that my selected area does not exist. I closed it, opened a new sheet and started over - same error over and over again
Exactly!!!
got the same problem
How do I use the f3 function on a mac?? I'm stuck trying to enter the data validation
Can you answer any of the mac questions. I am having the same two issues F3-what will do the same thing (otherwise I figured that one out) . But I can't get the indirect to work either . NOthing shows up in my column.
As far as i know there is no alternative for F3 so you need to remember or write down your names so that you can type them in. The most common reason why indirect doesn't work is that your indirect target is not the same as your named range e.g. Upper Body v a named range called UpperBody
Hi, i also having the same issue with the "F3", MacBook here. And i also tried many times manually key in the exact same words "Category" but still not working. Please HELP!
Same problem as marcusse777...identical. Is it possible that Excel 2013 do things little bit differently?
Done everything till i got to the Paste Name box and now im stuck, cant open the box on my Mac, any help?
Excel for Mac doesn't have all the same commands and functions. Some of the short cut keys are also different.
Where can I find the source excel to download?
this is fun tbf
the F3 is not working .wt shld i do?
Hi! This is beautiful! Just having a hard time with the indirect function!... It doesnt allow me to link the exercises with their category... Says: "Origin error, want to continue anyways?" I press OK then nothing shows when I press the arrow of the cell. Can you help me? THX!
+Javier Ignacio Villavicencio Marchant Did you ever get this figured out because I am having the exact same problem
+yep87 Yep! I figured it out. First of all, I think it doesn't work in mac. My advice is make EVERY step as shown, every letter, every space, every blank row left in the template. Seems like everything´s a big deal. I erased an extra blank row that I had and it worked. Keep trying!
What can I press instead of f3 that option isn't working on my Mac
Did anyone find a substitute on google sheets for the indirect function?
Hi John, Excellent videos - thanks you! Questions:
1) You have gone Lower Body > To all LB exercises (Squat, Dead etc) which leads to two drop down columns on the Prog sheet. Is it possible to have more than two linked columns e.g. Lower Body > Squat > Double Leg > All DL exercises (4 in this instance) to help me filter the exercises more quickly without having to trawl through all LB possibilities?
2) Is there a cap on the number of exercises (cells) in a list? I notice on your website in the $30 package you mention a "list of 300 exercises possible", I assume this was just a parameter you set not a limit of Excel correct?
Yes you can have multiple levels of drop down box. It takes a little bit of setting up but is worthwhile if you have very big lists of exercise options. Excel will allow you to have as many exercises as you like in a list so there is not really any limits (other than your ability to scroll). In another project i have a drop down box with 800 items.
ExcelTricksforSports Hello, thanks for the video
venomprotein.com.au/catalog/supleanmuscle.php Please visit this site and go to: Excercise Plan: Click here to download your Workout Plan.
They have a workout plan, and I was trying to make my own like the one they have. Can you please make a video on how to make the workout plan provided by that website? You have practically done half of it in this video, only the one in this site has graphs linked to it.
If not, thanks anyway for this video :)
@exceltricksforsports I am using Google Docs Excel and cannot get the exercises to register to the category chosen. The indirect formula doesn't seem to work. Any tips?
Hi there, i am not sure if there is an equivalent function in google sheets to allow drop down boxes to link to each other - when i googled this i got some hits that might be worth looking at "google sheets equivalent function to indirect"
ExcelTricksforSports thank you.
This video has been invaluable to me. I have a question, though.
If I have hyperlinks to all of my exercises (or categories) to UA-cam playlists or videos can I make them appear in the category lists as possible selections, and then show up in the cells as hyperlinks?
I tried but it did not work.
Thanks again for the great videos!
did you ever figure this out? you can use a hyperlink function the only problem i have is that if you save as PDF the links do not always stay active
Hi Guys I am also interested to know. If you found a solution it would be of great assistance
I followed everything step by step. When i get to the =indirect() function it says that the source currently evaluates an error. I started again only to have the same outcome. Please help. Thanks
I had the same issue. How were you able to fix this?
doesn't work, "the source currently evaluates to an error" 4:09
Remove spaces between words (e.g. Upper Body). Item selected in category picklist must match the range name
Hi exceltrick
you can explain me about this function
=IF(FIND("woven",A6),($F$4*0.05+$F$4),IF(FIND("knitted",A6),$F$4*-$F$4*0.05))
when i type description
-men's 100% polyester woven pants . the result number that's correct but when i type
-men's 100% polyester knitted pants . the result that's #VALUE!
please help me to explain about this function
thank.
Where could I find the name manager on a Mac? Thanks
Insert>Name>Define and modify,add,or delete data ranges names.
I have excel 2007. Can you send me a download of this spreadsheet which will work with 07?
Hi @ExcelTricksForSports. I have everything complete, until I try to do the indirect fuction on exercises colume on my program sheet. I get an Error box stating: Source currently evaluates to an error. can you help me out please?
Great Page...
I have a video on my page that outlines some of these concepts. I hope it helps
it is ok if it "currently" evaluates to an error because when you select the category it will make it not evaluate to an error and it should work
DSMStrength can you help me i have this problem !?
Thank you!!!!
Does anyone know a way to get validation to work without F3? I have a PC and F3 does not do anything. Thanks!
you can type in the named range - just make sure you spell it correctly (no spaces!). The F3 trick is just a shortcut so you dont need to use it.
@@JohnLythe Thanks mate! I tried that but couldn't get it to work. i used an = sign before category and it showed up. Now i have a "the source currently evaluates to an error" when trying to make an indirect. I'm such a rookie!
Thanks.
i cant get the indirect function to work in google sheets anybody???
send an email to athletefitness[at]gmail.com and i will hook you up
Thank you!!