 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
Mac Forum / Applications / Excel / August 2007Moving Links one coloumn to the right each Month
Excel 2003 I have a "Data" worksheet with columns of "months" and rows of indicators like "Sales" and "Margin" etc. I have a "Summary" worksheet linked to the "Data" worksheet via many formulas. Each new month I want the formulas in the summary sheet to move to the new month column in the data worksheet. How can I do this? I also use ranges like "Todate" so I want to extend the range by one column each month but keep the first (or anchor) month. On 6/23/07 9:47 AM, in article 9A500B5E-8D63-4B39-9B57-43C454D715CE@microsoft.com, "Andrew Davroche" > Excel 2003 > I have a "Data" worksheet with columns of "months" and rows of indicators [quoted text clipped - 5 lines] > I also use ranges like "Todate" so I want to extend the range by one column > each month but keep the first (or anchor) month. There are a whole bunch of ways to do this. Look in help for OFFSET, and use it in the formulas to get data from the column you want. Just enter a month number somewhere to represent the month "offset." You can also use self defining names to make an adjustable size range. For example, you can define a name as YearToDate to refer to a varying number of months. The refers to field will look something like: =offset($a$1,0,0,5,counta($1:$1)) This will define an area 5 rows deep, and a varying number of columns depending on how many month names are used in row 1.  SignatureBob Greenblatt [MVP], Macintosh bobgreenblattATmsnDOTcom Thank you so much - I can change all my formulas simply by changing the data in one cell - brilliant. I wish I'd asked years ago. > On 6/23/07 9:47 AM, in article > 9A500B5E-8D63-4B39-9B57-43C454D715CE@microsoft.com, "Andrew Davroche" [quoted text clipped - 18 lines] > This will define an area 5 rows deep, and a varying number of columns > depending on how many month names are used in row 1. With your help I have been extensively using Offset. I have a set of sixteen formulas which use the same reference cell. When I want to use the same set of sixteen formulas with another reference cell I have to edit each offset formula with the new reference cell. Is there a quicker way of doing this. I have used a name "Fred" instead of say "B56" but I still need to change "Fred" sixteen times. I have read help but I am not getting it. > Thank you so much - I can change all my formulas simply by changing the data > in one cell - brilliant. I wish I'd asked years ago. [quoted text clipped - 21 lines] > > This will define an area 5 rows deep, and a varying number of columns > > depending on how many month names are used in row 1. On 8/30/07 1:38 PM, in article 1565A834-EAEC-4D82-9DE2-EB36F73F7D08@microsoft.com, "Andrew Davroche" <AndrewDavroche@discussions.microsoft.com> wrote: > With your help I have been extensively using Offset. I have a set of sixteen > formulas which use the same reference cell. When I want to use the same set [quoted text clipped - 31 lines] >>> This will define an area 5 rows deep, and a varying number of columns >>> depending on how many month names are used in row 1. Have you tried edit-Replace? SignatureBob Greenblatt [MVP], Macintosh bobgreenblattATmsnDOTcom |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.