Google sheets - indirect function?
I have a 'totals' sheet that pulls stats for matches on a sheet named by the date the match(es) were played on.
On my totals sheet, I have each athlete's data listed under their name, by match, so they can see how they've (hopefully) improved throughout the season.
The problem is, I have to manually copy/paste/adjust the cell formula for each match.
Example:
On 9/5 we played Team A & Team B.
On the totals tab, I list "Cindy" and her data for certain skills for those matches.
Column A would show "9/5 Team A" and the next row would show "9/5 Team B".
In column B I would normally reference "='9/5'!B17" to pull her data for that stat. Then I could copy/paste it for each skill recorded during that game.
Next row, I'd repeat, but it would need to read "='9/5'!B31" (there are 14 rows between the same skill of each subsequent match).
Then I could copy/paste for that entire match as well (meaning column C becomes "='9/5'!C31").
I want to figure out a way that I can copy/paste based on some moving criteria.
I can reference the sheet name dynamically by using
=indirect(left(A119,3)&"!B
But, obviously, I can't copy/paste that for columns C forward, I have to manually increment the column.
Secondly, if there was a second (or multiple) matches, I'd like to be able to dynamically increment for the next row:
=indirect(left(A120,3)&"!B
Finally, if there's a way to do this based on the athlete's name, the first Athlete's name begins on A37 of Totals, and for each sheet containing 2, up to 5, matches, the athletes name would appear on A3-A12 (summary data only, that I'm not using), and then A17-A26, A31-A40, and A45-A54 (if a 3rd match), etc. The athlete's data would appear beginning to the right of their name (column headers list each skill being tracked).
Hope that helps explain it. I'm thinking it might not be too difficult to pull off for someone more familiar with Google Sheets than I. :^)
On my totals sheet, I have each athlete's data listed under their name, by match, so they can see how they've (hopefully) improved throughout the season.
The problem is, I have to manually copy/paste/adjust the cell formula for each match.
Example:
On 9/5 we played Team A & Team B.
On the totals tab, I list "Cindy" and her data for certain skills for those matches.
Column A would show "9/5 Team A" and the next row would show "9/5 Team B".
In column B I would normally reference "='9/5'!B17" to pull her data for that stat. Then I could copy/paste it for each skill recorded during that game.
Next row, I'd repeat, but it would need to read "='9/5'!B31" (there are 14 rows between the same skill of each subsequent match).
Then I could copy/paste for that entire match as well (meaning column C becomes "='9/5'!C31").
I want to figure out a way that I can copy/paste based on some moving criteria.
I can reference the sheet name dynamically by using
=indirect(left(A119,3)&"!B
But, obviously, I can't copy/paste that for columns C forward, I have to manually increment the column.
Secondly, if there was a second (or multiple) matches, I'd like to be able to dynamically increment for the next row:
=indirect(left(A120,3)&"!B
Finally, if there's a way to do this based on the athlete's name, the first Athlete's name begins on A37 of Totals, and for each sheet containing 2, up to 5, matches, the athletes name would appear on A3-A12 (summary data only, that I'm not using), and then A17-A26, A31-A40, and A45-A54 (if a 3rd match), etc. The athlete's data would appear beginning to the right of their name (column headers list each skill being tracked).
Hope that helps explain it. I'm thinking it might not be too difficult to pull off for someone more familiar with Google Sheets than I. :^)
aikimark
please post a link to the Google doc
ASKER
Here's a sample of what I'm trying to do...
https://docs.google.com/spreadsheets/d/1nRVTl_qhfZgwEdGdL-87GhSHpHM3z9a5PuOvO-_bRyU/edit?usp=sharing
https://docs.google.com/spreadsheets/d/1nRVTl_qhfZgwEdGdL-87GhSHpHM3z9a5PuOvO-_bRyU/edit?usp=sharing
You have "Athlete #" sequences for both teams. Is this really a different set of players on each team?
ASKER
This is all the same team.
Totals tab represents season stats. There's a section below that announces 'stat leaders', but below that you should see individual groupings, by match:
PLAYER SERVING HITTING PASSING BLOCKS SET DEF Ace Kill
Athlete 1 ATT ACE ERR IN % ATT KILL ERR HIT % ATT 3 AVG BLK AST TTL AST DIG % %
9/5 Team A 9 1 2 77.8 11 5 1 0.364 0 0 -- 1 0 1 0 5 9.0 2.2
9/5 Team B 9 2 1 88.9 4 1 1 0 2 1 1.5 0 1 1 0 0 4.5 4.0
It's this data that I want to read from the corresponding date sheet.
That data should be sourced from the 9/5 sheet. On the 9/5 sheet, the first grouping of statistics is the summary/totals for the entire date. Then below that, there would be a similar list for each match. If I wanted to see how we did on 9/5, I'd look at the very first group. If I was looking for individual matches, I would look at the 2nd, 3rd, etc. sections (versus Team A, or versus Team B). We could have up to 5 opponents on a given date.
Let me know if that helps.
Totals tab represents season stats. There's a section below that announces 'stat leaders', but below that you should see individual groupings, by match:
PLAYER SERVING HITTING PASSING BLOCKS SET DEF Ace Kill
Athlete 1 ATT ACE ERR IN % ATT KILL ERR HIT % ATT 3 AVG BLK AST TTL AST DIG % %
9/5 Team A 9 1 2 77.8 11 5 1 0.364 0 0 -- 1 0 1 0 5 9.0 2.2
9/5 Team B 9 2 1 88.9 4 1 1 0 2 1 1.5 0 1 1 0 0 4.5 4.0
It's this data that I want to read from the corresponding date sheet.
That data should be sourced from the 9/5 sheet. On the 9/5 sheet, the first grouping of statistics is the summary/totals for the entire date. Then below that, there would be a similar list for each match. If I wanted to see how we did on 9/5, I'd look at the very first group. If I was looking for individual matches, I would look at the 2nd, 3rd, etc. sections (versus Team A, or versus Team B). We could have up to 5 opponents on a given date.
Let me know if that helps.
It is clearer. Thanks.
Hi sirbounty,
Paste the below lengthy formula into Cell B38 , Then drag it right and down to get all required results.
=VLOOKUP($A$37,INDIRECT(AD
Paste the below lengthy formula into Cell B38 , Then drag it right and down to get all required results.
=VLOOKUP($A$37,INDIRECT(AD
ASKER
I like where you're headed with that - but why the reference to B37?
The above works on the sample, but not in my actual sheet. Breaking it apart to try to determine, and that one jumped out at me.
The above works on the sample, but not in my actual sheet. Breaking it apart to try to determine, and that one jumped out at me.
ASKER
I neglected to change one of the other A38 reference to my actual reference, so that's working.
Copy/paste works, up to a point.
My cell formula ='9/5'!F17, shows the correct value of 11 but pasting that formula shows a value of 9. Any idea how to troubleshoot that one? It worked for each cell up to F, so I'm not sure what happened there...
Actually seems to work for all cells in the row, with the exception of maybe 3-4.
Additionally, since there is a static reference to the date, 9/5, it only works for those corresponding matches. Any way to make that dynamically adjust based on the left 3 characters of the cell in column A?
Copy/paste works, up to a point.
My cell formula ='9/5'!F17, shows the correct value of 11 but pasting that formula shows a value of 9. Any idea how to troubleshoot that one? It worked for each cell up to F, so I'm not sure what happened there...
Actually seems to work for all cells in the row, with the exception of maybe 3-4.
Additionally, since there is a static reference to the date, 9/5, it only works for those corresponding matches. Any way to make that dynamically adjust based on the left 3 characters of the cell in column A?
ASKER
This seems to sort the dynamic date grab:
=VLOOKUP($A$37,INDIRECT(AD
But I think the issue with the 3 cells not working, is that the same secondary header is used: ATT & ERR (under Serving, Hitting & Passing)
=VLOOKUP($A$37,INDIRECT(AD
But I think the issue with the 3 cells not working, is that the same secondary header is used: ATT & ERR (under Serving, Hitting & Passing)
Hi,
I took B37 as reference because I thought you are looking up the Athlete name on the two different table and then on different sheet - I also assumed that your sheet name is included in A Column (9/5 Team A)= (SheetName TeamName) Where 9/5 is the sheet name and whatever after the space is team name. and regarding the column index if all sheet you will create will store data in same column then you can use Column() to get the Column index in the vlookup.
Check the formula below it will respond to the change in sheet name, Again paste below formula in B38
=VLOOKUP($A$37,INDIRECT(AD
IF still this solution doesn't work for you - let me know
I took B37 as reference because I thought you are looking up the Athlete name on the two different table and then on different sheet - I also assumed that your sheet name is included in A Column (9/5 Team A)= (SheetName TeamName) Where 9/5 is the sheet name and whatever after the space is team name. and regarding the column index if all sheet you will create will store data in same column then you can use Column() to get the Column index in the vlookup.
Check the formula below it will respond to the change in sheet name, Again paste below formula in B38
=VLOOKUP($A$37,INDIRECT(AD
IF still this solution doesn't work for you - let me know
ASKER
Yes, 'most' of the sheets are simply the date (9/5). I had a few that were the date and tournament name, but only 2-3, so I'll change the sheet names to just the date alone.
And yes, each row should be the date (same as sheet name) followed by the opponent (i.e. Team A), although I had sometimes written the abbreviation instead, so I will make sure those match up.
I got a #REF error simply pasting this one into B38, but I'll work with it a bit and see if I can get it working. Thanks for your time.
And yes, each row should be the date (same as sheet name) followed by the opponent (i.e. Team A), although I had sometimes written the abbreviation instead, so I will make sure those match up.
I got a #REF error simply pasting this one into B38, but I'll work with it a bit and see if I can get it working. Thanks for your time.
ASKER
Can't seem to get it working - not even in the sample. It gives the same #REF error.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sir, I believe that is the solution!
Thank you so much for this.
I have another sheet in this workbook that operates off a similar formula basis - if you don't mind, I'd like to create a new question and drop the link here. It shouldn't be much different than this, other than the orientation (it runs in rows, rather than columns).
Thanks much!
Thank you so much for this.
I have another sheet in this workbook that operates off a similar formula basis - if you don't mind, I'd like to create a new question and drop the link here. It shouldn't be much different than this, other than the orientation (it runs in rows, rather than columns).
Thanks much!
You are welcomed!
ASKER
https://www.experts-exchange.com/questions/29058394/Google-sheets-indirect-function-part-2.html is the new question. Thanks for any help you can provide.