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

#### tchandler29

##### New Member
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.

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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

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.

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

</tbody>

Replies
0
Views
146
Replies
0
Views
345
Replies
20
Views
550
Replies
1
Views
367
Replies
4
Views
423

1,218,538
Messages
6,143,078
Members
450,461
Latest member
Bosavon

### 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.

### Which adblocker are you using?

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

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