Setting up a sheet offset?

[SAMMYD]

I am trying to set up a workbook for a daily log sheet, one sheet for every day of the month. Into a cell, I want to enter a value, which would be a meter reading, then have the value from the same cell in the previous sheet subtracted from it and entered in an adjacent cell. This gives me the daily total on the sheet. So I can do this the hard way by putting in the formula, =Q10-'Thursday 01-01-2016'!Q10 into cell R10 in sheet 'Friday 01-02-2016', and this works fine, but I have to do this 6 different times for each day/sheet, and again for every month/workbook of the year. I know there has to be an easier way. I've tried setting up a sheet offset macro but I can not get it to work. Can someone walk me through this process, or am I going about it all wrong?

Thank you

 

[MarcelBeug]

Make sure you have your sheet names set up with genuine data values (e.g. 1-1-2016 was on Friday ).

Formula solution would be similar like:

=Q10-INDIRECT("'"&TEXT(DATEVALUE(MID(CELL("filename"),FIND("]",CELL("filename"))+1,255))-1,"ddd mm-dd-yyyy")&"'!Q10")

Note: for CELL("filename") to work correctly, the workbook must be saved first (in other words it won't work in an unsaved workbook).

I tested with sheet names formatted as d-m-yyyy.

 

[SAMMYD]

You are correct, the book I was playing with was actually set up for 2015.

 

[SAMMYD]

So forgive my ignorance, but I'm not sure what I'm supposed to do with that.

 

[MarcelBeug]

Put the formula in R10.
CELL("filename") returns the file- and sheetname, the latter is the part after the "]".
Taking the DATEVALUE of the name of the current sheet, subtracting 1 and reformat as TEXT in date format, should return the name of the previous sheet.
Q10-INDIRECT("'Previous sheet'!Q10") will subtract Q10 from Q10 in the previous sheet.

 

[SAMMYD]

Ok, I follow you. That's like what i was thinking, just a lot more steps to it. However, it is not working. Do I need to format cells a certain way? I tried general, number, text. There must be something in my sheet that is keeping it from working.

 

[SAMMYD]

Ok, I follow you. That's like what i was thinking, just a lot more steps to it. However, it is not working. Do I need to format cells a certain way? I tried general, number, text. There must be something in my sheet that is keeping it from working.

Sorry i forgot, it is giving me a #VALUE! error.

 

[MarcelBeug]

Most probably either date (current sheet or previous sheet) is not properly resolved.

If you select the cell with the formula. go to the Formulas tab and use "Evaluate Formula" to step through the formula, you can see where it goes wrong.
This will likely be:
either where the DATEVALUE is calculated (then you need to adjust the name of your current sheet to a string that is recognized as a date by DATEVALUE)
or
where the TEXT function should return the name of the previous sheet.

You be honest I can't get DATEVALUE resolve a date that includes the name of the day, so the easiest way is to remove the day name from your sheet names.

If you need to include the day name in your sheetname, then the solution will be more complicated and requires defined names (tab Formulas - Name Manager).
Assuming your sheetnames end with a date formatted as mm-dd-yyyy:

SheetName: =MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)
SheetMonth: ==MID(SheetName,LEN(SheetName)-9,2)
SheetDay: =MID(SheetName,LEN(SheetName)-6,2)
SheetYear: =RIGHT(SheetName,4)

Formula in R10:

=Q10-INDIRECT("'"&TEXT(DATE(SheetYear,SheetMonth,SheetDay)-1,"dddd mm-dd-yyyy")&"'!Q10")

 

[SAMMYD]

This is the macro I used to set up the sheet names. Then I am pulling the sheet name and inserting it into my header. I don't need the day name, I thought it looked more official, and that is how we did our hand written log sheets.

Sub DoDays()
Dim J As Integer
Dim K As Integer
Dim sDay As String
Dim sTemp As String
Dim iTarget As Integer
Dim dBasis As Date

iTarget = 13
While (iTarget < 1) Or (iTarget > 12)
iTarget = Val(InputBox("Numeric month?"))
If iTarget = 0 Then Exit Sub
Wend

Application.ScreenUpdating = False
sTemp = Str(iTarget) & "/1/" & Year(Now())
dBasis = CDate(sTemp)

For J = 1 To 31
sDay = Format((dBasis + J - 1), "dddd mm-dd-yyyy")
If Month(dBasis + J - 1) = iTarget Then

If J <= Sheets.Count Then
If Left(Sheets(J).Name, 5) = "Sheet" Then
Sheets(J).Name = sDay
Else
Sheets.Add.Move after:=Sheets(Sheets.Count)
ActiveSheet.Name = sDay
End If
Else
Sheets.Add.Move after:=Sheets(Sheets.Count)
ActiveSheet.Name = sDay
End If
End If
Next J

For J = 1 To (Sheets.Count - 1)
For K = J + 1 To Sheets.Count
If Right(Sheets(J).Name, 10) > _
Right(Sheets(K).Name, 10) Then
Sheets(K).Move Before:=Sheets(J)
End If
Next K
Next J

Sheets(1).Activate
Application.ScreenUpdating = True
End Sub

 

[MarcelBeug]

This time I'm not sure what I'm supposed to do with that.