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!