Lookup text

[Pauljj]

Could someone tell me how I look up the value in AG46 and return the result in AD9. The text could appear anywhere in the range AD12 to AJ56

Tailormade Day Plan 3.xls
	AC	AD	AE	AF	AG	AH	AI	AJ	AK
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46					R$F$46
47
Tailormade

 

[Domenic]

Assuming that there will always be only one value within AD12:AJ56, try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER...

=INDEX(AD12:AJ56,SMALL(IF(AD12:AJ56<>"",ROW(AD12:AJ56)-ROW(AD12)+1),1),SMALL(IF(AD12:AJ56<>"",COLUMN(AD12:AJ56)-COLUMN(AD12)+1),1))

 

[Pauljj]

Beautiful, thanks so much

 

[Domenic]

You're very welcome! Thanks for your feedback!

 

[Pauljj]

Domenic, could I ask, what the situation would be if there were multiple cells with references in the range AD12 to AJ56, it currently returns an error. Would this make it much more complicated to sort ?

 

[Domenic]

If there were more than one cell containing a value, which value would you like the formula to return? An example with some sample data, along with your expected result, would help.

 

[Pauljj]

Thank you for getting back to me Domenic

This range where text appears is actually references which the user has not updated properly, so I have a msgbox saying that a particular reference has not been updated, this being the only way I can think of doing it

As you can see, if there are multiple cells with references showing then AD9 returns an error. I guess, I would need to show one at a time, so if the user updates the macro they will see one cell needing correcting, then if, they try again, they will see another and so forth.

Not the most practical but with my basic VB stills it will suffice

Thanks once again

Paul

Tailormade Day Plan 3.xls
	AD	AE	AF	AG	AH	AI	AJ
9		#VALUE!	#VALUE!	#VALUE!
10
11
12
13
14
15
16
17
18
19
20				$F$20
21
22
23
24
25
26
27							$I$27
28
29
30
31			$E$31
32
33
34						$H$34
35				$F$35	$G$35
36
37
38
Tailormade

 

[Domenic]

First, if there's more than one cell containing a value, the formula should not return an error. Secondly, are you now looking for a VBA solution? If so, can you describe step-by-step what needs to take place?

 

[Pauljj]

To give you some better detail, this is a step by step approach

B12:B56 Houses the name of every employee in the department
C12:C56 Has the times these individuals work on a Sunday (they may not all be in, if they're not it remains blank)
D12:D56 Has the times the individuals work on a Monday this repeats up to I12:I56 for the remaining week

The manager will select a colour, the colour determines what task they are meant to do that day, he/she will do it for the entire week. This should mean every cell, where an employee is in (ie there is text there C12:I56) should be shaded in

When the manager has finished, they click on a button which sends an email to notify HR.

What I want is when they click on that button, it does a check to make sure that all the cells that have times in for that week have been formatted .

If a cell has a time but has not been formatted then the message should says consultant XX has not been issued a task on ****day (that being the day of the week)

The days of the week are in C11:I11


If a cell has been formatted but there is no time then the message should say consultant XX has been issued with a task on ***day but is not in

The routine should then exit, if everything is ok then it should continue on and email to HR, this code is already in place

It is worth pointing out the following

L12:L56 has a duplication of the names

M12:M56 has the description of task for the Sunday for the employee, this updates automatically based on the colour selected in C12:C56
N12:N56 has the time the employee is in for the Sunday

This replicates across the columns for each day up to Z12:Z56

So as a further example

I select C42 as red, this will then populate M42 with the task and N42 has the time the employee is in for. The employees name appears in B42 and L42


Hope that explains everything????


Thank you