H
HENRIETA
Hi
I have a spreadsheet that gives details of our sales peoples activities on a
weekly basis. The table takes its data from the data sheet Pro-Active
Activities. In here are various combinations of week number, name, activity
type and date. Every week the spreadsheet is updated with the weeks figures.
As you can see I am week 40 so in the data sheet are all the figures from
week 1 to week 40. I am currently redesigning the spreadsheet to be more user
friendly and thus have instilled a cell with the week number in a drop down
list in the top left hand corner so that the user can flick between previous
weeks. All the data in the cells work well except for the ‘Year to Date
Performance’ column. As the screenshot shows this is calculated by a
concatenate linked to the data sheet ‘Pro-Active Activities’ which references
activity type (A4), Week Number (A1) and name (A10) and gives the amount in
the cell. However, this formula gives the results for just the one week
selected and not a sum of the weeks from the beginning of the year as needed.
Example: if I were to change the week to 'Week 2' the cell should show the
'Year to Date Performance' for week 2 which should be the sum of Week 1 and
Week 2 together and so on and so on.
Hope this is a good enough explanation for you to understand. Please let me
know what you think.
I have a spreadsheet that gives details of our sales peoples activities on a
weekly basis. The table takes its data from the data sheet Pro-Active
Activities. In here are various combinations of week number, name, activity
type and date. Every week the spreadsheet is updated with the weeks figures.
As you can see I am week 40 so in the data sheet are all the figures from
week 1 to week 40. I am currently redesigning the spreadsheet to be more user
friendly and thus have instilled a cell with the week number in a drop down
list in the top left hand corner so that the user can flick between previous
weeks. All the data in the cells work well except for the ‘Year to Date
Performance’ column. As the screenshot shows this is calculated by a
concatenate linked to the data sheet ‘Pro-Active Activities’ which references
activity type (A4), Week Number (A1) and name (A10) and gives the amount in
the cell. However, this formula gives the results for just the one week
selected and not a sum of the weeks from the beginning of the year as needed.
Example: if I were to change the week to 'Week 2' the cell should show the
'Year to Date Performance' for week 2 which should be the sum of Week 1 and
Week 2 together and so on and so on.
Hope this is a good enough explanation for you to understand. Please let me
know what you think.