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
|
|||
|
|||
Referecing Worksheet names that are dates in a formula
Hi There,
I've got a tricky one here, and I'm not sure if anything can be done about it. Here's the situation... I've got a workbook that I use to track the number of proposals my company writes each month. There is one tab for each month, labelled with the format mmmm yyyy (i.e. November 2009, December 2009, January 2010, etc.) and a couple of summary sheets that reference data from each of the months. I need to start a new summary sheet that only looks at the last 6 months, excluding the current month, (so during March 2010, I need it to draw data from September 2009 to February 2010 tabs. The data that i need is in the same cell on each sheet. Is there a formula that I can use that would automate this? If anyone has any advice, I would very much appreciate it. -- David |
#2
|
|||
|
|||
Referecing Worksheet names that are dates in a formula
I need it to draw data from September 2009 to
February 2010 tabs. The data that i need is in the same cell on each sheet. Try this... You want to link to cell A1 on each of those sheets and have the formulas update automatically when each new month starts. Enter this formula in cell A2 on your summary sheet and copy down as needed: =INDIRECT("'"&TEXT(DATE(YEAR(NOW()),MONTH(NOW())-ROWS(A$2:A2),1),"mmmm yyyy")&"'!A1") A2 will link to February 2010 A3 will link to January 2010 A4 will link to December 2009 A5 will link to November 2009 etc etc -- Biff Microsoft Excel MVP "David McLean" wrote in message ... Hi There, I've got a tricky one here, and I'm not sure if anything can be done about it. Here's the situation... I've got a workbook that I use to track the number of proposals my company writes each month. There is one tab for each month, labelled with the format mmmm yyyy (i.e. November 2009, December 2009, January 2010, etc.) and a couple of summary sheets that reference data from each of the months. I need to start a new summary sheet that only looks at the last 6 months, excluding the current month, (so during March 2010, I need it to draw data from September 2009 to February 2010 tabs. The data that i need is in the same cell on each sheet. Is there a formula that I can use that would automate this? If anyone has any advice, I would very much appreciate it. -- David |
#3
|
|||
|
|||
Referecing Worksheet names that are dates in a formula
Just in case line wrap screws up the formula here it is in chunks. Just make
sure you enter it all on one line in the file. =INDIRECT("'"&TEXT(DATE(YEAR(NOW()), MONTH(NOW())-ROWS(A$2:A2),1), "mmmm yyyy")&"'!A1") -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... I need it to draw data from September 2009 to February 2010 tabs. The data that i need is in the same cell on each sheet. Try this... You want to link to cell A1 on each of those sheets and have the formulas update automatically when each new month starts. Enter this formula in cell A2 on your summary sheet and copy down as needed: =INDIRECT("'"&TEXT(DATE(YEAR(NOW()),MONTH(NOW())-ROWS(A$2:A2),1),"mmmm yyyy")&"'!A1") A2 will link to February 2010 A3 will link to January 2010 A4 will link to December 2009 A5 will link to November 2009 etc etc -- Biff Microsoft Excel MVP "David McLean" wrote in message ... Hi There, I've got a tricky one here, and I'm not sure if anything can be done about it. Here's the situation... I've got a workbook that I use to track the number of proposals my company writes each month. There is one tab for each month, labelled with the format mmmm yyyy (i.e. November 2009, December 2009, January 2010, etc.) and a couple of summary sheets that reference data from each of the months. I need to start a new summary sheet that only looks at the last 6 months, excluding the current month, (so during March 2010, I need it to draw data from September 2009 to February 2010 tabs. The data that i need is in the same cell on each sheet. Is there a formula that I can use that would automate this? If anyone has any advice, I would very much appreciate it. -- David |
Thread Tools | |
Display Modes | |
|
|