| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with changing formula linked to another worksheet
I have excel 2003
I have a workbook that has numbers relating to each month of the year for several different work areas. There are 4 weeks of data on each sheet with a summary page showing the average for the 4 weeks for each work area. I made a ytd workbook where I have a page for each work area that will show the the months listed with the average listed beside it. I linked to the first workbook. The problem is, how can I drag the formula down so the month changes? When I drag January's average down to February's, it still is linked to January in the formula. I tried grouping all the pages and clicking in February's cell and changing January to February, but it changed all the cell references to the first sheet also so that didn't work. Then I tried using Find and REplace under Edit, but that changed ALL the January's to February everywhere! What am I doing wrong? Thanks :) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Problem with changing formula linked to another worksheet
Let me try to make this problem clearer...
Actually for all the work areas, there is a work book for January, one for February, one for March... etc. I made a yTD workbook with a page for each work area that has a column with all the months and beside January, I linked to the Workbook for January for each work area and the number is there on each page. Fine. But when I drag the formula down for February, it doesn't change January to February in the Formula... how can I get the month to change without changing anything else in the formula? It's quite a few pages and I don't want to have to go to each one and do it manually. "Meenie" wrote: I have excel 2003 I have a workbook that has numbers relating to each month of the year for several different work areas. There are 4 weeks of data on each sheet with a summary page showing the average for the 4 weeks for each work area. I made a ytd workbook where I have a page for each work area that will show the the months listed with the average listed beside it. I linked to the first workbook. The problem is, how can I drag the formula down so the month changes? When I drag January's average down to February's, it still is linked to January in the formula. I tried grouping all the pages and clicking in February's cell and changing January to February, but it changed all the cell references to the first sheet also so that didn't work. Then I tried using Find and REplace under Edit, but that changed ALL the January's to February everywhere! What am I doing wrong? Thanks :) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Problem with changing formula linked to another worksheet
What does your formula look like?
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Meenie" wrote in message ... Let me try to make this problem clearer... Actually for all the work areas, there is a work book for January, one for February, one for March... etc. I made a yTD workbook with a page for each work area that has a column with all the months and beside January, I linked to the Workbook for January for each work area and the number is there on each page. Fine. But when I drag the formula down for February, it doesn't change January to February in the Formula... how can I get the month to change without changing anything else in the formula? It's quite a few pages and I don't want to have to go to each one and do it manually. "Meenie" wrote: I have excel 2003 I have a workbook that has numbers relating to each month of the year for several different work areas. There are 4 weeks of data on each sheet with a summary page showing the average for the 4 weeks for each work area. I made a ytd workbook where I have a page for each work area that will show the the months listed with the average listed beside it. I linked to the first workbook. The problem is, how can I drag the formula down so the month changes? When I drag January's average down to February's, it still is linked to January in the formula. I tried grouping all the pages and clicking in February's cell and changing January to February, but it changed all the cell references to the first sheet also so that didn't work. Then I tried using Find and REplace under Edit, but that changed ALL the January's to February everywhere! What am I doing wrong? Thanks :) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Problem with changing formula linked to another worksheet
='C:\Documents and Settings\hawthos1.RHS\Desktop\Susan''s Documents\Dir
Michelle W\Chart and Falls Audits\Falls Audits\[January 08 Falls Compliance.xls]Compliance Question 2'!$B$9 I want to be able to move it down one cell in all of my worksheets so "January" changes to "February" but nothing else changes in the formula.??? "RagDyer" wrote: What does your formula look like? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Meenie" wrote in message ... Let me try to make this problem clearer... Actually for all the work areas, there is a work book for January, one for February, one for March... etc. I made a yTD workbook with a page for each work area that has a column with all the months and beside January, I linked to the Workbook for January for each work area and the number is there on each page. Fine. But when I drag the formula down for February, it doesn't change January to February in the Formula... how can I get the month to change without changing anything else in the formula? It's quite a few pages and I don't want to have to go to each one and do it manually. "Meenie" wrote: I have excel 2003 I have a workbook that has numbers relating to each month of the year for several different work areas. There are 4 weeks of data on each sheet with a summary page showing the average for the 4 weeks for each work area. I made a ytd workbook where I have a page for each work area that will show the the months listed with the average listed beside it. I linked to the first workbook. The problem is, how can I drag the formula down so the month changes? When I drag January's average down to February's, it still is linked to January in the formula. I tried grouping all the pages and clicking in February's cell and changing January to February, but it changed all the cell references to the first sheet also so that didn't work. Then I tried using Find and REplace under Edit, but that changed ALL the January's to February everywhere! What am I doing wrong? Thanks :) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Problem with changing formula linked to another worksheet
"Meenie" wrote: ='C:\Documents and Settings\hawthos1.RHS\Desktop\Susan''s Documents\Dir Michelle W\Chart and Falls Audits\Falls Audits\[January 08 Falls Compliance.xls]Compliance Question 2'!$B$9 I want to be able to move it down one cell in all of my worksheets so "January" changes to "February" but nothing else changes in the formula.??? I can drag the cell down to the next cell, then manually change the formula from "january" to "february" but is there a way to have it change when you drag it? "RagDyer" wrote: What does your formula look like? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Meenie" wrote in message ... Let me try to make this problem clearer... Actually for all the work areas, there is a work book for January, one for February, one for March... etc. I made a yTD workbook with a page for each work area that has a column with all the months and beside January, I linked to the Workbook for January for each work area and the number is there on each page. Fine. But when I drag the formula down for February, it doesn't change January to February in the Formula... how can I get the month to change without changing anything else in the formula? It's quite a few pages and I don't want to have to go to each one and do it manually. "Meenie" wrote: I have excel 2003 I have a workbook that has numbers relating to each month of the year for several different work areas. There are 4 weeks of data on each sheet with a summary page showing the average for the 4 weeks for each work area. I made a ytd workbook where I have a page for each work area that will show the the months listed with the average listed beside it. I linked to the first workbook. The problem is, how can I drag the formula down so the month changes? When I drag January's average down to February's, it still is linked to January in the formula. I tried grouping all the pages and clicking in February's cell and changing January to February, but it changed all the cell references to the first sheet also so that didn't work. Then I tried using Find and REplace under Edit, but that changed ALL the January's to February everywhere! What am I doing wrong? Thanks :) |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Problem with changing formula linked to another worksheet
=INDIRECT("'C:\Documents and Settings\hawthos1.RHS\Desktop\Susan''s
Documents\Dir Michelle W\Chart and Falls Audits\Falls Audits\["&TEXT(DATE(2008,ROW(A1),1),"mmmm yy")&" Falls Compliance.xls]Compliance Question 2'!$B$9") and copy down. -- David Biddulph "Meenie" wrote in message ... ='C:\Documents and Settings\hawthos1.RHS\Desktop\Susan''s Documents\Dir Michelle W\Chart and Falls Audits\Falls Audits\[January 08 Falls Compliance.xls]Compliance Question 2'!$B$9 I want to be able to move it down one cell in all of my worksheets so "January" changes to "February" but nothing else changes in the formula.??? "RagDyer" wrote: What does your formula look like? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Meenie" wrote in message ... Let me try to make this problem clearer... Actually for all the work areas, there is a work book for January, one for February, one for March... etc. I made a yTD workbook with a page for each work area that has a column with all the months and beside January, I linked to the Workbook for January for each work area and the number is there on each page. Fine. But when I drag the formula down for February, it doesn't change January to February in the Formula... how can I get the month to change without changing anything else in the formula? It's quite a few pages and I don't want to have to go to each one and do it manually. "Meenie" wrote: I have excel 2003 I have a workbook that has numbers relating to each month of the year for several different work areas. There are 4 weeks of data on each sheet with a summary page showing the average for the 4 weeks for each work area. I made a ytd workbook where I have a page for each work area that will show the the months listed with the average listed beside it. I linked to the first workbook. The problem is, how can I drag the formula down so the month changes? When I drag January's average down to February's, it still is linked to January in the formula. I tried grouping all the pages and clicking in February's cell and changing January to February, but it changed all the cell references to the first sheet also so that didn't work. Then I tried using Find and REplace under Edit, but that changed ALL the January's to February everywhere! What am I doing wrong? Thanks :) |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Problem with changing formula linked to another worksheet
The bad news is that David's suggestion to use =indirect() requires that the
sending workbook be open. And then you wouldn't need the drive/path information in the formula. But the good news is that Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ That includes =indirect.ext() that allows you to return values from closed workbooks. David Biddulph wrote: =INDIRECT("'C:\Documents and Settings\hawthos1.RHS\Desktop\Susan''s Documents\Dir Michelle W\Chart and Falls Audits\Falls Audits\["&TEXT(DATE(2008,ROW(A1),1),"mmmm yy")&" Falls Compliance.xls]Compliance Question 2'!$B$9") and copy down. -- David Biddulph "Meenie" wrote in message ... ='C:\Documents and Settings\hawthos1.RHS\Desktop\Susan''s Documents\Dir Michelle W\Chart and Falls Audits\Falls Audits\[January 08 Falls Compliance.xls]Compliance Question 2'!$B$9 I want to be able to move it down one cell in all of my worksheets so "January" changes to "February" but nothing else changes in the formula.??? "RagDyer" wrote: What does your formula look like? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Meenie" wrote in message ... Let me try to make this problem clearer... Actually for all the work areas, there is a work book for January, one for February, one for March... etc. I made a yTD workbook with a page for each work area that has a column with all the months and beside January, I linked to the Workbook for January for each work area and the number is there on each page. Fine. But when I drag the formula down for February, it doesn't change January to February in the Formula... how can I get the month to change without changing anything else in the formula? It's quite a few pages and I don't want to have to go to each one and do it manually. "Meenie" wrote: I have excel 2003 I have a workbook that has numbers relating to each month of the year for several different work areas. There are 4 weeks of data on each sheet with a summary page showing the average for the 4 weeks for each work area. I made a ytd workbook where I have a page for each work area that will show the the months listed with the average listed beside it. I linked to the first workbook. The problem is, how can I drag the formula down so the month changes? When I drag January's average down to February's, it still is linked to January in the formula. I tried grouping all the pages and clicking in February's cell and changing January to February, but it changed all the cell references to the first sheet also so that didn't work. Then I tried using Find and REplace under Edit, but that changed ALL the January's to February everywhere! What am I doing wrong? Thanks :) -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Problem with changing formula linked to another worksheet
Hmmm I tried this and got #ref! What am I supposed to replace in the formula?
"David Biddulph" wrote: =INDIRECT("'C:\Documents and Settings\hawthos1.RHS\Desktop\Susan''s Documents\Dir Michelle W\Chart and Falls Audits\Falls Audits\["&TEXT(DATE(2008,ROW(A1),1),"mmmm yy")&" Falls Compliance.xls]Compliance Question 2'!$B$9") and copy down. -- David Biddulph "Meenie" wrote in message ... ='C:\Documents and Settings\hawthos1.RHS\Desktop\Susan''s Documents\Dir Michelle W\Chart and Falls Audits\Falls Audits\[January 08 Falls Compliance.xls]Compliance Question 2'!$B$9 I want to be able to move it down one cell in all of my worksheets so "January" changes to "February" but nothing else changes in the formula.??? "RagDyer" wrote: What does your formula look like? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Meenie" wrote in message ... Let me try to make this problem clearer... Actually for all the work areas, there is a work book for January, one for February, one for March... etc. I made a yTD workbook with a page for each work area that has a column with all the months and beside January, I linked to the Workbook for January for each work area and the number is there on each page. Fine. But when I drag the formula down for February, it doesn't change January to February in the Formula... how can I get the month to change without changing anything else in the formula? It's quite a few pages and I don't want to have to go to each one and do it manually. "Meenie" wrote: I have excel 2003 I have a workbook that has numbers relating to each month of the year for several different work areas. There are 4 weeks of data on each sheet with a summary page showing the average for the 4 weeks for each work area. I made a ytd workbook where I have a page for each work area that will show the the months listed with the average listed beside it. I linked to the first workbook. The problem is, how can I drag the formula down so the month changes? When I drag January's average down to February's, it still is linked to January in the formula. I tried grouping all the pages and clicking in February's cell and changing January to February, but it changed all the cell references to the first sheet also so that didn't work. Then I tried using Find and REplace under Edit, but that changed ALL the January's to February everywhere! What am I doing wrong? Thanks :) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Changing worksheet name within a formula (automatically) | Excel Discussion (Misc queries) | |||
| Changing a linked formula ... ... ... | Excel Worksheet Functions | |||
| "Formula too long" when changing linked sources?? | Excel Discussion (Misc queries) | |||
| Changing a Worksheet Reference Formula using a drop down box | Excel Discussion (Misc queries) | |||
| Replace worksheet name in formula linked to a different workbook | Excel Discussion (Misc queries) | |||
Hybrid Mode
