I'm trying to change the formula so that the name of the table changes depending on the row from the date column. Ex. on the formula where it says 20/5/2, is there anyway to make it soo that it automatically selects the date from the A column? this way the daily tallies would transfer their information to the main Season 2020 table.
thanks im new to numbers and struggling with sintax.
MacBook Pro 13”, macOS 10.15
From your screen shot, it appears the SUMIFS formula is returning a value from a single cell, G5, on the addressed table.
If that is indeed the case, there's no need to use SUMIFS, SUMIF, or SUM. A simple cell reference, built from the date in column A, the fixed name of the sheet, and the fixed location of the Total earned cell will do the job.
The Sheet 'shift 2' contains two tables in the example, more in your actual document.
The significant details of these tables are:
The summary table is on separate sheet. It is still named the defaut 'Table 1,' but the name is not significant as it is not part of any formula in this example. The Sheet name is also not significant to the example, but does let me find the example easily, should that be necessary for follow-up questions.
The formula below the table uses INDIRECT to construct the address of the cell whose content is to be retrieved, using a combination of fixed text and the contents of the cell on 'this row' of column A.
Column A's data format has been set to Text, so the data in those cells is NOT a set of Date and Time values, but a short text string, representing a date.
Filled down to the next row, the A5 cell reference changes to A6, the 'date' in A6 is placed in the Tablename part of the address string, and ndirect returns the value from G5 of the 20/5/3 table on the sheet 'shift 2'
Regards,
Barry
From your screen shot, it appears the SUMIFS formula is returning a value from a single cell, G5, on the addressed table.
If that is indeed the case, there's no need to use SUMIFS, SUMIF, or SUM. A simple cell reference, built from the date in column A, the fixed name of the sheet, and the fixed location of the Total earned cell will do the job.
The Sheet 'shift 2' contains two tables in the example, more in your actual document.
The significant details of these tables are:
The summary table is on separate sheet. It is still named the defaut 'Table 1,' but the name is not significant as it is not part of any formula in this example. The Sheet name is also not significant to the example, but does let me find the example easily, should that be necessary for follow-up questions.
The formula below the table uses INDIRECT to construct the address of the cell whose content is to be retrieved, using a combination of fixed text and the contents of the cell on 'this row' of column A.
Column A's data format has been set to Text, so the data in those cells is NOT a set of Date and Time values, but a short text string, representing a date.
Filled down to the next row, the A5 cell reference changes to A6, the 'date' in A6 is placed in the Tablename part of the address string, and ndirect returns the value from G5 of the 20/5/3 table on the sheet 'shift 2'
Regards,
Barry
how to change the table name in a sumifs formula to be variable.
