two problems - referencing a cell in a "previous" worksheet, and automatically naming a worksheet to

Posted by Aaron on September 14, 2001 6:11 AM

Hi, my problem may seem rather remedial to some of you, but I haven't been able to figure it out so here goes:
I use Excel for my weekly timesheet at work. I enter my hours and it automatically calculates my daily and weekly totals, etc. I use a separate sheet in the same workbook for each week. On the header is the starting date (Monday) and the ending date (Sunday) of that week. The Sunday date is simply a formula which adds 6 to the Monday date. What I'd like to do though, is have the cell containing the Monday date look at the "Previous" sheet in the workbook, at the Sunday date, and add one to it. I don't know the code for "previous sheet". Obviously I want the same formula to work in every sheet, so I can't call the Previous sheet by name, since the name of each sheet is different.
Which segues nicely into the second part of my problem. I name each worksheet to match the date in the Monday cell. Is there a macro to do that automatically for me (look at the contents of a cell and rename that worksheet to match those contents)?
Thanks for any help,
Aaron

Posted by Mark W. on September 14, 2001 7:48 AM

Aaron, if Sheet2!A1 contains the text value, "091401",
the the formula, =INDIRECT(A1&"!A1"), will return
the value in '091401'!A1.

Posted by Cory on September 14, 2001 8:01 AM

For the first one, the code is:

ActiveSheet.Previous.Select

But this won't work from the first sheet in the workbook. If you're trying to reference the previous sheet using a formula, I don't think it can be done. Sorry

A suggestion, though, would be if you're already going to use the contents of a cell (a date, you said)to name the sheet, then why not use that same cell to determine the header of your sheet?

Try this:
Say cell A1 has the date in it that you're going to use to make your header and page name "Monday". Make another cell, one you can hide, equal to A1, then format it: number -> date -> custom -> "dddd" (this last part you'll just type in yourself.

Now you can use this code:

With ActiveSheet.PageSetup
.CenterHeader = Range("G3").text
End With
ActiveSheet.Name = Range("G3").Text

The G3 is the cell I used whose column I later hid.

Cory

Posted by Aaron on September 14, 2001 12:53 PM

Thanks for the help. Let me clarify a bit. I won't use the formula on the first sheet, since I foresaw the same problem you mentioned. On the first sheet in the workbook I'll manually enter the date for Monday (for example, cell B7 contains 10-Sep-2001). That worksheet is also named 10-Sep-2001. Cell C7 on that worksheet contains the formula
=B7+6
and returns the data
16-Sep-2001.

What I want is in the next sheet in the workbook (named 17-Sep-2001) in cell B7 enter a formula something like
='PreviousSheet'!C7+1
But of course that makes it look for an actual sheet NAMED "PreviousSheet" so it won't work. What is the syntax I need to use there?

And to clarify the second part, I want the sheet to AUTOMATICALLY take the name of whatever is returned in cell B7.

Thanks,
Aaron