get an average from seperate sheets using a vlookup function?

The average in the 12-6 sheet is over 4 weeks (including that week), and the same for sheet 12-13.

Won't you have to change this formula anyway, as you move from one sheet to the next, to take account of different sheet names?

Pete

My personal opinion is improper data layout is causing you more hassle than it's worth here.

I'd put all the data in one long list and work out your avgs etc. from there based on a start & end date for the required period using AVERAGEIFS.

IF you're absolutely set in your ways on the current layout then you could replicate that with formulas from the long list.

Beth.

Originally Posted by Pete_UK

The average in the 12-6 sheet is over 4 weeks (including that week), and the same for sheet 12-13.

Won't you have to change this formula anyway, as you move from one sheet to the next, to take account of different sheet names?

Pete

I know - but say we added a 12-20 sheet. I would have to change the formula to

=AVERAGE('11-29'!C3,'12-6'!C3,'12-13'!C3,'12-20'!C3)

And, if someone on that list; say employee 19 left then I would have to make sure that the formula was different for that specific row and below...

It is an extremely tedious task to change this every week so I am looking for a solution.

Hopefully the attached file will solve your problems, and give you some time back in your life.

Notice first of all that I have renamed the sheet 12-6 to 12-06. It is important in the formulae which follow that the sheet names are of the format mm-dd, with leading zeros for both the month and day where appropriate.

In all the sheets I have used this formula in cell I1:

=MID(CELL("filename",A1),FIND("]",CELL("Filename",A1))+1,255)

This returns the name of the sheet, and I have coloured it red for emphasis. You can then derive the names of the previous sheets, using this formula in J1:

=TEXT(DATE(2019,LEFT(I1,2),RIGHT(I1,2))-7,"mm-dd")

and this formula can be copied across into K1:L1, to give you 4 sheet names. Note, though, that you will have the change the year (shown in red) to 2020, once you get to next year with more added sheets.

Then, for the sheets which have at least 3 previous sheets, you can use this formula in cell F3:

=AVERAGE(C3,IFERROR(INDEX(INDIRECT("'"&J$1&"'!c:c"),MATCH($A3,INDIRECT("'"&J$1&"'!a:a"),0)),""),IFERROR(INDEX(INDIRECT("'"&K$1&"'!c:c"),MATCH($A3,INDIRECT("'"&K$1&"'!a:a"),0)),""),IFERROR(INDEX(INDIRECT("'"&L$1&"'!c:c"),MATCH($A3,INDIRECT("'"&L$1&"'!a:a"),0)),""))

which will take the average of the current sheet's column C value along with the corresponding value in the previous 3 sheets, so even if a name moves up or down in one or more of those sheets, the value will still be found (there were no examples of this, so I couldn't fully test this out). The formula can be copied down column F where appropriate - I've done this for sheets 12-06 and 12-13.

In the sheets which have only two or fewer previous sheets, I've left the original formulae in column F, and instead put a modified formula in cell I3. Where there are only 2 previous sheets, you only need data from those sheets, so the formula can be shortened. If there is only 1 previous sheet, the formula can be shortened even further, but you can compare the results directly with the results that your original formulae produced.

Going forward, all you need to do is CTRL-drag the latest sheet tab to produce an exact copy, and then rename the new sheet as appropriate and the formulae will automatically adjust. You can move the names up and down as required, and then enter the data for that week. If you add any new names, you should bear in mind that they won't exist on previous sheets, so you can delete from the formula in column F the terms which relate to those weeks, as appropriate.

Hope this helps.

Pete

Originally Posted by Pete_UK

Hopefully the attached file will solve your problems, and give you some time back in your life.

Notice first of all that I have renamed the sheet 12-6 to 12-06. It is important in the formulae which follow that the sheet names are of the format mm-dd, with leading zeros for both the month and day where appropriate.

In all the sheets I have used this formula in cell I1:

=MID(CELL("filename",A1),FIND("]",CELL("Filename",A1))+1,255)

This returns the name of the sheet, and I have coloured it red for emphasis. You can then derive the names of the previous sheets, using this formula in J1:

=TEXT(DATE(2019,LEFT(I1,2),RIGHT(I1,2))-7,"mm-dd")

and this formula can be copied across into K1:L1, to give you 4 sheet names. Note, though, that you will have the change the year (shown in red) to 2020, once you get to next year with more added sheets.

Then, for the sheets which have at least 3 previous sheets, you can use this formula in cell F3:

=AVERAGE(C3,IFERROR(INDEX(INDIRECT("'"&J$1&"'!c:c"),MATCH($A3,INDIRECT("'"&J$1&"'!a:a"),0)),""),IFERROR(INDEX(INDIRECT("'"&K$1&"'!c:c"),MATCH($A3,INDIRECT("'"&K$1&"'!a:a"),0)),""),IFERROR(INDEX(INDIRECT("'"&L$1&"'!c:c"),MATCH($A3,INDIRECT("'"&L$1&"'!a:a"),0)),""))

which will take the average of the current sheet's column C value along with the corresponding value in the previous 3 sheets, so even if a name moves up or down in one or more of those sheets, the value will still be found (there were no examples of this, so I couldn't fully test this out). The formula can be copied down column F where appropriate - I've done this for sheets 12-06 and 12-13.

In the sheets which have only two or fewer previous sheets, I've left the original formulae in column F, and instead put a modified formula in cell I3. Where there are only 2 previous sheets, you only need data from those sheets, so the formula can be shortened. If there is only 1 previous sheet, the formula can be shortened even further, but you can compare the results directly with the results that your original formulae produced.

Going forward, all you need to do is CTRL-drag the latest sheet tab to produce an exact copy, and then rename the new sheet as appropriate and the formulae will automatically adjust. You can move the names up and down as required, and then enter the data for that week. If you add any new names, you should bear in mind that they won't exist on previous sheets, so you can delete from the formula in column F the terms which relate to those weeks, as appropriate.

Hope this helps.

Pete

Dude,

You are literally a wizard. Please link your paypal or something so I can tip you for your work!

No need for payment - all contributions to the forum are offered freely (and we don't tip so much in the UK). Glad to hear it worked for you.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete