MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Copy formulas

Posted by Mark on February 06, 2002 9:02 AM

I have a spreadsheet, called #1, with 12 columns representing the months of the year, Jan. thru Dec. Here is my problem.. Take the month of January, for example, I am wanting to pull about a dozen numbers from a very, very large spreadsheet and pull them onto the #1 spreadsheet which will give us exactly what we are wanting to see. So, January comes out fine, but how do I copy the formulas over to Feb., March, April etc. and get the formulas to pull from their respective month?? I want my Feb. formulas to pull from the Feb. worksheet, but when I copy the formulas from January to February the formulas are still reading the data as January, not February?? Confused? Me Too!! Help!!!

Posted by Steve Hartman on February 06, 2002 9:11 AM

Excel doesn't do relative addressing on the worksheet names. After copying your formulas, you'll have to do a search and replace on the worksheet names for each month.

Posted by MARK on February 06, 2002 12:59 PM

I replaced December with January, but no luck. Excel is not finding my January database so that I can pull my new numbers in.....

Posted by Chris D on February 06, 2002 1:17 PM

I'm guessing your January formulae aren't absolute because you wanted to copy them down the column.... did you remove *both* $ signs rather than just the one that anchors the column ?

if you did, when you copy your January formulae to your February column, it will shift the column ref along one (ie G becomes H cos it isn';t anchored with a $)

so despite trying Steve's perfectly sound logic, it will fail because the original anchored link of $x$y will have been changed

But, I'm guessing !