Using a formula on a different for the current sheet's cells

[tchandler29]

I have about 15 tabs that are formatted exactly the same, with the exact same formulas in the same place, but all with different numbers.

I have numbers for each month of the year going across cells M10-X10, and Jan-Dec above it in cell M9-X9. I want to have an average function on each sheet in cell M2 that averages the numbers from Jan up to the current month (If it is currently May, then I want the average function to be =Average(M10-Q10), if it is December, I want it to be =Average (M10-X10)).

I have an input tab with all the months that I am using to reference other formulas throughout the sheet (including one to show what the current month is). How do I get each individual sheet to know what months to pick up in the average formula, based on what month I say it is on the input tab? What I've been trying to do is find a way to type each average formula on the input tab, and somehow reference the formula to be used for the current sheet.

Throughout the year I will be adding sheets, so it would be nice to be able to just move & copy a tab and the formula still work for the new numbers on the new tab.

Thanks.

 

[StephenCrump]

Welcome to the Forum!

Assuming you have a reasonably recent version of Excel, you could use the AVERAGEIF function, perhaps something like:

=AVERAGEIF(M9:X9,"<="&MonthInInputTab,M10:X10)

This assumes your dates are actually dates, formatted to display as Jan, Feb etc, and not text values "Jan", "Feb" etc

 

[tchandler29]

Currently it is just text because the software that I use to retrieve my numbers requires the months to be in a certain format...any way I could work around that? Either formatting the actual dates how I need, or do an =AVERAGEIF off of texts? The months read as "JAN", "FEB" etc. so it's not really a crazy format...just caps I guess.

 

[StephenCrump]

It's much easier to work with numeric month values. Here's one way you could convert:

B2: =MONTH(B1&"-1"+0) If text values are like Jan, Feb etc
E2: =MONTH(SUBSTITUTE(E1,"""","")&"-1"+0) If text values are like "Apr" etc

Excel 2010

	A	B	C	D	E
1	TEXT:	Jan	Feb	Mar	"APR"
2	Month number	1	2	3	4

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

​