Copy formulas to next month

[Jennifer]

I have columns for each month of the year. I entered formulas into January
and I need to copy the formulas to February. The problem is the formula
isn't the same for the entire column (so I can't just drag all the way down)
and I need to change the worksheet from which it gets the information. Is
there any way to do this all at once? I have about 75 different worksheet to
do this to and it took about a day to just get the formulas into each sheet
for the first month.

 

[recrit]

I have columns for each month of the year.  I entered formulas into January
and I need to copy the formulas to February.  The problem is the formula
isn't the same for the entire column (so I can't just drag all the way down)
and I need to change the worksheet from which it gets the information.  Is
there any way to do this all at once?  I have about 75 different worksheet to
do this to and it took about a day to just get the formulas into each sheet
for the first month.

if i understand you correctly... the formulas for the entire column
will be the same for each month, but reference a different sheet.
If that is the case:

(1) you need to make the formulas able to take a sheet input. for
instance the following references A1 on the sheet name in cell G2:
=INDIRECT(ADDRESS(1,1,,,G$2))
you can then put the sheet name for each month and then reference
as in the example G$2 when copied or dragged will keep the row
constant but adjust the column

(2) you can now copy the entire column and paste the formulas

 

[Jennifer]

Can I use the =INDIRECT(ADDRESS(1,1,,,G$2)) with VLookup and ISNA?

This is what my formula looks like right now.

=IF(ISNA(VLOOKUP($A28,'C:\Documents and Settings\jbarton\My
Documents\Excel\[CSC
Volumes.xls]ppweb(Jun)'!$B$2:$BZ$13,39,FALSE)),0,(VLOOKUP($A28,'C:\Documents
and Settings\jbarton\My Documents\Excel\[CSC
Volumes.xls]ppweb(Jun)'!$B$2:$BZ$13,39,FALSE)))

Except like i mentioned this isn't the exact same formula down the entire
column.

 

[Pete_UK]

INDIRECT does not work with closed workbooks, so you would have to arrange
for the file CSC Volumes.xls to be open if you wanted to use it. If it was
open, then you wouldn't need the path in the formula, which would shorten it
considerably to this:

=IF(ISNA(VLOOKUP($A28,'[CSC
Volumes.xls]ppweb(Jun)'!$B$2:$BZ$13,39,FALSE)),0,(VLOOKUP($A28,'[CSC
Volumes.xls]ppweb(Jun)'!$B$2:$BZ$13,39,FALSE)))

Hope this helps.

Pete

Can I use the =INDIRECT(ADDRESS(1,1,,,G$2)) with VLookup and ISNA?

This is what my formula looks like right now.

=IF(ISNA(VLOOKUP($A28,'C:\Documents and Settings\jbarton\My
Documents\Excel\[CSC
Volumes.xls]ppweb(Jun)'!$B$2:$BZ$13,39,FALSE)),0,(VLOOKUP($A28,'C:\Documents
and Settings\jbarton\My Documents\Excel\[CSC
Volumes.xls]ppweb(Jun)'!$B$2:$BZ$13,39,FALSE)))

Except like i mentioned this isn't the exact same formula down the entire
column.

if i understand you correctly... the formulas for the entire column
will be the same for each month, but reference a different sheet.
If that is the case:

(1) you need to make the formulas able to take a sheet input. for
instance the following references A1 on the sheet name in cell G2:
=INDIRECT(ADDRESS(1,1,,,G$2))
you can then put the sheet name for each month and then reference
as in the example G$2 when copied or dragged will keep the row
constant but adjust the column

(2) you can now copy the entire column and paste the formulas