List Item by Date on seperate sheet

[ospaur]

OK so this may sound a bit complicated but maybe someone can help me:

I have a spreadsheet with a master set of data for all jobs worked on this year for my company. There are 2 date fields in this spreadsheet one for when the job was taken and one for when the job was installed. I have subcontractors that get paid a percentage at each date.

What I would like to do is on a second sheet create two inputs, 1 for the date of the start of a week and 1 for the date of the end of the week, when those fields are filled I would like the spreadsheet to list jobs that were taken and completed during those dates, as well as the total contract amount and percentages that the contractors are getting paid.

I have had limited success with playing with different functions such as index and match in array's

The data in my sample is fictitious, and there are a few other fields and many other formulas within it, but this information given is a sample of the kind of data I have...

What I want is a seperate sheet for each sales rep (contractor) where I put in date range it shows data like on the second sheet

Can Anyone help please!

[StephenR]

You've posted this in the programming forum so presumably a VBA solution is acceptable to you? The list of reps presumably varies over time?

[ospaur]

VBA is fine, really there are only 2-4 reps at any given time and they are constant enough, I can always add sheets for new reps. The main thing is getting that unique list to fall in the correct dates based on the dates input for both taken jobs and installed jobs

[StephenR]

Try this, and see attachment - press the button. Similar button could be added to other sheets and this macro assigned to each. There is something odd about the date formatting - I had to swap the day and month on the master sheet to get it to work but not sure why. I never like dates in Excel.

Please Login or Register  to view this content.

[ospaur]

Thanks I'll see if this works for me, let you know soon.

[ospaur]

Alright,

I was able to format the cells to a specific date format, then use the proper format on the other page and everything seems to work, i can see in the coding what to change/add if I want it to pull more information.

thanks again for all your help

[ospaur]

Ok, finaly got to implimentation, and as you can imagine i have run into issues. One thing, first time i ran the script in my own spreadsheet, it told me that it could not finish as there was too much data for excel to search, that i needed to close some programs to free memory, not a big deal (as you can imagine my actual spreadsheet has much more data for the year in it), closed some programs didnt get that error, however it isnt returning anything now, but it is running the script correctly. Like i said in previous post i managed to get proper date formating by formating cells with a date format, and the sample spreadsheet worked well, cant seem to figure out why it wont work in the much larger spreadsheet.

Any Help?