Creating & Using a global variable referenced by many worksheets within a workbook

**number6** · 11-02-2009, 04:35 PM

Forum Members,

1 - I am not a programmer.

2 - I have a Microsoft Excel 2000 workbook with 30 worksheets within it. In location "A8" in each worksheet I manually type the last day of the month. For example: "October 31, 2009".

3 - Is there a way to place a variable in location "A8" in each worksheet so that I need only manually type "October 31, 2009" only once in the workbook? If there is a simply way; please be specific and detailed in your solution.

4 - When I need to work on the next month's activity; "November 30, 2009", I will make a copy of the previous month's workbook and change the text from "October 31, 2009" to "November 30, 2009".

5 - Thank you for your attention to this request.

**Palmetto** · 11-02-2009, 07:18 PM

There are likely other, perhaps better solutions, but here is one approach.

Use a cell on a sheet to hold the month value (e.g. 1,2,3 . . .11,12)
In my example I use cell A1 on Sheet1.

Create a Named Formula:
Name: Current_Month (or whatever you like)
Formula: =EOMONTH(DATE(YEAR(TODAY()),Sheet1!A1,1),0)

Then in A8 reference the named formula: =Current_Month

If you Group Select all sheets and select cell A8 you can globally change the current value to use the formula above by typing =Current_Month into cell A8. Then in future workbooks, just change the month value in Sheet1!A1 to update the end of month date for all sheets at once.

Creating & Using a global variable referenced by many worksheets within a workbook

LinkBack

Thread Tools

Rate This Thread

Display

Creating & Using a global variable referenced by many worksheets within a workbook

Re: Creating & Using a global variable referenced by many worksheets within a workboo

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions