Complex issue in MS Excel

[geo40]

EXCEL Question:

Each day I run a report at the same time ( 10h00 ) of the amount of our outstanding invoices. This changes every day. The question is whether it is possible that I can save this data in another sheet in the same file ?

Sheet1 gives following data, and will be refreshed every day:

Day 1

Costplace Outstanding amount
101 10000
201 5000
301 15000
401 25000

In the beginning of day 2 I REFRESH my file and I get following data back:

Day 2

Costplace Outstanding amount
101 15000
201 2000
301 17000
401 21000

In the beginning of day 3 I REFRESH my file and I get following data back:

Day 3

Costplace Outstanding amount
101 14000
201 4000
301 21000
401 22000

This procedure will be repeated every new day at 10h00. At the end of the week, the following colums have to be seen in the other sheet in the same file:


Costplace Day1 Day2 Day3 Day4 Day5 Day6 Day7

101 10000 15000 14000
201 5000 2000 4000
301 15000 17000 21000
401 25000 21000 22000

Is this possible in MS EXCEL ? And if so how can I accomplish this ?

 

[xlbo]

I'd have thought you could just use the WEEKDAY function


Select Case Weekday(Date, vbmonday)
case 1
'Monday - use Monday Sheet
case 2
'Tuesday - use Tuesday sheet
etc etc
case 5
'Friday - use Friday sheet
case else
'Weekend
end select

Use that to determine which sheet to put data on and have a weekly template sheet that reads from the seperate weekly sheets

HTH
Rgds
~Geoff~