Forum Discussion
HELP! I'm stuck
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 ...
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.
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.
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.
