COUNTIFS using column headers not cell ranges

[TAB_BHD]

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

 

[Alex Blakenburg]

That sounds like quite high maintenance approach. Typically everyone uses the same uses the same template and imports their own data.
If you are continually having to push out changes to every users copy of the template, you would need a macro anyway.
Who is copying the formulas to the users spreadsheet, are they doing it or are you.
For 1 or 2 formulas I would use the edit box copy paste method, for your 4 your method is as good as any.
I tried the option of adding another column using =formulatext(cell_ref) and copying that as values to the target workbook but you then still need to remove the leading ' (single quote) from the copied formula.

Your source spreadsheet is presumably always going to have the same name, so for multiple formulas after you copy them in.

• Copy the file name part of the copied formula
  'EXCEL_FORMULA_TEST_031121-2-ED5-LP6-LP7-JE8-CV.xlsx'!
  (if you do it often you could store it somewhere)
  
  
• Hit Ctrl + H (replace)
• Paste in the path part
• Leave the "replace with" box as blank
• Hit the button Replace all
  
  
• View attachment 34688

A very quick macro that would do the replace above is below.
You would need to have it either in your common workbook or your personal macro workbook and set up a button on your Quick Access Toolbar (QAT). This is because we don't know the name of the target spreadsheet in advance and are relying on it being the active sheet.

If you put it in your Common Template, you could make it more flexible by getting the filename / strToReplace using ThisWorkbook.Name and adding the single quote at the start and the single quote + ! at the end.

Sub RemoveWorkBookRef()
'
    Dim strToReplace As String
   
    strToReplace = "'EXCEL_FORMULA_TEST_031121-2-ED5-LP6-LP7-JE8-CV.xlsx'!"

    ActiveSheet.Cells.Replace What:=strToReplace, _
        Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:= _
        False, SearchFormat:=False, ReplaceFormat:=False, FormulaVersion:= _
        xlReplaceFormula2

End Sub

 

[TAB_BHD]

The 'common' EXCEL sheet is a tracking log of all our data, who set it up, analyzed it, etc. The source spreadsheet (now) is not the "EXCEL_FORMULA_TEST..." sheet, but the common tracking log.

Users get the data they analyze from another piece of home-brewed software which generates an EXCEL file; everyone analyzes within this file and records the tallies back in the common EXCEL. I don't want the formulas to reference any file except the data file it is pasted into- currently the only way I found for that is to add the "=" after pasting the formulas, a little annoying (because I have to do it 4X) but easy enough. There are ~10 people who need to do this so I would prefer it as simple as possible.

I suppose I could do this the other way around entirely and make a whole template with the formulas already on it and add copy/paste the data into it (instead of adding the formulas). Not sure how much of a process change that would be (I am in a clinical diagnostic lab) and if it would need some approval.

 

[Alex Blakenburg]

For multiple people to open the source dat in Excel and then copy formulas into that spreadsheet is not the common approach.
I am surprised that this approach gives them all the "Table1" which the formula needs.

Its probably too hard to resolve the whole design issue here.

In terms of you copying the formulas you could use the macro. It the formula is now being copied from a different workbook just enter the new name in the line strToReplace =
You can assign it a shortcut key which would make easy to run against a different activeworkbook