Forum Discussion
Using the Date function in
Hello everyone,
I have a cashflow spreadsheet which predicts cashflow on all of my accounts.
I have used the MIN function to calculate the lowest balance based on the date range pre-selected. Using INDEX and MATCH I get the date when it happens. However, I would like to define the date ranges based on todays date, and also as I may add extra rows to use the last row on the sheet with data in it.
Formula is =INDEX(A218:A322,MATCH(MIN(C218:C322),C218:C322,0)) with the MIN formula based on totals in column C and the date in column A. To update it now I would need to change values for A218 and C218, as well as possibly A322 and C322 - not very elegant! I know it could proably be done using range and offset in VBA and replacing both ranges with a variable, but if anyone can come up with a solution without VBA I would be grateful. I would also welcome any other suggestions?
How do you define the date range based on the current date?
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
