Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing a Sheet based on the Month
Hello.
Is there a way to tell Excel to reference a sheet in a formula based on a variable like the month? Currently I am working on a project where new data is added each month and then is calculated on a summery sheet. Now every month the summery needs to be updated to reflect the new month. So I want to be able to add the new sheets to the workbook, then tell Excel to recalculate the data using the most current month. Here is one of my calculations from the summery sheet for example. =COUNTIF('Feb-08 Hires'!$J:$J,"New York") What I am trying to do is figure a way to have Excel change the calculation but base it off of the next month once that sheet is added. Basicly tell it to change the "Feb-08" part and reference the next sheet once its added, which would be "Mar-08". Right now I have to go in and change every formula by hand. I have no problem renaming the sheets if needed. Using Excel 2003. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing a Sheet based on the Month
Hi John,
I don't think there is any way of doing what you want without using VBA but do you realize that you can use Find/Replace to replace the worksheet name in all the formulas on the worksheet. Just set the options to Lookin formulas and leave the Match entire cell contents unchecked. If you initially use the Find Next and then Replace a few times before clicking Replace All then it gives you an opportunity to check that you are finding and replacing the right ones. -- Regards, OssieMac "John" wrote: Hello. Is there a way to tell Excel to reference a sheet in a formula based on a variable like the month? Currently I am working on a project where new data is added each month and then is calculated on a summery sheet. Now every month the summery needs to be updated to reflect the new month. So I want to be able to add the new sheets to the workbook, then tell Excel to recalculate the data using the most current month. Here is one of my calculations from the summery sheet for example. =COUNTIF('Feb-08 Hires'!$J:$J,"New York") What I am trying to do is figure a way to have Excel change the calculation but base it off of the next month once that sheet is added. Basicly tell it to change the "Feb-08" part and reference the next sheet once its added, which would be "Mar-08". Right now I have to go in and change every formula by hand. I have no problem renaming the sheets if needed. Using Excel 2003. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing a Sheet based on the Month
Was not aware of that. Thanks for the help!
"OssieMac" wrote: Hi John, I don't think there is any way of doing what you want without using VBA but do you realize that you can use Find/Replace to replace the worksheet name in all the formulas on the worksheet. Just set the options to Lookin formulas and leave the Match entire cell contents unchecked. If you initially use the Find Next and then Replace a few times before clicking Replace All then it gives you an opportunity to check that you are finding and replacing the right ones. -- Regards, OssieMac "John" wrote: Hello. Is there a way to tell Excel to reference a sheet in a formula based on a variable like the month? Currently I am working on a project where new data is added each month and then is calculated on a summery sheet. Now every month the summery needs to be updated to reflect the new month. So I want to be able to add the new sheets to the workbook, then tell Excel to recalculate the data using the most current month. Here is one of my calculations from the summery sheet for example. =COUNTIF('Feb-08 Hires'!$J:$J,"New York") What I am trying to do is figure a way to have Excel change the calculation but base it off of the next month once that sheet is added. Basicly tell it to change the "Feb-08" part and reference the next sheet once its added, which would be "Mar-08". Right now I have to go in and change every formula by hand. I have no problem renaming the sheets if needed. Using Excel 2003. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing a Sheet based on the Month
On Mar 4, 11:41*pm, John wrote:
Hello. Is there a way to tellExceltoreferenceasheetin a formula based on a variable like the month? Currently I am working on a project where new data is added each month and then is calculated on a summerysheet. Now every month the summery needs to be updated to reflect the new month. So I want to be able to add the new sheets to the workbook, then tellExcelto recalculate the data using the most current month. Here is one of my calculations from the summerysheetfor example. =COUNTIF('Feb-08 Hires'!$J:$J,"New York") What I am trying to do is figure a way to haveExcelchange the calculation but base it off of the next month once thatsheetis added. Basicly tell it to change the "Feb-08" part andreferencethe nextsheetonce its added, which would be "Mar-08". Right now I have to go in and change every formula by hand. I have no problem renaming the sheets if needed. UsingExcel2003. John, in your summary sheet enter the latest month's sheet name in a cell. So enter "Feb-08 Hires" into cell F3 say. Then change your formula (and similarly all other formulas in your summary sheet) to =COUNTIF(INDIRECT("'"&F3&"'!$J$J"),"New York") Formulas will then automatically refence the sheet whose name you enter into cell F3. Just to make it easier to read, the formula is ... ( " ' " &F3& " ' ! ... after I add spaces. Stefan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need an excel sheet to creating a new folder every month, and save a new spreadsheet every day, untill the next month, when it creates a new folder | Excel Programming | |||
How do I generate a new sheet based on date/week/month? | Excel Worksheet Functions | |||
Referencing data in different worksheet based on month name | Excel Worksheet Functions | |||
transfer cell $ amount to other sheet month-to-month without overc | Excel Discussion (Misc queries) | |||
Move data from on sheet to another based on month | Excel Programming |