Ask Your Question

Turkey shoot? Averaging multiple data across multiple sheets, while looking for name that are constantly moving

asked 2019-08-13 23:28:17 +0200

daverc gravatar image

updated 2019-08-15 03:36:27 +0200

I'm a complete knob at this, plus permafrost has started to settle in and disrupt the thinking process. So I need all the help I can get.

I'm working on a Calc project that has 12 sheets one for each month of the year. on each sheet are tables containing multiple names and with each name a list of 9 things that vary each month. Each month the names in each table can shift from one table to the next depending on their assignment. I'm looking to be able to collect the data for each name and average it on a 3/6 month basis.

Thanks in advance for any help or direction givenC:\fakepath\test 2019.ods

edit retag flag offensive close merge delete


Hi, are you able to post a sample of your sheet?

gregors15 gravatar imagegregors15 ( 2019-08-14 10:24:36 +0200 )edit

I've uploaded the file that I'm needing help on

daverc gravatar imagedaverc ( 2019-08-15 03:38:24 +0200 )edit

what I'm looking for is if I'm on the January Sheet it would average January and the prior 2 and 5 month. hope this helps

daverc gravatar imagedaverc ( 2019-08-15 03:42:34 +0200 )edit

Hi, Could you produce a summary .ods file, with the name as rows, and the months as the columns, which links out to your sheet then by using index/match and then counts and/or totals populate the data you need, I have a many monthly sheets, and 1 summary .ods file,I find it easier to do the data calculations from that single summary sheet.

gregors15 gravatar imagegregors15 ( 2019-08-16 11:59:24 +0200 )edit

I've never done a summary or worked with Index/Match could you give me an example of what might you do

daverc gravatar imagedaverc ( 2019-08-16 14:10:11 +0200 )edit

Hi, Ok I will try and create a sheet using your data, with a couple of months in, one question without know how your data hangs together, is the name (initials I assume) unique within the month, I see the name CT in both GRP1 and 2? You would have to have something unique to make the match work.

gregors15 gravatar imagegregors15 ( 2019-08-16 17:13:21 +0200 )edit

My Boo Boo the CT in the second grp should be a CT02. yes they are Initials and no one could be in more then one grp at a time

daverc gravatar imagedaverc ( 2019-08-16 18:50:36 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2019-08-16 17:57:57 +0200

gregors15 gravatar image

Hi, OK first attempt, I have created sheet14_Plmts which we can use as the summary sheet for Plmts(Column O) and populated Jan and Feb using SUMIF you could make similar sheets for the other headings, Vids, Hr's etc. And as you update the monthly sheets the summary sheet would also be updated automatically. New names would need to be added to the summary sheet. Let us know what you think.C:\fakepath\LOQ_20190816.ods

edit flag offensive delete link more


The one thing that has me stumped is being able to search through all 12 months(sheets) and automatically pull the names of each person and average the data 3/6 month period. I can begin to see how what you have worked up would be part of the answer.

I really appreciate you taking a look at this for me

daverc gravatar imagedaverc ( 2019-08-16 18:56:28 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-08-13 23:28:17 +0200

Seen: 61 times

Last updated: Aug 16 '19