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!

Do you want to increment from Jun96 to Dec96? This could be done a couple of
ways but neither is very efficient and one of the ways results in a rather
long formula. If you only need Jun to Dec I think you'd be better off just
copying your current formula and manually changing the other sheet names.

--
Biff
Microsoft Excel MVP


"Excel Newbie" <Excel wrote in message
...

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!

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

Thanks for the repy! Unfortunately, I have to do this for every month until
june 2006 for various combinations of AI and AJ values. If I had a formula, I
could try to manipulate it for the other combinaitons.

"T. Valko" wrote:

Do you want to increment from Jun96 to Dec96? This could be done a couple of
ways but neither is very efficient and one of the ways results in a rather
long formula. If you only need Jun to Dec I think you'd be better off just
copying your current formula and manually changing the other sheet names.

--
Biff
Microsoft Excel MVP


"Excel Newbie" <Excel wrote in message
...

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!

I was afraid of that. See Dave's reply, it'll work

--
Biff
Microsoft Excel MVP


"Excel Newbie" wrote in message
...
Thanks for the repy! Unfortunately, I have to do this for every month
until
june 2006 for various combinations of AI and AJ values. If I had a
formula, I
could try to manipulate it for the other combinaitons.

"T. Valko" wrote:

Do you want to increment from Jun96 to Dec96? This could be done a couple
of
ways but neither is very efficient and one of the ways results in a
rather
long formula. If you only need Jun to Dec I think you'd be better off
just
copying your current formula and manually changing the other sheet names.

--
Biff
Microsoft Excel MVP


"Excel Newbie" <Excel wrote in message
...

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!

If starting with the month of June, I would change this:

ROW()+6

To:

ROWS($1:6)

That way you don't have to worry about what specific row the formula is on.
(and it's robust against row insertions)

--
Biff
Microsoft Excel MVP


"Dave Peterson" wrote in message
...
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

Thanks Valko! Will add that in.

"T. Valko" wrote:

If starting with the month of June, I would change this:

ROW()+6

To:

ROWS($1:6)

That way you don't have to worry about what specific row the formula is on.
(and it's robust against row insertions)

--
Biff
Microsoft Excel MVP


"Dave Peterson" wrote in message
...
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

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

I see your point--especially starting with Jun--mine started with Jul (oops!).

But I'm not sure I'd bother with the robustness of the formula with this
technique--the formulas are only there for a small amount of time--they need to
be converted to values and then converted to formulas.

In this case, I'd use whatever I remembered <bg.

"T. Valko" wrote:

If starting with the month of June, I would change this:

ROW()+6

To:

ROWS($1:6)

That way you don't have to worry about what specific row the formula is on.
(and it's robust against row insertions)

--
Biff
Microsoft Excel MVP

"Dave Peterson" wrote in message
...
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

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

I'm not sure I'd bother with the robustness of the formula with this
technique--the formulas are only there for a small amount of time

Yeah, that's true.

--
Biff
Microsoft Excel MVP


"Dave Peterson" wrote in message
...
I see your point--especially starting with Jun--mine started with Jul
(oops!).

But I'm not sure I'd bother with the robustness of the formula with this
technique--the formulas are only there for a small amount of time--they
need to
be converted to values and then converted to formulas.

In this case, I'd use whatever I remembered <bg.

"T. Valko" wrote:

If starting with the month of June, I would change this:

ROW()+6

To:

ROWS($1:6)

That way you don't have to worry about what specific row the formula is
on.
(and it's robust against row insertions)

--
Biff
Microsoft Excel MVP

"Dave Peterson" wrote in message
...
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

Thanks Dave! This will certainly come in handy the way this project is set up.

"Dave Peterson" wrote:

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