HELP! I'm stuck

[CLoveday1982]

I am trying to make a workbook for the motel I work for to speed up our Daily Operations Report. When I add a new sheet at the bottom for a new day and copy over my formulas I can not figure out how to get the formulas to auto update to the previous day. They always go back to day 1 not the previous. I don't want to have to manually go through and change every single formula for each new worksheet. Day 1 is obviously all manually entered. Day 2 I start the formulas that refer back to the cells in Day 1. When I get to Day 3+ and copy over the formulas they all still refer to Day 1 instead of automatically referring back to Day 2 or whatever the previous day is. Here is an example of the formula I have in Day 2 =SUM('Nov 1'!D4:E4,B4). When I copy it to Day 3 I want Nov 1 to automatically update to Nov 2. I hope this is making sense. If someone could point me in the right direction or tell me what to do to make this happen I would greatly appreciate it.

[#2]

you are right, the tabs do not automatically increment when copying formulas the way rows and columns increment. There are few options:
a) (and probably best choice) is reformat/structure the workbook so all the data is entered based on date and then you can use another tab to show a monthly report using formulas or pivot tables
b) you can use INDIRECT(...) to refer to the corresponding sheet assuming you name the sheets in a consistent way. So let's say A1 is the day (e.g. "Nov 3") then
=INDIRECT("'"&TEXT($A$1-1,"MMM D")&"'!D4:E4")
will return the values from 'Nov 2'!D4:E4
Although this looks like an easy and best answer, it is a volatile function that may result in poor sheet performance (i.e. excel may run slow if you have a lot of these) and is prone to breaking since Excel can't help 'fix' the formula when you insert/delete things.
c) you could also set up like 31 days worth of sheets the way you want and then use that template workbook each month.

[#3]

@mtarler oh my goodness. Thank you so much for the reply. I am still very basic level for excel but trying to learn. I was starting to think I wouldn’t get help. I will absolutely check out these options you’ve supplied. 

[#4]

My advice is to keep your daily data in one sheet and add a column for the date. Looking ahead, you could potentially have 365 sheets in the workbook to manage. Maintaining those sheets and analyzing the information is possible but it would be a chore. Excel has plenty of room in just one sheet with 1,048,576 rows.

[#5]

@CLoveday1982, you are welcome and as @Patrick2788 restated (and probably said better) option A is the preferred method. That said we are often under time crunches and option B might get you a temporary quick fix but please make time to consider and then restructure your data so you enter it in a single table (at least all data of 1 'type') and do NOT split the data across sheet just because they are different months. Basically treat data entry as entering all relevant data and then data reporting as using excel to pull the relevant data in the way you want and making it look pretty. Then you can produce many different reports much easier.