Create persistent data field with if statement?

[Sam S]

I work in a school and am creating a template that allows teachers to track the behaviour of the kids over the days, weeks, terms.

On one worksheet they type in the week number, and then the daily totals, from 0-100.
The data is then posted to a second worksheet. Pictures, below:


Daily Tracking:
Daily track.png

Term Tracking:
Term Track.png


On the second worksheet, as shown below, I wrote an if statement

Please Login or Register  to view this content.

Which takes the value from the daily totals and posts it into the weekly totals, depending on the week number.

The problem is, when the second weeks data is entered, then the first week reverts back to '0'.

Is there any way for the data to persist after the function is changed?



PS. I left the 'value_if_false' area blank as an attempted fix, but it didn't work.

Also I felt like I didn't explain that very well, so I'll link the excel work book to play around with. Try adding data for week 1 in the Daily Behaviour Tracking worksheet, then set it to week 2 (F8) and week one will disappear in the Weekly Behaviour Tracking worksheet.

Dynamic Template Test.xlsx

Thanks,

any suggestions are more than welcome.

[bebo021999]

Is it possible to create each sheet for each week then auto-updated in sheet "Weekly Behaviour Tracking"?

i.e:sheet 1: week 1, sheet 2: week 2,....

I have made formula to draw week number from sheet name to draw value from each week to sheet "Weekly Behaviour Tracking"

Each week you have to do:

1) Right-click on the last week sheet ( instant "week 1"), Move Or Copy/ stick to "Creat a copy". Rename new WS to "Week 2". (In format

"Week" and space and number: Week 2, ..., Week 10... This fomat should be similar in cells (A5,A17,...) with Week number in sheet

"Weekly Behaviour Tracking")

2) Copy A5:B12 to A17:B24. Rename "Week 1" to "Week 2"

Same tasks for week 3,4,...

See attachment.

[Sam S]

Thanks for the suggestion, I was hoping to keep the amount of worksheets minimal, but it seems it may be too difficult to have anything else. Thanks also for taking the time to create the template!