First, I started with Jul, not Jun. So ignore that error.

This portion is the part that does the real work:

&TEXT(DATE(1996,ROW()+6,1),"mmmyy")&

=date(year,month,day) is the syntax for the =date() function.
In this case, year is 1996, month will resolve to 6, 7, 8, ...., and it'll aways
be the first of that month.

And excel is pretty smart. It knows the 13th month of 1996 is January of 1997.
And on and on and on...

And
=text(somedate,"mmmyy")
just formats that date to look like: Jun96, Jul96, ...

And that portion of the formula replaces your worksheet names (3 times).

But by using a formula, it'll adjust when you drag it down. But you have to do
the extra work of converting to values and converting the string to a formula.

Excel Newbie wrote:

It works! Thanks a lot Dave, that saved me a LOT of time. Would it be
possible to get an explanation for what the formula actually does? Its a
really handy techique an it would be great if I could work out the mechanics
of it. Thanks again Dave!

"Dave Peterson" wrote:

If there's a lot of those cells to fill, then I'd use this techique:

I'd build a formula that builds a string that looks like the formula.

With the first formula in row 1:

="=AVERAGEIFS(" &TEXT(DATE(1996,ROW()+6,1),"mmmyy")&"!AG:AG,"
&TEXT(DATE(1996,ROW()+6,1),"mmmyy")&"!AI:AI,5,"
&TEXT(DATE(1996,ROW()+6,1),"mmmyy")&"!AJ:AJ,5)"

You may have to adjust the row()+6 in all 3 spots if you're starting in a
different row.

Then drag the formua down the column.

Now you've got a bunch of results that look like your formula:

=AVERAGEIFS(Jul96!AG:AG,Jul96!AI:AI,5,Jul96!AJ:AJ, 5)
=AVERAGEIFS(Aug96!AG:AG,Aug96!AI:AI,5,Aug96!AJ:AJ, 5)
=AVERAGEIFS(Sep96!AG:AG,Sep96!AI:AI,5,Sep96!AJ:AJ, 5)

But they're not formulas.

Select that range.
edit|copy
Edit|paste special|values

They're still text--one more thing to do.

Edit|Replace
what: = (equal sign)
with: =
replace all

Excel will see them as formulas!

Be careful. I'd suggest that you convert a couple first. If your formula that
builds the formula is wrong, you may spend a lot of time dismissing the "where's
this file" dialog.




Excel Newbie wrote:

Hi all,

Does anyone know a way where I can change the sheet used in an AVERAGEIFS
formula in Excel when I attempt to autofill the formula down the column? the
formula for cell

B2 is:AVERAGEIFS(jun96!AG:AG,jun96!AI:AI,5,jun96!AJ:A J,5)
For B3 I would like it to be:
AVERAGEIFS(jul96!AG:AG,jul96!AI:AI,5,jul96!AJ:AJ,5 )

Where my sheet names are jun96, jul96, aug96 etc? Currently, Excel just
copies the exact same formula when I attemp to autofill.

Thanks!

--

Dave Peterson


--

Dave Peterson