1

This is for an online game and I am trying to build a database of what I have in the game. I have modified my data to protect my privacy a bit and bit in some better example data.

I essentially have a training courses log sheet, and a building database sheet with different fire stations. Each station has a set of staff working at them. Each one also requires a certain number of staff to operate the vehicles at that station. I also then can have people in training courses.

In the image below I have combined the two sheets onto one sheet just to make it easier to illustrate. To note, the end date would be used to exclude any completed courses from being used in the "In-Training" count.

enter image description here

And here is an annotated mock-up of what the results of the example above should automatically be turned into.

enter image description here

4
  • Sorry but I am confused by your images. Is Training Sheet column D (Station) supposed to hold Stations Sheet column A (Name) and Training Sheet column C (Num of Staff) supposed to hold Stations Sheet column B (In-Training). Also, what kind of data is in Training Sheet column A (Name)? People like John, Ann? So, Station=Name and Num of Staff=In-Training? Or...? Could you match your headers accordingly and give more realistic data? – marikamitsos Nov 3 '20 at 9:04
  • @marikamitsos Yes it sounds like you have it right. Sorry my naming for this example was a bit confusing. But essentially the training log is just mentioning which station (Stations Sheet) is having what training, for how long, and how many people will be present. No names or anything are listed, the training log is just the number of people that will be present in that training. So then when I am on the stations sheet I would like to be able to SUM all of the currently active training associated with that station. I will try and adjust my images – ComputerLocus Nov 3 '20 at 18:35
  • @marikamitsos I have updated the OP and completely re-wrote it and gave new examples with annotations. Hopefully that makes it more clear. – ComputerLocus Nov 3 '20 at 18:47
  • Thank you for the updated info. Please check my answer. – marikamitsos Nov 4 '20 at 4:02
1

You mention

...on the stations sheet I would like to be able to SUM all of the currently active training associated with that station.

...the end date would be used to exclude any completed courses from being used in the "In-Training" count.

You do not clarify though -or I least it is unclear to me- whether you will have 1 or more than 1 active training courses in each station.
To cover both possibilities

Case 1
If there is just 1 active training course in each station.

=ArrayFormula(IFERROR(VLOOKUP(A2:A11,IF(B14:D>NOW(),{D14:D,B14:C},""),3,0),""))

The VLOOKUP function will only pick the first from top active course.

enter image description here

Case 2
If there is 1 or more active training course in each station.

=ArrayFormula(IFERROR(VLOOKUP(A2:A11, 
                         QUERY(B14:D,"select D, sum(C)where B>now() group by D label sum(C) '' ",0) 
                                            ,2,0),""))

What we actually did here is replace the range {D14:D,B14:C} with the query formula. The QUERY function will pick all active courses.

enter image description here


(Please adjust ranges to your needs)

Functions used:

1
  • Yeah sorry I wasn't clear, case 2 was the version I needed to use as there could be multiple training courses. I appreciate the effort! I knew this would be a bit complicated to do but didn't realize it would need so many different functions! I'll have to take a look at the functions you used like Query and ArrayFormula as I have not used those before. I got it working nicely with the training data on the separate sheet and it is all good. Again, thank you! – ComputerLocus Nov 4 '20 at 21:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.