VBA Vertical and horizontal Lookup ???

Hi all
I was wondering if it's possible to create a macro that would check the range horizontally and vertically and return the resoults in a userform so that when workbook is open this userform would come up with the information who is due for refresher....
The sample book doesn't show the real size of the original matrix which has 190 employess and about 30 procedures so as you can imagine it's a pain.. to keep it up to date so I need something to flag that for me, yes I can see the red cells but still there's so many cells that I might easly miss some of them....
I would like the result to bring the name of the person and procedure title.
Thank you in advance
Dan

Hi Dan,

there doesn't seem to be any data in the attachment???

Jmac

Originally Posted by jmac1947

Hi Dan,

there doesn't seem to be any data in the attachment???

Jmac

Hi Jmac
Seems like a bad upload...
Here's the new one :-)
Thank you:-)

Originally Posted by jmac1947

Hi Dan,

so how about something simple like a new tab called "action needed" that the macro populates, based on either today's date or some user input (ie you) response to a prompt??

that seems like a relatively simple solution and fairly easy to develop the code for (but I wouldnt want to do it if that was not going to solve your "I need reminding" question

Jmac

PS... can you explain the purpose of the cells/rows that seem to start from about row 238... which also says, what is the initial group meant to show, last training attended or next needed... there are a few assumptions that you know about the worksheet that are not so apparent to someone else

Hi This sounds grate never though about this... I am liking it a lot...
The reason for second range in my worksheet that starts from row 238 is that when I input the date for example in A1 it then takes that date adds 3 years and displays it in cell A238 THEN A238 is used for condition formatting so if A238 is larger than today it turns A1 background colour to red. does this makes sense? :-)

I would be very grateful if you could help me create this macro :-)
Thanks again
Dab

yep that makes sense now,

just makes the construction of a macro to process the "real" rows a little more tricky, but no big deal

(and sorry, not sure where I got "Dan" from, maybe another thread or just bad eyes )

I will take a look at a solution tomorrow, already 23:12 here and I need some sleep

Glad you like the "outside the square" suggestion, I am a big adherent to the KISS principle so careful and simple gets my vote most times

Jmac

Originally Posted by jmac1947

yep that makes sense now,

just makes the construction of a macro to process the "real" rows a little more tricky, but no big deal

(and sorry, not sure where I got "Dan" from, maybe another thread or just bad eyes )

I will take a look at a solution tomorrow, already 23:12 here and I need some sleep

Glad you like the "outside the square" suggestion, I am a big adherent to the KISS principle so careful and simple gets my vote most times

Jmac

Haha Sorry I have mistype my name it's Dan as per previous posts :-)
I am very grateful for your help I really mean it !
Thanks

Originally Posted by ciapul12

Haha Sorry I have mistype my name it's Dan as per previous posts :-)
I am very grateful for your help I really mean it !
Thanks

Hi
I haven't heard from you....
Did you managed to work it out???
Thanks
Dan

Sorry Dan, been busy, will be on the case later today, sorry for the delay

Jmac

Originally Posted by jmac1947

Hi Dan,

Firstly, please ignore the large slab of red on the first tab, I was experimenting with conditional formatting trying to get rid of the need for the helper rows in the bottom of the tab and only "nearly" got it working

Ok,
1. I have renamed your first tab to something that will be more meaningful when you look at the VBA code.
2. I added in a new tab to store calculation values that are critical to the VBA code. This is the area you would need to change if you added in more staff members for example of new courses.
3. Your helper cells seem to be set for a three year cycle, I just moved that variable (for the macro) into a variable you can alter (note however that it is currently a "global" value, that is every coyurse and every staff member is on this cycle of refresher training.
4. I have added in a variable to do the check against. At the moment it is populated with a formula of =now() but you could manually change this date if you wanted to.
5. I have taken the view that the data is mor important than the formatting so I have paid little due tot he formatting of headings etc in the new results tab (Training Overdue).

I haven't added in any image for starting the macro, you will need to do that manually or add in the image/button yourself.

I tested it sparingly, I am sure when you use your real data there may be some issues but this should get you 98% down the path

Let me know how your testing goes

Attachment 300782

Hi Jmac :-)
Firstly I want to thank you for your work!
Ok there is a lot of things happening on the sheet and not quiet what I was after....
I need to be able to set the refresher time for different columns as each column/course has different expiry period some of thenm are valid for 1,3,5 years and some of them never expires and I could do it with formulas by just changing values....
The other thing is that instead me seeing the copy of the matrix I would rather have a simple list like for example:

Person 1 due for refresher on Counter Balance
Person 16 due for refresher on Counter Balance
Person 16 Overdue on Ride on Truck
Person 2 due for refresher on HACCP
Person 2 Overdue Ride on Truck
Person 7 due for refresher on Food safety Level 2
Person 7 due for refresher on Ride on Truck

So that I can simply printout this list and it's lot easier to look through the list instead of the wide range....

Sorry for being a pain...


Thank you a loooot :-)
Dan

Originally Posted by jmac1947

Dan,

I am not going to complain (much) but you now are changing the requirements quite a bit, certainly more than I expected from 190+ posts.

So, as I see your last post, there are now two classes of "red cells", due and overdue... how do you expect the difference to be identified, just for one issue??

the variable refresher period was something I worried about, but all the data in the sample spreadsheet that I eyeballed had a constant "3" in the formula, hence my assumptions.

let me know how to tell due/overdue wording and I will spend a couple of hours changing the current code although I think you should have the experience to do it.

PS.. sorry if I sound a bit grumpy, it is nearly 22:00 on a Friday night here

Jmac

Apologize for this I didn't realized It is totally different request......
The wording I would like to see is as above just a simple due for refresher and overdue.....
Dan

Originally Posted by jmac1947

Hi Dan,
Firstly sorry I managed to overlook the finalisation of this issue, had it 80% coded and the weekend "blanked out my memory"

SO take a look at this version.

Notes:

The constants tab is now even more important as it has the variable refresher periods by training course. IN the event that there is no refresher due for a particular course I suggest you simply set the period in the Constants tab for that course to say 50 years *(that should see both of us well past the point of worrying about a spreadsheet

I have left all your conditional formatting as it was.

You didn't explain the difference between due & overdue so I am making no distinction in this version

Again, only minimal testing at my end so OTY

Attachment 301901

Jmac

Hi Jmac
This is exactly what I wanted you are awesome!!!! and very smart I must add :-)
I have looked at the workbook and noticed that person 1 has a red cell in matrix for counterbalance but in training overdue it says " was due for training in Ride on Truck"
How can I fix it?
Thank you Jmac

Dan,

I see your problem, is 22:00 here and I am "stuffed", will try to debug tomorrow

Jmac

Originally Posted by ciapul12

Hi Jmac
This is exactly what I wanted you are awesome!!!! and very smart I must add :-)
I have looked at the workbook and noticed that person 1 has a red cell in matrix for counterbalance but in training overdue it says " was due for training in Ride on Truck"
How can I fix it?
Thank you Jmac

Hi Jmac
In addition to my previous email I have also identify another issue, when for example Person1 is due for refresher on more than 1 training the macro would display only 1 record... Is this easy to fix???
Dan

Originally Posted by jmac1947

Attachment 302268

hello Dan,

try this solution

and yes, if you worked through the code with the "old F8" stepping (adding watch points on key variables) would have seen that I managed to get the "focus" wrong, ie didn't set the current worksheet focus correctly and didn't test all options"

my only excuse is the age old excuse.... "the business needs to test the solution with real data"

mea culpa

Hi
I cannot thak you enough for your help!
It works as I wanted.
Thank you again.
Dan

Hopefully this is something simple to fix !
Thank you.