Forum Discussion
Change Cell References Based on Year
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=108508434553019106707&rtpof=true&sd=true
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.
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.