Refer to previous page in formula?

[svendiamond]

I create a new sheet every weekday and use the date as the name of the sheet. I have a formula in one column that counts the "days at site" that tracks the number of days a trailer has been parked. It's a simple SUMIF that finds if a trailer was on the previous sheet, and if so, returns the number from "days at site" and adds +1. Otherwise it just adds 1.

Here's what the formula for my first trailer looks like today under "Days at site" (E2)

=IF(ISBLANK(A3),"",(SUMIF('7.1'!A:A,A3,'7.1'!E:E)+1))

So if column A is blank, return blank -- otherwise, it finds whether the trailer number in column A was on the previous sheet ('7.1') and returns the "days" from column E, and adds 1.

Every day I create a create a new sheet from the template (macro button) but I have to manually go in and adjust the formula to change the date. I've Googled and Googled and I can't seem to find a way to refer to the previous sheet within this formula. Any help you can provide would be much appreciated! Thanks!

 

[Gerald Higgins]

Hi there. This might not be the answer you want, but if I were you I would give serious consideration to NOT creating a new sheet every day. It is usually preferable to hold all your data on a single sheet, doing all the calculations on that sheet (or possibly one more single sheet) and then you can use various techniques such as Data, Filter, to focus on "today's" activity, which can then be handled in a separate sheet if required.

 

[svendiamond]

Thanks for the response. I get a new list of trailers every day, and send it out to those companies. That's why I have a separate sheet so they can just look at a simple list of trailers for the day with the amount of days at the site.

I suppose I could put them all on the same sheet, and have a column for the date... then I could use Filter to just see today's date. But how would I get the "days at site"? I couldn't "SUMIF" because it would be a circular reference, no? And when you say today's activity could be handled in a separate sheet, what do you mean?

I appreciate the help!

 

[Gerald Higgins]

It's difficult to provide precise answers without knowing more about what your data looks like.

For example, would a SUMIF formula be a circular reference ? I have no idea without knowing what your data looks like, and what SUMIF formula you are proposing.

When I said you could handle today's activity on a separate sheet, what I meant was you could hold all your data in one sheet, then use something like Data, Filter, to select today's activity, and then if you need to send that out to other people, you could copy today's data to a separate sheet or even a separate workbook.

 

[svendiamond]

Here's what it looks like:

The issue is column E. Every time I create a new sheet (macro button which copies current sheet and renames with next date) I have to go in and update the formula in column E to change the date to the previous sheet. (e.g. today 7.10 I will have to change the formula from =SUMIF('7.8'!A:A,A3,'7.8'!E:E)+1 to =SUMIF('7.9'!A:A,A3,'7.9'!E:E)+1

One way I can make it work is to simply create all the next few days/weeks worth of pages ahead of time and update the formulas. That's what I've been doing lately because if I'm not here, no one else in the office knows how to update the formulas.