|I'm setting up a spread sheet to keep track of fire calls that volunteer firefighters respond to. We require that they make a certain % of calls per quarter to stay on the fire department.|
Data is recorded on a spreadsheet where the first two column headings are "line number" and "Firefighter name" and then there is a long list of fire incident numbers which is basically a unique number after the 4 digit year (2010001, 2010002, and so on). I put a 1 if the firefighter responded to that call and a 0 if they did not. Sounds pretty simple so far doesn't it?
The problem comes in when I need to separate this long line of fire calls up into 4, 3 month segments and add the calls up for each quarter/firefighter. I could have 20 fire calls in 3 months but I might also have as many as 50 so the start and end points for each quarter will be different from year to year and I don't want to re-do all my formulas every year.
I've setup another tab/sheet called "Start Here" which whoever inputs the data will be asked to provide the last incident number for each quarter. I had planned on using this to compensate for the variable start and end locations for the SUM formula for each quarter.
So what I've tried so far (I'll brake up the formula cause it's kind of long):
Formula 1: =ADDRESS(ROW('Calls and Trainings'!C6),MATCH('Start Here'!B16+1,'Calls and Trainings'!A5:FF5,0),4)
Formula 2: =ADDRESS(ROW('Calls and Trainings'!C6),MATCH('Start Here'!B21,'Calls and Trainings'!A5:FF5,0),4)
Both of these formulas do the same thing. They both search the column headings for the last incident number of the quarter provided by the user in the Start Here Tab. Once they find it, they each give one of the cell addresses I need for the SUM formula. The only diff between the two is the first uses the previous quarters last incident number and adds one to get the first incident in the this quarter. Both of the above formulas work by themselves but when I go to put them in a SUM formula like so . . .
Complete Formula: =SUM(ADDRESS(ROW('Calls and Trainings'!C6),MATCH('Start Here'!B16+1,'Calls and Trainings'!A6:FF6,0),4),ADDRESS(ROW('Calls and Trainings'!C6),MATCH('Start Here'!B21,'Calls and Trainings'!A6:FF6,0),4))
When I put this formula in it gives me this error: "A value used in the formula is of the wrong data type."
What I'm thinking is going on is that Formulas 1 and 2 above are giving outputs in a way that the SUM formula can't process them as cell addresses/locations. I don't know how to fix this nor am I sure if I can fix it.
So my question is, can I fix the above formula so that it works or do I need to find a different way of doing it?
Thanks for the help.