If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
how to link multiple columns to multiple worksheet totals / functi
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 |
#2
|
|||
|
|||
how to link multiple columns to multiple worksheet totals / functi
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" wrote: 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 |
#3
|
|||
|
|||
how to link multiple columns to multiple worksheet totals / fu
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" wrote: 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" wrote: 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 |
#4
|
|||
|
|||
how to link multiple columns to multiple worksheet totals / fu
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" wrote: 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" wrote: 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" wrote: 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 |
#5
|
|||
|
|||
how to link multiple columns to multiple worksheet totals / fu
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 "JLatham" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|