What function is needed to reflect current weekly totals?

[mtctx]

Hi! I lurk here a lot but this is my first time to post.

I manage a fleet of aircraft. Each Friday we post the current hourly flight time for each aircraft on a worksheet. There are 52 cells for each aircraft where the data is located. On a seperate summary sheet in the same workbook is a cell that reflects the hourly total as well, and this is the sheet the boss looks at.

What function could I use to have the summary sheet automatically show the current aircraft total time (ACTT) without having to manually change the information each week. In other words, I want the summary sheet to automatically look at the next sequential box on the data sheet where the ACTT data is each week so that I don't have to edit the summary sheet to look at the next cell every Friday. Does that make sense?

Thanks!
Mick

[soberguy]

Can you post some sample data?

Though from what you've stated, it seems a SumIfs would work sufficiently.

[mtctx]

Thanks Soberguy...attached is the test sheet I am trying to get working.

[soberguy]

How should current time be calculated?

[mtctx]

The current time doesn't need to be calculated...it is manually entered each week from the aircraft logbooks. I am just trying to save a little time by having the current time column look for the current week's numbers and automatically changing to reflect that. I realize that it's not that much extra effort to just copy and paste the times into the current time column, but the records people are swamped and this little bit of automation will help them out quite a bit.

[soberguy]

Let me try again. I'd like to help put a formula into the current time column.

But I see a few issues here, and I'm trying to make a life a little easier all around.

You also have a hidden spreadsheet in the workbook that contains a fantastically organized set of data. That single data source can drive the entirety of your spreadsheet, with the simple addition of a starting time column (which appears to be the data in I4:I15 of Fleet Hours). I'm just curious if Fleet Hours is manually populated, because that hidden sheet is a much more organized data set to work off of. Giving you a formula for Fleet Hours provides a solution, but giving one that works off of the hidden sheet will be easier for you to read and adjust as needed.

On top of that, you have a formula error in J5.

[soberguy]

I dropped this into E4 and got a perfect match with what you had:

=SUM(Sheet1!B2:INDIRECT("Sheet1!"&ADDRESS(2,WEEKNUM(D1)-4)),I4)

That -4 after Weekday(D1) can disappear if you change D1 to 5/2/14.

[mtctx]

Yeah the fleet hours are manually populated. That's the best way we found to get the data into the sheet to start with. The hidden sheet was the only way I could get the AVERAGEIF function to work correctly. Eventually the only sheet that the managers will see is the MASTER SCHED...everything else will be locked and hidden...thus the goal of having the MASTER SCHED sheet populate as much info automatically as possible. Since this project is in its infancy, I am open to any suggestions for streamlining it, making it more user friendly, or better organizing the data. Thanks for your help so far!

[oeldere]

Maybe with an pivot table.

[mtctx]

Thanks soberguy...I will play with that for a while and see what happens.