Referencing Another Sheet that is designated by a cell

[Smurlos]

Hi Everyone

I'm making a work book for my team stats at work and each day I have another sheet, the sheet is named in the format "DD-MM-YYYY" then each month I make a new workbook.

On the front page, it collates the stats for me via a series of formulae. However at the moment I have to update about 100 cells with the new DD-MM-YYYY sheet name every single day and it's quite time consuming and repetitive.

To make things quicker, I was wondering if there was a way that I could have one cell where i typed in the DD-MM-YYYY myself for today's date i.e. 01-07-2015 and then rather than having to manually update each cell for example:

Yesterday was 30-06-2015 so all my formulae will say '30-06-2015'!D15 etc. so instead of having to manually change each one to '01-07-2015'!D15 it will automatically change 30-06-2015 to whatever i write in cell D1 for example.

This way it could also pull historic data for me too just by changing the sheet name it reads from via one cell.

Does anyone have any idea what formula I would use to do so, as every formula I've created ends in disaster.

Thanks guys

[FDibbins]

each day I have another sheet, the sheet is named in the format "DD-MM-YYYY" then each month I make a new workbook.

Have you considered using only one sheet for all data input? Then have another sheet/sheets for summary/analysis? that is how excel works best

I was wondering if there was a way that I could have one cell where i typed in the DD-MM-YYYY myself for today's date

Sounds like you need INDIRECT for this, it will allow you to to something in a cell, and then use that as a reference in a formula
Can you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

[nigelbloomy]

Does this help if the date is in A1?

Please Login or Register  to view this content.