Your attempted set of formulas cannot work as intended. You have the ImportHTML function in cell B1, a list of dates in column A and commands such as
=IF(TODAY()=A6,$B1)
in column B. An issue with this formula is that the condition will become FALSE after the day passes, so the data will be lost. And trying to rectify with =IF(TODAY()>=A6,$B1) would not help, since then the data will keep on changing.
The basic limitation here is that a formula cannot stop being a formula on its own. You want to "freeze" it in place, replacing with a static value. Apart from manual editing, this can only be done with a script. Here is a script that produces a record in the form similar to yours:
function addDateAndValue() {
var url = ' url of your spreadsheet '; // put url here
var ss = SpreadsheetApp.openByUrl(url);
var sh = ss.getSheetByName('Sheet1'); // or another name
var height = sh.getDataRange().getHeight();
sh.insertRowAfter(height);
sh.getRange(height+1, 1).setValue(Utilities.formatDate(new Date(), "GMT", "mm/dd/yyyy"));
sh.getRange(height+1, 2).setValue(sh.getRange("B1").getValue());
}
Namely, it adds a new row at the bottom of the current data range, puts today's date in column A and puts the value from B1 into column B. The copied value is just that, a number (or string), which is not going to change anymore.
You can make this script run daily by adding a trigger via (Resources > Current project's triggers).
Best Answer
I made a duplicate of your "Anvil - 5%" sheet (renamed "Erik Help"). There, I first added data validation to F4 (as a list) so that you can only choose the three-letter English month names. This is an extra level of fool-proofing.
Your formulas in C12 and E12 are already set up to SUMIF only entries that fall between the dates in C7 and E7, respectively. So the only thing you really needed was a way to change those two dates to the beginning and ending of the month whose three-letter English month name is shown in F4.
However, you've set your locale to Greece, while your sheet is in English. This adds a layer of complexity, because month names will only be recognized in Greek. This being the case, I placed the following formula in C7:
=IFERROR(DATEVALUE(SWITCH(F4; "Jan"; "Ιαν"; "Feb"; "Φεβ"; "Mar"; "Μαρ"; "Apr"; "Απρ"; "May"; "Μαΐ"; "Jun"; "Ιουν"; "Jul"; "Ιουλ"; "Aug"; "Αυγ"; "Sep"; "Σεπ"; "Oct"; "Οκτ"; "Nov"; "Νοε"; "Dec"; "Δεκ")&" "&1))IFERROR will return null should an error every occur. SWITCH will switch the three-letter English month name in F4 to the Greek equivalent. DATEVALUE forms a date from that three-letter Greek month name with " 1" added to it. This will always give the first of that month in the current year, since year is left off.
Once this date is in place, the formula for E7 can reference it in order to return the end of that month:
=IF(C7="";;EOMONTH(C7;0))This simply says, "If C7 is null, do nothing; otherwise, return the end of the month that is zero months away from the date in C7 (i.e., the end of that same month).
Since I set up data validation in F4, it should be impossible for you to ever have anything in F4 that would trigger an error. But it's good practice to have IFERROR in place anyway, in case things change in the future.
One final note: I could have set up a new sheet with the English and Greek three-letter month names and then used VLOOKUP in the C7 formula. But since this is a closed set, I figured it would be easier for you to understand if I just used SWICH directly in the formula.