Formula not working

[Guest]

1. I would like to find a matching number in column C. I want the function
to return what row the number was found in.

2. I would also like to find a matching number in row 3. I want the
function to return what column the number was found in.

**Column C and Row 3 share no duplicate numbers

3. I then want to place a number in the intersecting row and column found
from
#1 and #2 above.

4. I need to do the following with formulas not macros or vba.

5. This would be triggered whenever a number was added to A1.

For Example:

Let's say I want excel to search for the number 11 in row 3. It should
return the column it was found in which is G.

Let' say I want excel to search column C for the number 9. It should return
the row it was found in which is 7.

Then I want excel to enter another number let's call it (N) in the column
and row it found earlier which is G7.


A B C D E F G H
1 N Row
2 Column
3 4 32 34 42 11 15
4 8 ? ? ? ? ?
5 1 ? ? ? ? ?
6 2 ? ? ? ? ?
7 9 ? ? ? (N) ?
8 3 ? ? ? ? ?

Toppers provided these directions, but I can't seem to get them to work?
How do I paste them into the worksheet? Maybe that is what I am doing wrong?

In A1 put your value of N

in H1 put adress where you want to place N in absolute format e.g. $D$6

In D4 ,where you had the question mark(?) put

=IF(CELL("Address",D4)=$H$1,$A$1,"") (s per Harlan's reply).

Copy this formula across and down so D4 will change to E4 etc

Thanks much.

 

[Guest]

It works perfectly fine for me the way I think you were trying to explain it.
Because the formula Cell("Address",D4) will give you $D$4. So if H1 is $D$4
then it will match and show $A$1 in that cell. Are you sure you are entering
both dollar signs in H1? This is a case where $ signs are very important, but
if you copy the formula that was given to you exactly in to D4 and copy it
accross and down, it should work.

 

[T. Valko]

It works for me.

in H1 put adress where you want to place N in
absolute format e.g. $D$6

I assume H1 contains an ADDRESS formula?

Here's an alternative that includes error traps and eliminates the separate
ADDRESS formula:

A1 = some number
A3 = header = Row
B3 = row number to match
A4 = header = Column
B4 = column number to match

Enter this formula in D4 and copy across to H4 then down to row 8:

=IF(OR($A$1="",COUNTIF($C$3:$C$8,$B$3)+COUNTIF($C$3:$H$3,$B$4)<2),"",IF(CELL("Address",D4)=ADDRESS(MATCH($B$3,$C$3:$C$8,0)+2,MATCH($B$4,$C$3:$H$3,0)+2),$A$1,""))

Biff