New Excel Question

[RandBlade]

I have a spreadsheet which is basically like a league table. Every week there are results giving points in various categories and then those get tallied into a final sum of the score for the week. The final column then is the score for the week plus the score to date from the previous week. Make sense?

Each week a new tab is created with the new date as its name, with the previous weeks basically being copied to the end to be the new weeks template then the scores put in.

I would like a method of a formula where Excel looks at a cell from the previous sheet - whatever the name of the previous sheet is. Normally in a formula you name the previous sheet but I know from Macro experience that behind-the-sceens the sheets also have a numbering system. Any way to access that in a general formula, rather than through a macro.

EG I have a cell in each table P17 that is this weeks score for the team in row 17, formula in Q17 is =P17+LastWeeksSheetQ17

[Wraith]

I don't think there's any such thing as a relative sheet reference, AFAIK. You could manage something with VBA, but you've said before that your machine is setup to disallow macros, right?

[RandBlade]

It doesn't like them. Its set up now to allow them (they were banned) but it doesn't like them still. Easier to try without, if there's a way.

[icks]

You can use Indirect() which takes references as text. If you can manipulate the current sheetname to give lastweeks sheetname using text/date functions this should work...

[RandBlade]

Yes, if I have one cell as the week's date then last weeks can be computed and Indirect would work.

[icks]

Cell("filename",$a$1) contains the worksheet name as the last part iirc from a long time ago.

[RandBlade]

Indeed it does but that's the current sheets name, not the previous sheets name

[Khendraja'aro]

Indeed it does but that's the current sheets name, not the previous sheets name

AllSheets
=GET.WORKBOOK(1+0*now())
Gets an array of all sheets in the workbook

ThisSheet
=GET.CELL(32+0*now(),indirect(”rc”,False))
Gets the name of the sheet the name is used in.

PrevSheet
=INDEX(AllSheets,MATCH(ThisSheet,AllSheets,0)-1+0*now())
Gives the name of the worksheet to the immediate left of the sheet where this name is used.

http://www.methodsinexcel.co.uk/Arti...%20before.html

[RandBlade]

Awesome!

Shame its not just a predefined function as I can't be the first person to want to do that by any means, but that's awesome