Formula not working
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(M ATCH($B$3,$C$3:$C$8,0)+2,MATCH($B$4,$C$3:$H$3,0)+2 ),$A$1,""))
Biff
"joesf16" wrote in message
...
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.
|