Search a Text item in a range

[Pauljj]

In a range AD12:AJ56 there maybe a cell reference that I have formulated to appear. How can I search that entire range so that AD10 shows the value

So for example in AF20 there maybe a cell reference saying $C$21, if it does, then I want $C$21 to appear on AD10

Or similarly I may have $D$19 appear in AJ20, therefore I would want $D$19 to appear in AD10....hope that makes sense.

I presume I will need to be using an array formula but I can't seem to think what formula would apply as the value will not always be the same


Thanks in advance

 

[Pauljj]

I must be asking lots of questions that no one knows the answer to, not having much luck on any

 

[Pauljj]

Please please help, Im desperate

 

[Gerald Higgins]

Is the cell reference entered as text, or is it part of some formula that's in the cell ?

 

[Pauljj]

The reference appears as text, from a formula which is


=IF(AND(N21<>"",M21=""),ADDRESS(ROW(),COLUMN(C12)),"")

 

[Gerald Higgins]

So in that example, what would you want the result to be ?

 

[Pauljj]

I guess I should give some more indepth information as to the result

The user has 7 columns to which he or she has to fill in data. So for example in C21 the user has to indicate a job for the user . In M21 will be the automated details of the job and N21 has the time of the job
The formula I gave you is located in AD21 and its returning the reference C21.

This is because the user has not put in a job for the employee in C21, ie there is a time for the employee to be in (N21) but no details for the job (M21)

Ultimately what I am trying to achieve is to indicate to the user that a particular cell has not been filled in. I will have a table of potential cells with missing data. These are all in AD12:AJ56 What code I am looking for and this is hopefully where you come in, is to say ok there is a reference of C21 appearing in that table of AD12 to AJ56, I want that reference C21 to appear in AD10...is that as clear as mud ?

 

[Pauljj]

This might be better to explain...or at least to see

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		$C$21
22
23
24
25
Tailormade

The result in AD9.....I apologise as i said AD10 in previous posts

This should say C21 as that is the only text that appears in the table

 

[Pauljj]

Any thoughts anyone or should i give up on this ?