Excel 2007 : Automatically updating weekly Sales Data

[Mertyn]

Hi,

I've got a bit of a monster problem that probably isnt too tricky...

I currently track about 70 products (each individual spreadsheets) and manually key in about 4,000 cells of data across them all in the form of sales for that week and current stock. This has been time consuming but useful as it allows me to keep an eye on exactly how the products are performing in different retailers and keep an eye on stock levels. Unfortunately things have now got too busy and I need to automate this.

What I have come up with so far is that I get the weeks data in one spreadsheet, Customers in the column and product sku's in the rows. I have found that I can use the following formula to automatically fill in the data in each individual products spreadsheet:

=INDEX('WeeklyStockandSalesSummary(2).xls'!$C$3:$BC$1012, MATCH($F$1,'WeeklyStockandSalesSummary(2).xls'!$B$3:$B$360,), MATCH(A21,'WeeklyStockandSalesSummary(2).xls'!$C$3:$BC$3,))

This works great but there are two problems...the main problem is i'm trying to find out a way for excel to know what week it is so as to only take the appropriate sku codes. Ideally i am planning on creating a spreadsheet and pasting the weekly sales data on to the bottom of it each week and having a unique week code such as "482009" then the following weeks pasting the data below and having "492009" next to them.

So what I am asking for is a formuala which will basically work alongside the lookup above but know what week it is and count how many rows have the number 482009 alongside it and only use those rows (sku codes) for that week so it would automatically know which rows to use (highlighted in red above) then the next week automatically know that it should only be looking for rows that have 492009 next to them - i'm assuming it must be some kind of count function but I can't figure it out

Hopefully this makes sense and someone can help me out!

Cheers!

[davesexcel]

Hi Mertyn,
Welcome to the forum,
Maybe try to supply a sample workbook, showing your original data and what the final result should be, you will probably get some answers then.

[Mertyn]

Hi Dave,

Thanks for the reply, i've uploaded a stripped down workbook to remove sensitive information but the bare bones is there.

I am currently in Wk 8 of the sheet, Wk 0-7 (highlighted in blue) have been manually updated as i usually would, week 8 has been updated by me linking to the sheet StockandSalesSummary (in the real world this would be an completely different workbook that all the sales sheets link to so I only have to maintain one) using the lookup function.

What i am basically looking for is a way for the sheet to know which week it is in using the week code i.e 482009 and then using that to find rows for the MATCH function that it needs in the StockandSalesSummary sheet - the number of rows will change each week.

I've added lots of comments across the sheet so please everyone have a look and get back to me

Hmm ok it wont let me upload the spreadsheet at the moment, saying its an invalid link... i'll try again later

[davesexcel]

Hi Dave,

Thanks for the reply, i've uploaded a stripped down workbook to remove sensitive information but the bare bones is there.

I don't see it.

[Mertyn]

Hi Dave,

As the the last line of my previous post it won't let me upload the spreadsheet, saying there is a database error... i've just tried again and it still won't let me upload the spreadsheet - any idea why?

Its a standard excel file, 870kb so within size limit... doesnt seem to make sense!