Google Sheets Athlete Dashboard | Part 2 | Headers & KPI Charts

[@stefanosgiogos7618]

Dude... You are amazing! Thank you for the value you give us! I have just saved hundred of hours trying to create a monitoring system for my athletes, just by watching carefully and using your tips! Keep up the great work! The world needs people to contribute, just like you! Regards from Greece!

[@DSMStrength]

Wow thank you

[@stephanegervais8372]

Awesome!!! Done ✅ Ready for Part 3

[@DSMStrength]

Yes man!!!

[@CCalistoTraining]

This is awesome. That’s a lot of good info here. Thanks

[@AnapeaxDiagnostics]

Hi Dave. It is great Google Sheet KPI :) Could you help me? How to create Team Min and Team Max per female and male separately for the same Data sheet? in Set up test (video time 12:25)?

[@waynechilds1933]

Love this and am excited to get started on it. I have completed everything through video two. My problems is my dates on my first KPI Chart of going left (Newest Test) to Right ( Oldest Test). Any easy way that I can flip it so it reads right to left. Thanks again.

[@DSMStrength]

Yeah first off thanks for watching To do that just wrap your formula in a sort()

[@waynechilds1933]

@@DSMStrength Thank you for the quick reply. It worked like a charm. Have a great evening and thank you again.

[@DSMStrength]

@@waynechilds1933 thanks for watching... tell your friends lol

[@waynechilds1933]

​@@DSMStrength new problem my charts are sorted by date now. However, the more weight a person does the lower the bar graph is. So as the person shows growth the line is trending down instead of up. Any ideas where I might have screwed up? Thanks again.

[@waynechilds1933]

Never mind. Hunt and pick and I found my error. Sorry to bug you.

[@Glute_Expert]

Hi Dave, in the sheet "DataReference" i have a doubt. the "Min" & the "Max", which values the equation will bring the minimum/maximum of the team or the athlete? Min 15:50 Amazing Work thanks

[@DSMStrength]

Hi rodrigues, I am not quite sure what you are asking me?

[@Glute_Expert]

@@DSMStrength In Table KPI CHART 1 you choose, athlete, then he will bring automatically the position and the start date because of the formulas that you make previously. My question is, for example the test "CMJ" the values "Min&Max" that will appear is reference to the all team or just the athlete "Taylor"? ( that you have in Data Sheet) I dont know if you understand what im want to ask :/ thanks

[@DSMStrength]

@@Glute_Expert Hey Rodrigues in video #1 in this series we go through the "test set up" tab which we outline what min / max values we want for each test. For this project I am basing things off of the team scores not the individual athlete

[@robnelson8044]

Hello, First of all I wanted to say thank you for making these videos, they're extremely helpful. I am the portion for the min and max at the beginning of the video and every time I enter in the formula I get "0" for the min and "0" for the max and when I switch to a different test in the drop down box I get "#N/A" and I was hoping you could help me. I'm not sure why the mins and maxes are not showing up, but I've done this on two separate sheets and had the same problem. Thanks again!

[@DSMStrength]

What is your formula?

[@robnelson8044]

@@DSMStrength =min(index(Data!A2:Q,,match(A2, Data!1:1)))

[@DSMStrength]

@@robnelson8044 try this Index(data!a2:Q, ,match(A2, data!1:1, false))

[@robnelson8044]

@@DSMStrength that worked! thank you so much! Seriously this is an amazing resource that you provide

[@DSMStrength]

@@robnelson8044 glad to hear it thanks for watching.. please share the content if you can it really helps the channel

[@troymorris6001]

Thank you sharing knowledge with these athlete dashboards. They have been a game changer. I'm having trouble with creating the array and mround formula. I keep getting an error message.

[@sydneysellscullman]

Thank you for this! Is there an easy way to have every athlete's data and dashboard go to their own Google Sheet rather than using a dropdown menu? Wanting to share data with parents, but don't want them to have access to others' data.

[@DSMStrength]

I would recommend just making a sheet for each athlete.. there is not a easy way to do this.

[@tanyarobinson275]

Hi Dave, Im doing an overall dashboard for a previous sporting season. How can create graphs like you shown comparing the data to individual players then as well positions rather then dates

[@DSMStrength]

shoot me an email with like a sketch or something of what you want to show i can better help you.. really hard to know without seeing an example. [e-mail]

[@KateRitchie-p4z]

Hi there! Love your video's. Just at the 19 min mark pulling the dates etc. I have found that although I have put in the exact same formula however when changing the drop down, it is not actually pulling the date that is greater than or equal to that of the one selected. Not too sure what I am doing wrong however it is currently just shooting out random dates instead of what is it supposed to. From Australia so not sure if it is a formula typo that I need to fix. Currently formula entered is =FILTER(index(Data,,MATCH("Date",Headers,)),INDEX(Data,,MATCH("date",Headers,))>=B4)

[@bryanmizell8529]

Hi Dave, When I am doing the min and max setup at 9:00 or so in the video, its not populating the correct data for each test. The data pulls the correct pro agility numbers for me, but for other columns such as vertical, CMJ, etc. its not pulling the test info into the max and min columns. Any idea how I can fix this? Making it hard to progress through the other videos when those numbers aren't populating correctly

[@DSMStrength]

Sounds like you are missing some $ when you drag your formula

[@coldavenue2325]

Could you show us how to make a dashboard for a stock's watchlist?

[@speedfactorybysaharshshah3256]

This is so user friendly.. I just had a problem with using the "team min" and "team max" in "text setup" worksheet. When I am using the same formula, for some of the selected tests, it gives an error saying "Did not find the value in match function" and for the other tests that I select from the drop down list, some haywire data turns up. Could you please help me solve it. I can also share the datasheet on your mail id if you want. Thanks.

[@speedfactorybysaharshshah3256]

Opps, I figured it out! It was a comma that I missed at the end!! Fuck!! this took me ages to realize....

[@DSMStrength]

@@speedfactorybysaharshshah3256 Glad you got it figured out... thats the annoying thing with these projects it can be one little thing that makes everything not work!

[@adrianpilkington8954]

@@DSMStrength I am having the same above issue. could you tell me where I am going wrong please ?

[@DSMStrength]

@@adrianpilkington8954 sounds like something as simple as a mistyped formula, or perhaps an extra space somewhere, or a name that does not match.. recheck your formula and matches

[@arielle1546]

Thanks for a fantastic tutorial! I'm having issues with my graph not showing the first value in the correct date and is instead showing that value in the next date. Could I get some assistance? Thanks

[@DSMStrength]

Hi Arielle I'd make sure that you = signs are right.

[@ermogenescampos6300]

I believe that because it is a dashboard for a sports team, this dashboard is not adaptable for a personal trainer. The data I enter are body weight, BMI, fat percentage etc. and it will be necessary to insert some calculations for women and men.

[@DSMStrength]

it should work fine for personal training provided that you set up your data appropriately. Its hard for me to help without seeing it.

[@ermogenescampos6300]

@@DSMStrength How can I send you my spreadsheet? If it doesn't bother you. But it is in Portuguese, I believe it is a problem, I don't speak English, I understand some things but I don't speak your language

[@JakeBanegas]

Amazing work! & I have a question coach, when I am at 21:30 and getting the curly brackets filled etc so it can only be the data points for the "Athlete Name" it still brings up other athletes data and multiple data even when shes logged twice. Any suggestions? Would love to hop on a consultation call too!

[@mdibiasi0301]

One area I noticed for me was I had to check to make sure where he says "B2" you do not have ">="...it needs to say "=B2". Hope that works for you.

[@elite_level_conditioning]

@@mdibiasi0301 thanks for this! I also made this mistake.

[@johanasberg1281]

Thank you for great content. I have trouble when I add the {} brackets. It is working when I pull only the dates but when I add the brackets to also include Test I get the following: "FILTER has mismatched range size error".

[@DSMStrength]

Hey Johan I believe we answered this through email but for others if you are using gsheets in a country that uses , for numbers then instead of using , in the formula you will need to use ; and /

[@lic.franciscodiberti]

Hola, estoy siguiendo el paso a paso, pero al insertar la formula del minuto 20:19 me figura error, en donde dice que el tamaño de intervalo no coinciden. me pueden ayudar ? gracias

[@ermogenescampos6300]

Dave, I am still in the step by step of video 2, the values do not appear in my cell, excel says that the filter has no corresponding intervals.

[@DSMStrength]

Hi ermogenes, It is hard for me to say without seeing your sheet, is everything set up the same way? as long as you have names and dates in one column, and then each column has its own data it should work fine.

[@janhejny996]

Hi @DSMStrength, Fantastic video, but as is repeated here, I have a problem with the longest formula, which starts in the video in about 20 minutes - DataReference - I seem to have a problem with the Data column. It still appears as "FILTER has incorrectly positioned ranges." Expected number of rows: 999. Number of columns: 1. Actual number of rows 1: 1, Number of columns: 1 “, but I use exactly the same equation as you. I checked the ranges: DATA = A2: Z1 (final column), Headers = 1: 1. I tried to change the brackets {} → //, \\ and I use ";" all the time because it worked all the time. I tried to expand the formula with "false" and other advice that is here under the video and nothing worked. I followed the whole procedure from the video and so far everything has worked perfectly. Thank you.

[@DSMStrength]

Hi Jan change ur data from a2:z1 to a2:z

[@Schmazy]

Thanks for the video! I'm having trouble with 12:30. My initial row shows the testing results, but when I drag it down I get no testing numbers from any tests.

[@Schmazy]

Found the issue, when I pulled the formula down #REF! was put in my formula

[@BálintRuppert-v8k]

Hello, I get this message when i write the long formula: Error FILTER has mismatched range sizes. Expected row count: 1988, column count: 1. Actual row count: 994, column count: 1. Could you help me? What is the solution?

[@thomasmyslinski2763]

Dave, in the "DataReference": I seem to be having trouble with the Dates column. It keeps showing up as "FILTER has misplaced range sizes. Expected row count: 999. column count: 1. Actual row count 1: 1, column count: 1" but I'm using the same exact equation that you are. Have you ran into that issue before and if so could you provide some clarity on it? Appreciate this entire series!

[@DSMStrength]

Thomas thank you for watching.. depending on how you are pulling some things to check 1. do you only have 1 entry for that athlete on that date? 2. are your references the same for your date and data EX: are you going A2:A and not A1:A on one of them and B2:B on the other

[@landryestes6607]

Hello, thanks for such a helpful video. I am having the same problem as Thomas described above. I have checked my named ranges and they match your identically, however, most of my athletes do only have one entry per date. How does this change the formula? When I follow the first step I get all dates after my selected date for every athlete, it’s getting dates for just the athlete listed in B2 that is the problem. Any help would be appreciated!

[@personallpeters]

Hello, I was having a problem that I don't know if anyone else has: My long formula wouldn't work when I tried to put the multiple ranges between the curly brackets, and I didn't know why. After searching I lot, I found out that in languages that use the comma to separate decimal numbers (like Portuguese in my case) you need to separate the ranges with a "\". Now it's working fine :)

[@DSMStrength]

Yes I have helped many with this issue.. believe I addressed it in the comments many times. Thank you for your comment very helpful!

[@personallpeters]

@@DSMStrength Aww sorry I didn't see it! btw your videos are awesome, have been helping a lot !

[@DSMStrength]

@@personallpeters thank you so much for watching and helping. I plan to do some future videos outlining the syntax from other countries

[@matkovukovic5426]

Thanks a lot!

[@sebastianmartinez-kinesiol4810]

Hello everyone! I have G Sheets in Spanish and I have this same problem. I tried to change the curly braket for the \ and I was not successful. Any ideas? Currently my function is like this (it's Spanish). =FILTER({INDEX(Data; ;MATCH("date"; Titles;)); INDEX(Data; ;MATCH(B5; Titles;))}; INDEX(Data; ;MATCH("date"; Titles;)) >=B4; INDEX(Data; ;MATCH("name"; Titles;))=B2)

[@adrianpilkington8954]

Hi Dave Great content thank you. When I get to the part where i need to do the min and the max, I can get the data for the first test I choose in A2 cell but when I go to pick another test or copy the formula down it is giving me a #NA. Any Help would be greatly appreciated.

[@DSMStrength]

it is very hard for me to diagnose these problems without seeing the sheet. Sounds like either your match does not work correctly, or your formula is not typed correctly or referring to the right cells

[@adrianpilkington8954]

@@DSMStrength yea I'm not sure I've literally followed your video right up to this point with out any issues. I have redone it at least ten times now and still same issues it's actually quite frustrating at times cos I have genuine feeling it's going be something so small to fix

[@DSMStrength]

@@adrianpilkington8954 make sure you have the $ in the right spots so that when you drag you are not breaking your references

[@samelsner7736]

When is part 3 being uploaded?

[@DSMStrength]

Hey Sam! I am aiming for monday but will release sooner if I can get it completed!

[@tomasmount2335]

Hi Dave, These videos are great, thanks so much. I am having a problem with the values section. When I type in the formula it keeps changing the dates column. Could you help with this please Kind Regards Tomas

[@DSMStrength]

Hi Thomas, I am not sure what the issue is, I would need to see your sheet?

[@adrianpilkington8954]

Hi Dave, When Im working on my KPI Chart Number one and input the formula in the dates column it is telling me that it did not find 'date' in the Match valuation ? Would you have any idea how to fix this please Regards Adrian

[@DSMStrength]

Check for spelling or extra spaces

[@mattjohnson8044]

Hey Dave, Thanks so much for this awesome resource. Have been following along with no hiccups up to this point. Hit my first one today. At the 20:00 min mark when I hit enter I am receiving the error message "FILTER has mismatched range sizes. Expected row count: 887. column count: 1. Actual row count: 1, column count: 1." in the Date cell. Any thoughts here? Thanks so much.

[@mattjohnson8044]

@DSMStrength Formula for that cell.... =filter(index(Data, ,match("Date", Headers,)), index(Data,,match("date", Headers,))>=B4, index(Data,,match("Athlete Name",Headers,))=B2)

[@DSMStrength]

so basically that error means that you are trying to return something that it thinks should be pulling the whole data but you are only giving it 1 row of information. try after Headers, put false in each of your match criteria assuming that they are all pointing to the right spots. The other thing to be sure of is that your "athlete name" does not have any extra spaces in it in your headers row in your data sheet or things like that.

[@MichHamlin]

Hi, I am filling along and at 11 minutes, I have issues with getting it to load. By chance do you offer to sell this excel sheet?

[@michellehurzeler7476]

First of all, thank you for that great content. I have some issues with the long formula, my google sheet is giving me the #NV mistake.. is there any possible you could help me out with that ? I would highly appreciate that ! thank you!

[@DSMStrength]

Michelle thanks for checking out the content.. you may need to use a \ instead of a , if you are using ; throughout your formula I just found out that different regions have different requirements for formulas

[@michellehurzeler7476]

@@DSMStrength thank you!

[@santiagofilippini272]

@@DSMStrength Hi, this tutorials are really helpfull! Thanks a lot for doing this! I have the same mistake than others, I'm using ; correctly but when i use \ it says #ERROR! My formula is: =FILTER(\INDICE(Datos; ;COINCIDIR("Fecha"; Titulos;)); INDICE(Datos; ;COINCIDIR(B5; Titulos;))\; INDICE(Datos; ; COINCIDIR("FECHA"; Titulos;))>=B4; INDICE(Datos; ; COINCIDIR("NOMBRE ATLETA"; Titulos;))=B2 ) It's in spanish but the formulas are the same. Thank you!

[@MrNatec521]

Hello, When I was setting up the Team Min and Team Max under "Test Set Up", there were numbers that autofilled. I listed the Front Squat (A3) as a 405. The Team Min autofilled to 44676 and the max to 44874. These numbers also autofilled in the MPH (A10). I've been stuck for 2 days and can't seem to find the problem. Do you know where this could have stemmed from?

[@zachkerkow-provo8860]

Hey Coach, ive been stuck on this the long formula for two weeks and have looked through all the comments to try and find the answer, but I am getting a "Formula Parse Error" and cant figure out how to fix it. I believe my named ranges are correct and im pretty sure the equation is the same as what you have posted. Any help would be great. Thank you

[@shravanivaidya5365]

Hello, I am able to use the filter formula for the date. However when I add the name condition to it, it still shows me dates for all the athlete instead of the one that is selected. I don't know what is going wrong. Could you please help me out.

[@mattinbc007]

Hey Dave, thanks again for the great content. If I was making this dashboard for a smaller group and wanted to use an individual's Min/Max values on each test vs. the min/max of the entire dataset, what could I add to the formula to do so? So far I have this but it's not working: =INDEX(MATCH(Dashboard!B2,'KPI Data'!A2:A,0) ,MIN(INDEX(Data,,MATCH(A2,Headers))))

[@DSMStrength]

yeah you would need to do the same steps as the test set up, just match the row for the athletes name.

[@mattinbc007]

@@DSMStrength Somehow when I try to add another match function to go from this: =MAX(INDEX(Data,,MATCH(A2,Headers,))) To This: =MAX(INDEX(Data,MATCH(Dashboard!B2,'KPI Data'!A2:A,MATCH(A2,Headers,)))) It matches the data to the athlete name, but no longer matches the column based on test, and instead pulls out the highest or lowest value from the entire dataset. Happen to see anything I'm missing? Sorry for the trouble!

[@mattinbc007]

For anyone going through this series wanting to make dashboards for smaller groups/individuals, I finally figure out a formula to pull out individual MIN/MAX: =IF(A2="",,MIN(TRANSPOSE(QUERY(TRANSPOSE({'KPI Data'!$E$1:$V$1;FILTER('KPI Data'!$E$2:$V,'KPI Data'!$A$2:$A=Dashboard!$B$2)}),"where Col1='"&A2&"' ",0))))

[@DSMStrength]

@@mattinbc007 matt why not just do something like maxif(index(value), index(name) = name)

[@mattinbc007]

@@DSMStrength because I am terrible with google sheets. Thank you, much simpler!

[@alexmelendez7280]

I've been watching your videos for a few years now, this is awesome. Appreciate all the resources you've been putting out throughout the years! I have recreated my own sheets thanks to your videos and made changes to accommodate my needs. I have a question for the formula at 21:00 (filter for dates and value); how would this be written for excel? I was able to convert most formulas from google sheets to excel, but I'm stuck here. Not sure what the correct syntax or substitute would be for the array portion. =filter({index(Data,,match("Date",Headers,)),index(Data,,match(B5,Headers,))} doesn't

[@DSMStrength]

maybe I can do a video in the future recreating some of these projects in excel so that you can figure that out

[@matthewcowley8646]

Hi Alex! I have been stuck on this formula as well. Were you able to figure this out by any chance?

[@radimhruska5874]

I follow the video step by step, but the values ​​do not appear. The part in which I want to insert a long function and insert data below Dates and Values, but when I click enter It appears in my cell: The FILTER function has mismatched sizes. Expected number of rows: 1998, columns: 1. Actual number of rows: 999, columns: 1. I dont know, what is wrong? Thank you!

[@DSMStrength]

Make sure the ranges you are referencing are the same size

[@radimhruska5874]

@@DSMStrength still not working :(

[@ernrydshealthperformance7835]

Hi! I have the same problem, did you find out how to fix it?

[@DSMStrength]

@@ernrydshealthperformance7835 if your data is set up the same way as the video I assure you the formulas will work. It is hard for me to diaganose the problem without seeing how things are set up. Often its a spelling mistake, or comma in the wrong place

[@DSMStrength]

@Ernryds Health and Performance so essentially it should look like this =filter({range 1, range2}, condition 1, condition2 ... etc) range 1 and 2 need to be the same size (length)

[@yonigivoni]

Nice!!!

[@DSMStrength]

Thank you

[@ReedJ26]

How do you get the values cells to fill out when doing the filter function for test scores? Im working from excel not sheets but all that ends up happening every time i try to add another array for tests is it screws the date up rather than displays the value for the test

[@DSMStrength]

This will not work in excel

[@ReedJ26]

@@DSMStrength do you have any recommendations for changing the formula or achieving a similar result through a different method in excel?

[@ReedJ26]

@@DSMStrength nevermind i have figured it out. I copied the filter formula into the value tab and edited the array to return the test results instead of the date

[@matthewcowley8646]

@@ReedJ26 Hi! Were you previously stuck on this formula? =filter({index(Data,,match("Date",Headers,)),index(Data,,match(B5,Headers,))}

[@Tprosser003]

Hi, have you ever tried to use a Google form for populating the Data sheet? Do you think this would require any changes to the filter formula as I think form questions that aren't filled in don't populate as true blanks?

[@Tprosser003]

Think I've stumbled into the answer. I swapped the last criteria to not(isblank(index(Data,,Match(B5,Headers,)))) instead of "index(Data,,match(R5,Headers,)"")"

[@DSMStrength]

I would use an import range and pull your form data from wherever it is being collected with some conditions

[@joshbracken4185]

What if instead of using dates i wanted to use weeks how would use the formula; =FILTER(INDEX(Data, ,MATCH("Week", Headers,)), INDEX(Data,,MATCH("WEEK",Headers,))>=B4,INDEX(Data,,MATCH("Athlete Name",Headers,))=B2)

[@jaimerodriguez6023]

Thank you for the video. I am struggling to figure out how to get my chart y axis to hold a standard ranking of 0-100. It only goes as high as 80 or changes all together. I followed the formula for Standard but still not working.

[@DSMStrength]

Sounds like you just need to set the min max values in your chart

[@sohailaishak7104]

Hi .. may i know what meaning of "team min and team max".. and y whenever u click the checkbox, the num is change? i really dont understand. Tq

[@gritathleticperformance3034]

Hey this is a great resource! Thank you so much. I do have one question... When creating my graph, I have my Dates in the X-axis, and standards as the series with Values as the Label. But as soon as i change the Data range from A8:A12, to A8:A and so fourth... it no longer lets me choose "Standards" as an option for the series? Do you know how i can resolve this?

[@DSMStrength]

I believe this is because you will be including null or empty values by choosing the entire column. what is the goal? you should likely choose your dates to be shown as "text" if you only want them to show the dates that you have

[@ivangadea9185]

hi, im in minute 21;55 and apears and error thas sais that FILTER has mismatched interval sizes. Expected number of rows: 1998. Number of columns: 1. Actual number of rows: 999. Number of columns: 1. How can i fixed it? thank you

[@AlvaroEntrenamientos]

Hello, By adding curly brackets to the formula, google sheet tells me: FILTER has mismatched range sizes. Expected row count: 1998. column count: 1. Actual row count: 999, column count: 1. Do you know how to fix this ?

[@DSMStrength]

This means that you are trying to match for something that does not equal the ranges you've selected. Check your ranges and make sure they are all the same size

[@AlvaroEntrenamientos]

Thanks so much!

[@femkehellinx180]

@@DSMStrength where do I check these ranges? :(

[@michellehurzeler7476]

Hey , is it still possible to purchase this template ?

[@DSMStrength]

Absolutely as well as the new one. link is here: www.dsmstrength.com/product-page/google-sheets-athlete-dashboard Thank you for watching!

[@juancarlosentrenador]

Hello! Regarding the great formula of the video, everything works for the athlete that was in the beginning, but when I change the athlete I get this error "No matches found in the FILTER evaluation". Any advice?

[@juancarlosentrenador]

It is fixed, there was an space more added to the name on one athlete!

[@javiercalderon7384]

I have a doubt with the formula: When I perform the formula: =FILTER({INDEX(Datos; ;MATCH("Fecha"; Encabezado;false))/ INDEX(Datos; ;MATCH(B5; Encabezado;false))}; INDEX(Datos; ;MATCH("Fecha"; Encabezado;false))>=B4; INDEX(Datos; ;MATCH("Nombre Atleta"; Encabezado;false))=B2; INDEX(Datos;;MATCH(B5; Encabezado; false)) "") I get the date in the data column but I don't get the values

[@lic.franciscodiberti]

Javier, pudiste resolverlo ? estoy en el mismo paso y con la misma dificultad

[@anthonypenalber3919]

How would it work if I had 3 test dates and 15 athletes. confused on your testing dates.

[@DSMStrength]

the same way

[@ermogenescampos6300]

The part in which I want to insert the dates, values ​​and standard to insert in the graph is giving error. I follow the video step by step, but the values ​​do not appear.

[@DSMStrength]

What is the error

[@ermogenescampos6300]

@@DSMStrength When I put the dates ok, but when I give a sequence in the formula the values ​​do not appear and instead of the error dates as well. It is difficult to explain because I changed the language and the tests by putting body weight, fat and muscle percentage, BMI etc. Thank you for your help. This dashboard is very good!

[@DSMStrength]

@@ermogenescampos6300 Make sure you are using the {} brackets around any arrays that you are trying to return as well that they are separated by a comma.

[@ermogenescampos6300]

@@DSMStrength Ok, thank's very much

[@kyledexter1480]

Hello, is there anyway to do this but use multiple teams in the same data chart?

[@DSMStrength]

yes for sure

[@peakperformancetrainingnut6891]

Hey DSMStrength, I am having trouble to get my long formula to work Here is my formula =filter((index(Data,, match("Date", Headers,)), index(Data,, match(B5, Headers,))), index(Data,,match("date", Headers,))>=B4, index(Data,,match("Athlete Name", Headers,))=B2, index(data,,match(B5, headers,))"") Each time I get a #ERROR! in return. I am in the U.S. so commas are used. I was wondering if you are able to help with this issue? Also wanted to thank you and say how awesome your videos are to allow coaches to program and track athletic performance more efficiently!

[@DSMStrength]

what is the error you are getting? after your Headers add ",false" to your formula see if that helps

[@SERRANOSTEPUP]

Hello Coach, thanks for all you do to helping us, I am in France, I got a problem with the next formula : =FILTER({index(Data; ;match("Date"; Headers;)); index(Data; ;match(B5; Headers;))}; index(Data;;match("date"; Headers;))>=B4; index(Data;;match("Athlete Name"; Headers;))=B2), I have the answer on my sheet : La taille de plage pour la fonction FILTER n'est pas valide. Nombre de lignes attendu : 3998. Nombre de colonnes attendu : 1. Nombre de lignes saisi : 1999. Nombre de colonnes saisi : 1., it's in French but that's mean the column and the row attempted are not the good one. I saw on the comments that we maybe don't have the same { from France, but I don't know what I can do. Thanks for the help

[@quentinsoleilhac]

same problem !! i don't know what to do

[@smessom32]

Love the Videos and I am excited to complete them and get working with my team I am struggling with the long formula at 20mins in the video =filter({index(Data,,match("date",Headers,)),index(Data,,match(B5,Headers,))},index(Data,,match("date",Headers,))>=B4, index(Data,,match("Athlete name",Headers,))=B2, index(Data,,match(B5,Headers,))"") Can you tell me what I have missed as I am returning the error Error FILTER has mismatched range sizes. Expected row count: 999. column count: 1. Actual row count: 1, column count: 1. Pretty new to this stuff so any help would be Much appreciated

[@DSMStrength]

Hey coach I'll see if I can take a look at this a little later

[@smessom32]

@@DSMStrength Thanks for the quick reply. I look forward to hearing back from you.

[@DSMStrength]

@@smessom32 thanks for commenting it helps the videos get seen more :)

[@DSMStrength]

@@smessom32 Hey coach.. first thing to check is that your "Data" and "Headers" named ranges are correct Data = A2:Final Column Headers = 1:1

[@smessom32]

@@DSMStrength Fixed it. Thankyou

[@Mike__]

Hi Dave, With the Dates, Values and Standard section, I am having issues. I type in =filter(index(Data, ,match("Date", Headers,)), index(Data,,match("date", Headers,))>=B4) and the dates come up as they should. Then, when I move on to the next part to make the dates athlete specific =filter(index(Data, ,match("Date", Headers,)), index(Data,,match("date", Headers,))>=B4, index(Data,,match("Athlete Name", Headers,))=B2) I get the "filter has mismatched sizes error. Expected row count: 994, column count: 1. Actual row count: 1, column count: 1.". Not sure what I am doing wrong here...

[@Mike__]

NVM, solved it.

[@DSMStrength]

@@Mike__ great glad you figured it out! thanks for checking out the videos

[@Mike__]

@@buntingstrength2139 For me, it was because in the "Athlete Info" tab, my one header was not set to "Athlete Name" - instead I had it set to "Name". So when it was trying to pull from "Athlete Name", there was no information because I didn't have that as header. Very silly mistake, but I just simply changed that input to "Name" - as what my header actually was.

[@jamesjosephmarx9199]

Hi Dave on this part of the video it tells me that the "date" in this formula is #N/A (it tells me: Did not find value 'date' in MATCH evaluation.) 19.06 minutes of video. =filter(index(Data,,match("date",Headers,)),index(Data,,match("date",Headers,))>=B4) what am I doing wrong, because it doesn't give me the dates listed like in your video.

[@anthonypenalber3919]

Still confused on the dates. You have 1st-15th. Would each athlete need multiple testing dates ?

[@DSMStrength]

i believe I have already answered this

[@diogobaptista7796]

When I filter the equation for dates and values they show up. But, when I add the filter for the athlete's name, it gives an error. "FILTER has misplaced range sizes. Expected row count: 991. column count: 1. Actual row count 1: 1, column count: 1" Any solution. Also im using in my equation false. =FILTER({INDEX(Dados;;MATCH("Datas";Titulos;false)) \ INDEX(Dados;;MATCH(B5;Titulos;false))} ; INDEX(Dados;;MATCH("Datas";Titulos;false))>=B4; INDEX(Dados;;MATCH("Nome";Titulos;false))=B2 ; INDEX(Dados;;MATCH(B5;Titulos;false))"")

[@DSMStrength]

re-check your named ranges, spelling, syntax etc. also recheck if you have more then one testing entry per date

[@niyischolten8788]

how te get 2 ranges? because my values don't shows up!

[@niyischolten8788]

When I type your formula to get 2 ranges =filter(index(Data; ;match("Date"; Headers;)); index(Data; ;match(B5; Headers;)) its shows me the Dates but nothing off the values. And the formula is correct!

[@DSMStrength]

formula looks like you are only matching for date and nothing else

[@GastonRodrigoS]

Have you been able to solve it? I'm having the same problem as you.

[@TheDGLab]

FILTER has mismatched range sizes. Expected row count: 999. column count: 1. Actual row count: 1, column count: 1. I am getting this error but my Range Sizes on DATA is A2:End Column and HEADERS 1:1. The dates work but the Values will not show. Not sure what to do. Could I email or let you access the document to look at it? Let me know if this looks correct ---- =filter({index(Data, ,match("Date", Headers,)), index(Data, ,match(B5, Headers,))}, index(Data,,match("Date", Headers,))>=B4, index(Data,,match("Athlete Name", Headers,))=B2)

[@TheDGLab]

UPDATE: I subbed out "Athlete Name" for The A:A Column in my Database and it worked. Not sure why.

[@johanasberg1281]

I seem to have the same problem. The formula works when I only pull the data for Date but not when I'm trying to also pull data for the tests and adding the curly bracket.

[@DSMStrength]

it was not finding the athlete name after headers try adding ", false" in your match formula see if that cleans up your formula

[@adamstansbie36]

It comes up say did not find value of date can u pls help

[@DSMStrength]

make sure everything is formatted correctly, sorted, and that your match have , false at the end.

[@adamstansbie36]

@@DSMStrength thanks I have one last problem. Long term test KPI Chart 1 Bleep test and 400m. Test value 1 is 6.6 (bleep test score) Test value 2 is 2 (400m) Why do the array-formula in E11 and F11 both say 0 Any help would be great

[@GastonRodrigoS]

Have you been able to solve it? I'm having the same problem as you.

[@pratikkaushik5351]

hey,, You are making things complicated with formulas.... Not a appropriate way to make things understnad to the audience