October 10, 2020
Hello Friends can I ask your advice please?
We run a remote Summer camp for disadvantage kids here in Mongolia where they stay for 5 – 7 days at a time.
Our beneficiaries require a financial cost report each month on the amount of kids staying in camp.
We use two excel spreadsheets.
1st is call SUMMARY. This goes to our beneficiaries each end of month.
2nd is called CRR. This is for us to allocate rooms to arrivals, and quickly check who is in camp and where.
The SUMMARY sheet logs kids arriving and departing in a particular month.
[Blank cell] = not in camp / [IN] = arrived today / [R] = resident in camp / [OUT] = left today. We input these cells daily.
On the CRR spreadsheet we manually input into column C (In Camp Now) the digit  if a child is present in camp. When the child leaves, we delete the digit 
What we would like to do is link each the data we place in the monthly Summary sheet, into the CRR. So, when a summary cell for a child for that month is either (in) or (R), it automatically adds  into CRR spreadsheet cell ‘D’ next to the child’s name.
When the summary cell is changed to (out) or is (blank) at any time in the month, it automatically deletes the digit  in CRR ‘D’ cells to blank.
I’m so sorry but we are volunteers and very few, and definitely not expert at excel. We usually have over 200 kids and therefore inputting this data into the CRR is very time consuming and takes us away from the children.
Thank you so much for your consideration if you are able to help.
December 20, 2019
I have done a 2 way lookup to return the current status of the child.
0 means the cell is blank, all the others are obvious.
So know you have a choice - you can add an if to the formula to say if it is in or R make it 1 or you can add a countifs to the sum in total residents now
Hope it helps