Forum Discussion
Compare Columns of Dates In A Formula
Ok Got it. It will help me if we can place values in it so I can see how it currently operates, and how you want it to. Can we place some items in each Skins/Consumables, then see how the buying checklist currently works? Also, I do not see the formula based on the date anywhere.
The formula for creating the date is causing me issues. Every formula I type is giving me a blank cell/not working since there is another page. I think the problem, for me..., is comparing the new Hidden dates to whatever cell is next on the list... Don't know how to solve this. I unhided all the cells and input some sample data in. New link for the excel file is below. Everything on the right side of the Checklist can be typed into Item for sorting.
https://drive.google.com/file/d/1CFyv1O9glRqwAxhhBuwiK_nP2qgKGnpy/view?usp=sharing
- Ok, in cell F9 of the consumable tab I put =IF(E9<'Buying Checklist'!$K$3,"",W9). This will put nothing if the date (buy) is less than the date on the buying checklist. Drag it down. Then, change the sumif formula (amount bought-buying checklist), to point to column F instead of W for all instances and it seems to work. Do the same thing on the skins page.
- I took a second look at the Buying Checklist. If the sumif is based on the "Estimated Start" date for all entries, then no lookup is needed. It can simply be referenced.
Instead of putting a date in that cell, (hidden date) have the formula resolve the amount you want counted instead, if the conditions are met. In your sumif formula range, point it to the hidden date range. It will count what is there if it meets the conditions, and what is there only meets your date condition.
The hidden date formula will have to be a lookup formula looking at the buying checklist page, and have an iferror clause so it zeros out when no value is found.