Formula copy

[philb99]

Hi - I have a number of tabs detailing team results in month order Jan in A2 Feb in B2 etc

Then an on another tab I have the monthly results in month order

team 1 team 2 team 3 etc for Jan in Cells A2 B2 C2 etc

However each month I have to copy the formulae into the monthly results tab or use =

i am am trying to understand if there is quicker way of doing this as it’s taking me ages as the team volumes increase

Thanks

 

[levy77]

From what I understand, it looks like you should be able to use an index match formula. Try "=INDEX('Team 1'!$B$2:$B$13,MATCH('Monthly Results'!A2,'Team 1'!$A$2:$A$13,0))"

 

[levy77]

You can also put an iferror function in front and drag the formula all the way down so the monthly stats automatically update.

 

[philb99]

thanks for trying to help - this is the set up in the monthly results tab -

[TABLE="width: 306"]
<colgroup><col span="6"></colgroup><tbody>[TR]
[TD="colspan: 3"]Jan[/TD]
[TD="colspan: 3"]Feb[/TD]
[/TR]
[TR]
[TD]Team 1[/TD]
[TD]Team 2[/TD]
[TD]Team 3[/TD]
[TD]Team 1[/TD]
[TD]Team 2[/TD]
[TD]Team 3[/TD]
[/TR]
</tbody>[/TABLE]

I am slightly confused re the Index match formula so might need some more help please. The results for the team are taken from their tabs - Team 1, Team 2 etc where the results are recorded in A2 to L2

 

[sandy666]

would be fine to see your Excel version, representative source data and expected result
use GoogleDrive, OneDrive or any similar to share excel file and post link to this file here...

 

[philb99]

thanks for looking - google drive simplistic spreadsheet

https://drive.google.com/open?id=1z5lQIhrF17SvRCrVRfPiu6ShpG4-A8Jk

 

[sandy666]

maybe this way will be easier (without double headers and merged cells) ?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Month[/td][td=bgcolor:#70AD47]Team1[/td][td=bgcolor:#70AD47]Team2[/td][td=bgcolor:#70AD47]Team3[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Jan[/td][td=bgcolor:#E2EFDA]

23​

[/td][td=bgcolor:#E2EFDA]

48​

[/td][td=bgcolor:#E2EFDA]

78​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Feb[/td][td]

34​

[/td][td]

235​

[/td][td]

56​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Mar[/td][td=bgcolor:#E2EFDA]

55​

[/td][td=bgcolor:#E2EFDA]

74​

[/td][td=bgcolor:#E2EFDA]

83​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Apr[/td][td]

44​

[/td][td]

44​

[/td][td]

96​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]May[/td][td=bgcolor:#E2EFDA]

78​

[/td][td=bgcolor:#E2EFDA]

78​

[/td][td=bgcolor:#E2EFDA]

58​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Jun[/td][td]

55​

[/td][td]

55​

[/td][td]

456​

[/td][/tr]
[/table]

or like this:

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Month[/td][td=bgcolor:#70AD47]Jan[/td][td=bgcolor:#70AD47]Feb[/td][td=bgcolor:#70AD47]Mar[/td][td=bgcolor:#70AD47]Apr[/td][td=bgcolor:#70AD47]May[/td][td=bgcolor:#70AD47]Jun[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Team1[/td][td=bgcolor:#E2EFDA]

23​

[/td][td=bgcolor:#E2EFDA]

34​

[/td][td=bgcolor:#E2EFDA]

55​

[/td][td=bgcolor:#E2EFDA]

44​

[/td][td=bgcolor:#E2EFDA]

78​

[/td][td=bgcolor:#E2EFDA]

55​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Team2[/td][td]

48​

[/td][td]

235​

[/td][td]

74​

[/td][td]

44​

[/td][td]

78​

[/td][td]

55​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Team3[/td][td=bgcolor:#E2EFDA]

78​

[/td][td=bgcolor:#E2EFDA]

56​

[/td][td=bgcolor:#E2EFDA]

83​

[/td][td=bgcolor:#E2EFDA]

96​

[/td][td=bgcolor:#E2EFDA]

58​

[/td][td=bgcolor:#E2EFDA]

456​

[/td][/tr]
[/table]

If there is more data in Teams you want to sum values from months?

edit:
it was done via PowerQuery aka Get&Transform) that is why I asked about your Excel version

 

[philb99]

Thanks but I need the format provided as I have different detail in the rows - it’s the ability / knowledge on how to get a formulae recorded in the Monthly tab that I require

 

[sandy666]

Thanks but I need the format provided as I have different detail in the rows - it’s the ability / knowledge on how to get a formulae recorded in the Monthly tab that I require

sure

so good luck and have a nice day

 

[levy77]

Hi Phil, try this https://drive.google.com/file/d/1ZtcZqN794ttto_h69R7OOivSQEiCW5K9/view?usp=sharing

I wouldn't recommend using the format you are unless you have to. The format in the above spreadsheet is easier to analyse, manipulate and create easy formulas for.

If this is not what your looking for let me know.

Cheers