VLookups and Index/Match functions in VBA

[DougieFitness80]

Hi all,

I have 3 workbooks which plot resources and assets:
Main repository of data (called the IR System)
Planner sheet (in a yearly planner type format)
Schedule sheet

I currently draw information from the IR System using Vlookups to separate sheets within the Planner workbook from 5 columns to 5 separate sheets. These 5 sheets then get updated onto a master sheet in the Planner workbook. That information is then drawn to the Schedule sheet.

I have attached links to all 3 files. When you open the files, you will see that the Planner sheet (I suspect because of the frequent and plentiful use of Array formulae!) is phenomenally slow, and I read somewhere that, if you used VBA instead of worksheet Arrays, it would speed up the process significantly.

I need to remove the Tutor Planner workbook from the equation altogether, but if I keep to the same method, one of the remaining workbooks will suffer the same fate as the Tutor Planner workbook, therefore if anyone can suggest a more appropriate way of getting the data to the Schedule workbook, or use of VBA, I would be forever in your debt!

Thanks all

Dougie

IR System Link
http://www.adrive.com/public/2d6cb14217df1c61632a4bfca26008427c5db4fb41ffdec72738fecabc3e7ce4.html

Planner Sheet link
http://www.adrive.com/public/c09819b86125149a6b5db6273e14abaf9001bea541ac840b66f8c1b4da201b1d.html

Schedule Sheet link
http://www.adrive.com/public/4bc797dc2a48103fa07889857dae786fa1c2850ff11712d178ccb3f0e68a4ac8.html

 

[Jerry Sullivan]

Hi Dougie,

You're right -those bloated workbooks need to get in shape and shed some excess weight!

I'd suggest taking these steps as a start:

1. Remove all the data from your workbooks that isn't integral to the core scheduling function. Put anything that isn't needed by the scheduler in a separate workbook including all the cost stuff that is piggy-backing on your scheduler. You can add it back if you want once you speed up your scheduling system.

2. Eliminate sheets no longer being used and sheets that are just being used to transfer data.

3. Repost your schedule in a single workbook that has one sheet for the input (similar to your Pivot Table Data sheet), one sheet for your desired Output (similar to your Tutor Planner Sheet). You'll also need a sheet for any lookup data needed by the scheduler (like Course Duration Table).

If you will post that, I'd be glad to help with a VBA macro that updates the schedule and eliminates the helper sheets you are using to transfer the data to your output format.

 

[DougieFitness80]

Hi Jerry!

Thanks for the help with this....after all that hard work we went through last time, I need to change it all!

Such is life eh

Anyway, I have wielded the trusty carving delete button and have this offering for starters:

http://www.adrive.com/public/6ffc2971f497005c7e5919e5135369ede3a0861d3880e2fd0aa34932b39d4723.html

I would say that I do need a Tutor List sheet, so I can just update one field, and this will then populate the Tutor Planner Column A fields and also provide the validation data for the drop down list in the Schedule Sheet.

This could then be used as validation in the resource columns in the Pivot Table Data sheet?

 

[Jerry Sullivan]

Dougie,

I had misunderstood which format you wanted for your final output. I assumed your desired result was Tutor Planner Sheet.

It appears you want the format that shows the entire year schedule for one Tutor at a time. Is that correct?

If so, we can set it up so that the macro is triggered when you change Tutors from the dropdown list.

What is your preferred way of showing a 3-day class on the Tutor Planner Sheet? Do you want the class name to appear in each of the 3 cells or just the first with Conditional Formatting for the 3 days as you were doing previously?