Sheet to sheet copying

[Boenerge]

Hi,
I have two spreadsheets. One called nursing and the other medical. I want
to be able to copy certain cells from one sheet to other and visa verca
without the need to copy & paste.
So I want a cell in nursing to search a column for specific text in the
medical sheet, then I want the contents of cells in the row where the text is
found to be copied to cells in the nursing sheet.

Example:
The cells B3-G3 in the nursing sheet to search Column A in the medical sheet
for specific text e.g. "G1". When "G1" is found in cell A5 then the contents
of cells B5-G5 in the medical sheet are copied to the cells B3-G3 in the
nursing sheet.
Hope this helps to make it clearer, if not let me know.
Thanks
Jason

 

[Max]

In nursing,

Assume lookup value is in A3
Put in B3:
=IF(ISNA(MATCH($A3,medical!$A:$A,0)),"",INDEX(medical!B:B,MATCH($A3,medical!$A:$A,0)))
Copy B3 across to G3


If it's a fuzzier search ..
Try in B3, normal ENTER:
=INDEX(medical!B2:B100,MATCH(TRUE,INDEX(ISNUMBER(SEARCH(TRIM($A3),TRIM(medical!$A2:$A100))),),0))
Copy B3 across to G3
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik

 

[Shane Devenshire]

Hi,

In 2007 the formula would be

=IFERROR(MATCH($A3,medical!$A$1:$A$100),"")

Cheers,
Shane Devenshire