Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A formula to identify tab names in a worksheet | Excel Discussion (Misc queries) | |||
Formula using worksheet names 2 | Excel Worksheet Functions | |||
Formula using worksheet names | Excel Worksheet Functions | |||
Worksheet Tabs Names as Dates | Excel Worksheet Functions | |||
How to link Excel worksheet tab names to dates in each worksheet? | Excel Worksheet Functions |