Help with an simple excel lookup by date program!

[agp4]

Could somebody help point me in the right direction for the following;

I am creating an excel application (using vba if necessary) whereby I have a table of data with a date field, customer_name and quantity field on worksheet1. On a seperate worksheet (worksheet2) I have created a userform that allows the user to add a record which is appended to the table on weeksheet1. Now on a third worksheet (worksheet3) i wish to create a table that allows the user to select a date from a drop down list of all weeks of the year. This then selects all records in worksheet1 whereby the date field falls within the selected week from the drop down list. This should then automatically populate a cell in worksheet3 with the total quanity for that week! I am not expecting anyone to provide a full solution but any help/directions would be very much appreciated as although I am quite happy programming I am still fairly new to excel/vba! Many thanks.

 

[SkipVought]

Hi,

I'd probably so with MS Query via Data/Get External Data/New Dagtabase Query - Excel Files - YOUR WORKBOOK - YOUR SHEET contining the table with DATES......

Make it a Parameter Query -- in the Criteria for the DATE field, use [enter the date] instead of a date value. You'll actually need 2 date criteria...
[tt]
[MyDate]>=DateValue(year([enter the date]) & "/" & month([enter the date]) & "/" & day([enter the date])) And [MyDate]<DateValue(year([enter the date]) & "/" & month([enter the date])+1 & "/" & day([enter the date]))
[/tt]
or...
you could macro record editing your query and do this all in VBA.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[agp4]

Sorry for the delay in my getting back to you, but thank you for your response. I think you are right suggesting that I use a MS Query. The application will only need to hold around 600 non relational records - would I be best storing these in access or an excel worksheet?

My next problem is that I want the query to select and then display the results on my worksheet. However, I also want to be able to edit these results on the worksheet with the changes being automatically made to the underlying data in my database. I have read that SQL update and delete queries are difficult if the data is stored in an excel worksheet. Is this correct and can you recommend the best approach to querying a database so that I can view and then edit the data on display? Many thanks again.

 

[SkipVought]

You can make changes to the data and then run an UPDATE query in a loop, reading each row of data on the sheet and doing the UPDATE with the corresponding criteria.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}