how to link multiple columns to multiple worksheet totals / functi

[Chieftan]

Hi,
I havea work book with 13 worksheets ... 12 worksheets for Jan to Dec. They
are all identical structure, with vaying perfomance data entered.
I have created a 13th worksheet to summarise key information for each month.
One column per month. There are some complex formulas to display the result I
want, which work ok for the column I have created them on.

I now want to copy over the formulas in each column to correspond with each
month ... without having to type each formula again.

In other words ...
Summary worksheet column Jan = data collected from worksheet Jan

I would like to copy these formulas so that
Summary worksheet column Feb = data collected from worksheet Feb
... and so on.

I'm guessing there is an easy way to do this .. but I cannot figure it out!
Can anyone help please?
Thanks
Chieftan

 

[AG]

Try copying and pasting your formula first and then select the whole
column (Say Jan) and then do a Control+H to find and replace. Type in
the reference ranges say - C2:C50 and replace them with Jan!C2:C50
using replace all option.

 

[JLatham]

It would help to see an example of at least one of the formulas you have for
the Jan column.

But without seeing that, it may as relatively simple as copying the column
and then use Edit | Replace to replace the name of the monthly worksheet in
the formulas in the new column.

 

[Chieftan]

OK, Thanks. Your suggestion sounds good, but I am strugging to make it work.
For example, this formula is in the column for June on my summary worksheet
and is pulling and processing data from the June worksheet :

=SUMIF(June!Z4:Z26,"=Sole",June!AE4:AE26) /
SUMIF(June!Z4:Z26,"=Sole",June!V4:V26)

Returns a useful value to me. So, I copied the formula itself and pasted it
into the July column on the summary worksheet. Using the edit replace to find
June! and replace with July! I get a dialogue box open for each edit ... i
think it is looking to open a file of somekind?? When I cancel the dialgue
box, the formula is updated to

=SUMIF(July!Z4:Z26,"=Sole",July!AE4:AE26) /
SUMIF(July!Z4:Z26,"=Sole",July!V4:V26)

Now. Although it looks correct to me, this formula now returns an error ...
in this case a #VALUE

Fearing I may be biting off more than I can chew, I tried it on :
=June!BM13 a really simple one .... which also brings up the dialogue box
and also updates the formula, and also generates an error ... #REF.

What am I doing wrong here?
Thanks for your help so far.
Chieftan

 

[JLatham]

I think maybe the sheet names may be slightly different than what you're
typing into the formulas. While they may look like "June" and/or "July" on
the sheet's name tabs, they may have a leading or trailing blank space as
part of the name.

It's easy to check: choose a sheet OTHER than the June sheet. Pick an empty
cell for testing and enter the = symbol, then instead of typing in the
formula, click on the June sheet and then select a cell (as BM13, but any
would do for the test), and hit the enter key. Then look at the formula
generated. If I'm right, it will probably look something like =' June'!BM13
or ='June '!BM13 or even =' June '!BM13.

If it turns out that is the problem, then simply change the name of the
worksheet, making sure it has no leading/trailing white space.

 

[Chieftan]

Thank you JLatham ... that did it. Once I confirmed the sheet tab was named
correctly, I was able to convert the formulas to absolute cell references,
drag the whole column of formulas over, and use the edit/find-replace
function. Worked perfectly ... Populated each column with one step!

thanks again
Chieftan