Change Cell References Based on Year

[BryceDavis]

I have a spreadsheet I'm trying to do for work that has a summary sheet that would update automatically every year to reflect if there are any rent increases or expirations based on other sheets filled with data sorted per year. I can't seem to figure out how to accomplish this (or if it is even possible) but I am running out of ideas. I have played with a few different formulas and ideas in the spreadsheet but can't seem to find something that would work. Instead of explaining what I did, I will attach a link to google drive to get the file if anyone would be so kind as to take a look and offer any tips.

Thanks in advance for any possible info or advice.

[Link removed by Admin]

[#2]

@BryceDavis 

Well, a couple of observations here to get started.

First, and very important: these are obviously REAL leases that you've listed, and although the information isn't super revelatory--you don't show actual rental rates--I would think that your clients/customers would not be happy to see this in the public domain, which it is. So I would suggest you remove that link and create a dummy file for us to work with.

Second: you use dates poorly. I'd recommend changing the format to show the full date (6/2/22 rather than just 2-June) and you'll see that what you've pretty clearly copied is the date from 2020 (or whatever the starting year was) into subsequent years. For the sake of data integrity--and ongoing functionality--you'll want those dates to be dates in the actual year in question.

Third: Do you not want a count (as your formula in cell D4 might suggest) of the number of increases in a given month and then a total of that for the year? Why, then, just an "x" in the cell in this image? Why not a count of the number of rent increases in Feb (which, by the way, would be zero given the current data on that sheet)? Said another way, what are you actually hoping the summary will show?

Fourth: although you and I as humans might work better with separate sheets for each of the major physical locations, Excel would produce your summaries far more readily if you created all this as a SINGLE database--the formulas will be a lot easier to create and maintain. You will always be able to extract the data for a single location from that comprehensive database. In other words, consider designing the workbook for the computer to do the heavy lifting; don't just take ledger sheets that you've been working with on paper and create a separate Excel sheet for each of those paper ledger sheets. Are you willing to consider a re-design like that?

[#3]

@mathetes 

First timer here. Although none of the information on the sheets was accurate or relevant, you're right so here's a new link:

https://docs.google.com/spreadsheets/d/1RWD4YSvgKLDkLpeO3Y5AZNzZ_aB3SDNQ/edit?usp=sharing&ouid=10850...

I'll answer questions one by one to hopefully avoid confusion:

Firstly, thank you for your time to look into this. Big big help for me.

I can reformat the dates. The reason I had them that way in the sheets was so people could quickly look at them and see "oh hey I have a change on the 1st of this month". Not a big deal, those can be reformatted. I will update the years as well this is just a dummy copy that I'm trying to get formulated.

I'm not looking for a count of changes for the particular month, the first sheet is a summary and I just want a placeholder to show that there is a change for this year in that particular month, then go into that sheet to find the particular lease it goes for. Looking for something like =IF(increase for jan 2022=true, "x",""). The "Increase?" column is to state "YES" or "NO" based on the count of

=IF(COUNTIF(E4;P4, "x"), "YES","NO")

And expiration as

=IF(COUNTIF('sheet'!range, "EXP"), "YES", "NO")

(Formula in example is just placeholder example, not what I actually use)

This summary sheet is an attempt at the most dumbed down version of "do i need to alert of a change" as possible. I am flexible if it needs to be different for formulating sake, but this is the goal.

While I am flexible in redesigning, this is the design that everyone is used to and that I would like to maintain. This is my first whack at it and I want it to be as easy to read as possible for others and automatically update based on the year so I don't have to alter cell references every year.

The ultimate goal is to maintain this particular design (up for change though if needed), but to have the cell references that reference current-year data change based on the year in column C on the summary sheet to show updated info for the current year. Quite a long shot and I've sat in front of it for hours with not much progress. I'm all ears for advice though if you think one way would be easier than another or if this is even possible with the current design.

[#4]

I'm sorry to butt in but just wanted to mention that @mathetes is right that entering the full date will be very helpful for use over time BUT that doesn't mean you can't FORMAT that set of cells to SHOW it in the format you prefer "2-Feb". I also like his idea to show a count of how many increases are in that month. If you are doing the work why not give more info but if you really want to only show 'X' you do that too using Cell Format and create a custom format for those cells to display "X" for any number. Then the actual Value of the cell would be the total count but visually it would just show "X". Why both you ask? Because at the end of the Year you could SUM across the months to show total number of increases.
Lastly, again I agree with putting all the data into a single table and then you can have 'reporting' sheets in this format that only lookup the data and display it. So user can still look at the sheet in the format they are used to but the data is better organized and easier to manipulate and lookup.

[#5]

@BryceDavis 

You've written what I'm copying here, and below, in italics:

I can reformat the dates. The reason I had them that way in the sheets was so people could quickly look at them and see "oh hey I have a change on the 1st of this month". Not a big deal, those can be reformatted. I will update the years as well this is just a dummy copy that I'm trying to get formulated.

I beg to differ: it IS a big deal. One of the things you need to take very seriously--in designing a database and then a program (which is what an Excel spreadsheet is) to process that data--is a concept known as data integrity. Data integrity has more than one dimension to it, but at a minimum involves being accurate on such things as dates. Even in the dummy copies that you're creating to test a concept, be accurate/precise on those things that are key ingredients to what you're trying to monitor, which in this case IS the dates. You can be casual about the names, sure, but in this case you're creating a program to monitor dates, so put real dates in. It's not just a cosmetic thing.

Another aspect of data integrity would be that of consistency when it comes to the array of columns. You aren't. You've got some hidden columns in some sheets. All of that can get in the way when it comes to writing formulas to extract data. And an easy, self-regulating, way to be consistent on that aspect of design is to have a single database rather than separate sheets for each site. The latter approach is prone to --"oh, we need to add a column to track X on this site"--so design the single database to handle all of them the same, even if that means leaving a column or two empty on some. But that way you DO have a comparable pieces of information, for each location, in the same column.

So I want to challenge you--or request, if you prefer--to go in and continue making a clean single database sheet that represents, oh, three or four of your separate sites. I started to do it--which is when i encountered some of the inconsistencies and discrepancies I mention above.

• Instead of having a separate sheet for each site, you can combine multiple sites (each with its own several lessees in different rows) by simply adding a column that represents the site itself. Excel can easily handle the creation of a summary sheet for each site or each year (or each whatever) from that consolidated database.