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

tchandler29

New Member
Joined
Feb 23, 2016
Messages
3
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.
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,054
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 23, 2016
Messages
3
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

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,054
Office Version
  1. 365
Platform
  1. Windows
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
ABCDE
1TEXT:JanFebMar"APR"
2Month number1234

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

 

Watch MrExcel Video

Forum statistics

Threads
1,123,524
Messages
5,602,169
Members
414,509
Latest member
Cdavis7078

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top