Hi All,
This one has me going around in circles; and please be aware we only have Windows 2013 to operate with.
Worksheet 2 (WK SQN Calc) draws data from a weekly download into worksheet 1 (owwsvr).
Best place to start is column AS in the former; that has all the elements I need to identify
The current extraction draws out the School and number of trainees on the course (each row is a different course from the owwsvr).
What I need to do
1. Identify the School,
2. Identify the number of courses (in the column) conducted by that school, and
3. Identify the total number of trainees on all courses at that school (in the column).
Each column relates to a week in a year
Hoping someone has the solution
Regards and thanks in advance
Paul
Hi Paul,
With Windows 2013 you probably mean Office (Excel) 2013.
The calculations you need only take place in row 3 to 8 in the 'Wk SQN Calc' sheet? Is that correct?
Did a quick check and modified your formulas so that when you copy them you do not need to check the references.
I think this will work
If you add a new item in the A column it will be included it it exists
Hope this is what you're looking for.
The text bos you can delete it was just to show what you wanted for my use
Hi, I don't know if you have looked at the file but I'm curious to know why you have a worsheet with 314 columns
If I look ate the owssvr sheet the earliest start date is 14/03/2022 and the latest END DATE is 02/10/2025
Okay, there's 1278 days between these two dates but since you check it by week that results in max 182.5 days
START DAYE has 952 different dates which by week are only 136 daus so you only need 136 columns starting with B
You can make this dynamic and let it depend on the owssvr data, all with just formulas and no macro's
Hi Hans,
Yes I've solved it an I know there is data missing, I was too big to upload, I am trying to set them up with a systerm that will run through till at least the end of 2027; or until they upgrade thier systems. Reality is I will likley be retired before before they would need to updat the process lol.
As always, thanks for your invalaubale asisstance
Paul
You're welcome, hope you make it to see the final result 😉
