Please Help...

I have several sheets with dates as names in a workbook for my expenses...so sheet names are 01-01-07, 01-08-07, 01-15-07...etc...

I want to carry expense info from prior week (or prior sheet) into new week...

I need a cell function to return the prior (or adjacent) sheet name...I dont always know it from the sheet....not always 1 week behind, etc.

so if working on sheetname 01-08-07, I want the sheet to be able to return in a cell the adjacent sheet name (or maybe just an adjacent sheet value) without knowing the name of the adjacent sheet....

Any ideas?

Thanks

Lets say you name 2 sheets, Sheet1 and Sheet2.

On Sheet1 you put the value of 10 in cell A1.

You can create a dynamic reference to this value on the same sheet by entering into A2 "=A1". Of course if you copy and paste A2 to B2 the value changes to "=B1" which obviously doesn't exist.

You can make a static reference by adding '$' signs to the Cell Row/Column.
A2 could have entered "=$A$1" Now you'll find if you copy A2 to B2 it will equal the same as A1.

If you enter "=$A1" then it will stay set to A column but change number when you copy to a different row.

If you enter "=A$1" then it will stay set to Row 1 but change Letter when you copy to a different column.

Now lets say you want to access A1 on Sheet2, you have to reference the Sheet name before the cell name.

Lets say you enter into A1 on Sheet2, "=Sheet1.A1" this will dynamically link the sheet and cell. However if you want to stop the sheet from changing should you paste into another sheet add the '$' in front of it.

"=$Sheet1.A1" means the sheet is static but the Cell is dynamic. "=$Sheet1.$A$1" means the whole thing is static.

Doesn't work, Stryder. I don't think Excel has a simple method of relative Sheet referencing. Not Excel 2003, anyway... maybe 2007 does???

fox99221,
I can think of a couple of ways to do it, but none are simple or ideal.
The most elegant involves using some VBA code, but this means that you'll get a macro warning when you open the spreadsheet.
Other ways avoid using VBA code, but involve adding clunky workarounds to your sheets that will break if you add, delete, or rename sheets.

I didnt think so...

Thanx anyways guys....

Cheers

Doesn't work, Stryder. I don't think Excel has a simple method of relative Sheet referencing. Not Excel 2003, anyway... maybe 2007 does???

fox99221,
I can think of a couple of ways to do it, but none are simple or ideal.
The most elegant involves using some VBA code, but this means that you'll get a macro warning when you open the spreadsheet.
Other ways avoid using VBA code, but involve adding clunky workarounds to your sheets that will break if you add, delete, or rename sheets.

Well I admit I use Open Office, however I've seen it work with some Excel sheets.

Open Office is freely available at OpenOffice.org, it utilises Java so actually free's up some space per Spreadsheet (They aren't so many kb in size).

I actually prefer it over Excel which is why I'm a little out of touch with Excel. You can also import old Excel sheets into your Open Office spreadsheet, so you don't have to redo all the sheets you previously did.

Good call! I didn't think of trying Calc :o