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