|Hoping for some help- I currently use formulas that I paste into EXCEL worksheets to create a tally based on certain criteria. The formulas reference certain column ranges to find the data (e.g., =COUNTIFS(K$2:K$361,"*"&K365&"*",G$2:G$361,"FALSE")). (Cell K365 is the location of the name we are looking to match in K2:K361 to count the associated data in G2:G361.)|
These formulas work fine unless IT updates the software (several times lately) that generates the EXCEL worksheet- they insert new columns, throwing off the reference locations, and the formulas subsequently stop working (until I fix them with the new column letter).
Is there a way to make the formulas based on the column headers instead (which don't change), and not reference the cells below it? Maybe with a structured reference? I could change the data into a table for this, but I generally have an empty column (other than the header) and there is no space between the header and the first line of data (which I could easily insert if needed to make a table).
It would be awesome if the formula could be something like: =COUNTIFS(Table1[@ExtractionBatch],"*"&K365&"*",[@RepeatTest],"FALSE") (this formula does not work- would be great if it does). (ExtractionBatch is column K and RepeatTest is column G in the previous example).
Also, are there restrictions as to where in the worksheet these formulas could be pasted (e.g., below or to the right of a column they reference?
Thanks so much for any help- I have spent hours trying to figure out a way...