Google-sheets – How to get data to fill alternative cells when data is present in a zigzag pattern

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).

EXPLANATION OF BAD FORMULA

=MATCH(E6,indirect("'5 Star Gear Sets (Hidden)'!E6:E"),0) =MATCH(B6,indirect("'5 Star Gear Sets (Hidden)'!A6:A"),0)

The extra formula starting with a second = is the same as wrapping the entire formula with AND( , ).

=AND(MATCH(E6,indirect("'5 Star Gear Sets (Hidden)'!E6:E"),0),MATCH(B6,indirect("'5 Star Gear Sets (Hidden)'!A6:A"),0)

This Formula does not work because it checks to see if E6 matches any cell in E6 thru E of the referenced sheet and then also checks if B6 matches any cell in A6 thru A of the referenced sheet. Once it finds the first match in the reference sheet, it does NOT use the same row in the range of the second MATCH function.

=MATCH(search_key, range, [search_type])

Example: =MATCH(A6,B1:B100,0)

Using MATCH in a Conditional Format will return True or False.

DISCLAIMER

I wrote the answer for a different sheet than the one listed in the original question. If using link provided in the question for the spreadsheet, the answer is meant to be applied to what would normally be considered Sheet1.

ANSWER

Apply to Range: E6:E

Format Cell if...: Custom Formula

Formula: =AND(NOT(FILTER(INDIRECT("'6 Star Gear Sets (Hidden)'!D$6:D"),INDIRECT("'6 Star Gear Sets (Hidden)'!A$6:A")=B6)=E6),E6<>"")

Formating Colors Text Color: None

Background Color: Light Red

=FILTER(range, condition1, [condition2], ...)

Example: =FILTER(A1:A100,B1:B100=C1)

The FILTER( , ) function needs to be used instead of the MATCH( , , ) function. FILTER( , ) references the cell(s) from the range whos row(s) matches the condition(s).

=INDIRECT(cell_reference_as_string, [is_A1_notation])

Example: =INDIRECT("Sheet2!A1:B")

The INDIRECT( ) function has to be used because the reference sheet can not be referenced directly.

=INDIRECT("'6 Star Gear Sets (Hidden)'!A6:A")=B6

=B6 has to be added to condition1 so that only the value in the range are referenced whos row matches the condition1. This finds which cell in range A6 thru A in the reference sheet matches B6. Only 1 cell is returned because A6 thru A in the reference sheet are unique.

=INDIRECT("'6 Star Gear Sets (Hidden)'!A$6:A")=B6

$ needs to be added in front of the row in both the range and condition in order for them NOT to change as the Conditional Format Rule goes down each cell to apply the condition to. (Something I failed to do in the original formula.)

=FILTER(INDIRECT("'6 Star Gear Sets (Hidden)'!D$6:D"),INDIRECT("'6 Star Gear Sets (Hidden)'!A$6:A")=B6)

Add the FILTER( , ) function and the range. '6 Star Gear Sets (Hidden)'!D$6:D returns the contents of D6 thru D of the referenced sheet that is on the same row as 6 Star Gear Sets (Hidden)'!A$6:A found in condition1.

=FILTER(INDIRECT("'6 Star Gear Sets (Hidden)'!D$6:D"),INDIRECT("'6 Star Gear Sets (Hidden)'!A$6:A")=B6)=E6

=E6 has to be added to the end of FILTER( , ) so that the formula checks to see if E6 matches the resulting contents of the cell found from D6 thru D of the referenced sheet. Because it is a Conditional Formating Rule, the result of the FILTER is either True or False, thus applying the Format if it is True and NOT applying the Format if it is False.

=NOT(FILTER(INDIRECT("'6 Star Gear Sets (Hidden)'!D$6:D"),INDIRECT("'6 Star Gear Sets (Hidden)'!A$6:A")=B6)=E6)

=NOT is added because we want True to actually be False and False to actually be True. We are trying to Format the cells that are not True of the FILTER( , ) we are using.

=AND(NOT(FILTER(INDIRECT("'6 Star Gear Sets (Hidden)'!D$6:D"),INDIRECT("'6 Star Gear Sets (Hidden)'!A$6:A")=B6)=E6),E6<>"")

=AND( , ) and E6<>"" has to be added so that it does NOT format every cell in column E that is blank. =AND( , ) in this case states that not only does the FILTER( , ) must be true, but also E6<>"". E6<>"" just states that E6 is not BLANK.