Jun 02 2022 08:00 AM - last edited on Jun 03 2022 08:42 AM by EmilyPerina
Jun 02 2022 08:00 AM - last edited on Jun 03 2022 08:42 AM by EmilyPerina
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]
Jun 02 2022 03:58 PM
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?
Jun 03 2022 06:35 AM
First timer here. Although none of the information on the sheets was accurate or relevant, you're right so here's a new link:
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.
Jun 03 2022 08:52 AM
Jun 04 2022 12:09 PM
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.