HOW DO U MAKE 2 dates STick together to make a WEEK LIKE 21/

[Sami]

i dont no how u make them stick together n excel recognises them as a week? how do u make weeks? n how do i group the sales data into weeks and put the weekly sales revenue in a workbook?

 

[Sami]

MUDFACE u said: You can group your data by weeks. Create your pivot table, stick the Date as a row or column field with your sales as the data field. When you're done, right-click on the date heading in the pivot table, select Group and Outline- Group click on Days and set the number of days to 7 with the spin button at the bottom.

when i try and press group it says :
cannot group that selection???

 

[Mudface]

You must have a valid date in EVERY row of your source data that you have created the pivot table from. If you have a blank or anything that isn't a date, you won't be able to group, so I suggest you look through the Date column of your source data and make sure. Also, you will likely need to recreate your pivot table once you've ensured that all you have in that column are dates.

 

[Sami]

hey END OF TABLE is in there? should i get rid of that? also will this pivot table get updated automatically bcos i need it 2 :S???

 

[Mudface]

Yes, END OF TABLE is not a date, so get rid of it. Go into the Pivot Table options and click on Refresh on Open, to automatically refresh it when you open the file. I think in Excel 2000 you can also get it to refresh every so many minutes, too.

 

[Sami]

hey that kool i got it to work but... how do I get excel to make it in my accounts workbook if the data is in the sales workbook and how do you do that thing when it updates every day for example???

 

[Mudface]

Every time you open up the file with your pivot table, it'll link to your sales workbook and be updated (as long as you don't change the name or location of your sales workbook and you have it set to automatically update).

 

[Sami]

where do you put the code and how do you do automatically update??

 

[Sami]

hey ive made the pivot table so that it refreshes on open and this works but....

the week dates that it uses are only that of which it got from the "Database" when creating the pivot table. When other dates are entered the pivot table dose not update to accomodate these? plz help what should i do?

 

[Dave Hawley]

Hi Sammi

Use a dynamic range these are ideal for Pivot Tables.

http://www.ozgrid.com/Excel/DynamicRanges.htm

 

[Sami]

Ill think ill use this one

=OFFSET($A$1,0,0,WEEKNUM(TODAY()),1)

But where do i put it??? and im guessing i need a cell which has ()Today????