Sheet to sheet copying

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
0
Boenerge (52)
11/30/2008 1:39:00 PM
excel 39879 articles. 2 followers. Follow

2 Replies
936 Views

Similar Articles

[PageSpeed] 21

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
--- 
"Boenerge" wrote:
> 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.
0
demechanik (4694)
11/30/2008 5:47:01 PM
Hi,

In 2007 the formula would be

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

Cheers,
Shane Devenshire

"Boenerge" wrote:

> 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
0
12/1/2008 12:31:41 AM
Reply:

Similar Artilces:

How can I share information between multiple sheets in Excel?
New question: I know now how to use "=sheet1!a1" to copy cells from sheet 1 to sheet 2,3 etc. Is there is a formula that will allow me to enter data into sheet 2, and have it copy automatically into sheet 1 and 3, or into sheet 3 going to sheet 1 and 2? So that I can enter the data into any sheet and have it transfer to all sheets, rather than always from sheet 1 to sheets 2 and 3. Nothing built into excel (except for the formula you showed). But I'd be very hesitant to separate my data. I'd do my best to keep all the data on one worksheet. You can use Data|Fi...

Disable Copy/Cut
Process: Creating forms in MS Excel for office us Problem: Cannot prevent user from cutting/pasting or copying/pasting unlocked cells and/or cell formats - even when Protection for Worksheet and Workbook is activated Example: Worksheet is protected and all but cells A1 and B1 are locked. A1 stores a dollar value and B1 stores text. It is critical that the data in these cells retain their format and value types. However, even though the worksheet and workbook protection is active, I can still drag the contents from cell A1 into B1 and as a result, mess things up Can someone explain to me a pr...

Call from a different sheet?
I'm wondering if it's possible in Excel to call data from a different sheet. For example if I changed A1 in sheet 2 it'd affect forumlas in sheet 1. -- HisHighness ------------------------------------------------------------------------ HisHighness's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=31454 View this thread: http://www.excelforum.com/showthread.php?threadid=511552 You can link to another worksheet with a formula like ='Sheet1'!A1 -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson...

Transfer the total of mutiple entries in a sheet to another sheet in the same file #2
How to transfer the total of multiple of multiple entries in a sheet to another shet in the same file. Please help me in this formula ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Software! http://www.ozgrid.com/Services/excel-software-categories.htm ** ...

How do I copy a Word text file into Excel in a way that each char.
How do I copy a Word text file into Excel in a way that each character ends up in a separate Excel cell? Paste each line into column A. Then put this in B1: =MID($A1,COLUMN()-1,1) drag to the right (as far as you need--but you've only got 256 total columns) and drag down the range Newtech programmer wrote: > > How do I copy a Word text file into Excel in a way that each character ends > up in a separate Excel cell? -- Dave Peterson Thank you. Very helpful. "Dave Peterson" wrote: > Paste each line into column A. > Then put this in B1: > =MID($A1,CO...

HOW DO I CHANGE THE ORDER OF THE SHEETS IN EXCEL WORKBOOK?
my sheets are not in the order I want them. How do I change the order. The data is already in the sheets. Click on the tab and drag it to where you want to. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------�------------------------------�---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------�------------------------------�---------------- "joanbaer" <joanbaer@discussions.microsoft.com> wrote in message news:1F45...

Max number sheets in a file?
How many sheets can an excel 2002 file have if each sheet has only abou a page of text? I'm just looking for a general idea, if it's lots an lots and I don't need to worry about it that's a good enough answer fo m -- Message posted from http://www.ExcelForum.com The number will be limited by the memory in your computer. Certainly hundreds of sheets is well within most machine's capacity. In article <bishopwill.154u1g@excelforum-nospam.com>, bishopwill <<bishopwill.154u1g@excelforum-nospam.com>> wrote: > How many sheets can an excel 2002 file hav...

How to copy the function SUM but offseted in 7 columns
I Have the folow situation : The cells content is: A1 = 50, B1 = 30, C1 = 40, D1 = 10, E1 = 20 and F1 = 10 Using the function SUM in the cell A4 = SUM(A1:C1) I have as a result 120 If I copy the cell A4 to B4 my SUM function will be B1 = SUM(B1:D1) and the result 80. I would like to know how can I copy the SUM function from Cell A4 to B4 but the SUM function should be SUM(D1:F1). And if a still copying to left side, I mean to columns C, D, E... It should set the SUM functional to jump in each 4 Columns. A4 = SUM(A1:C1), B4 should be sum D1:F1, C4 should be sum G1:I1 ... Regard...

Sheet to sheet copying
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 t...

list sheet comments
Hi, I have a sheet with a lot of comments. I would like to create a macro that lists all the comments of the sheet in column F. I=B4ve tried something similar like this: For Each cmt in ActiveSheet.Comments MsgBox cmt.Text Next cmt But I don=B4t know how to list each comment in each cell of column F instead of a msgbox. thanks Dim StartCell as Range dim Cmt as comment with activesheet set startcell = .range("F1") for each cmt in .comments startcell.value = cmt.txt 'come down to the next cell set startcell = startcell.offset(1,0) n...

copying/pasting
when i try to copy paste from one workbook to another, my formatting i not copied, only the data gets copied, how do i copy paste along wit the formattin -- Message posted from http://www.ExcelForum.com try edit>paste special -- Don Guillett SalesAid Software donaldb@281.com "praptisahni >" <<praptisahni.16tebk@excelforum-nospam.com> wrote in message news:praptisahni.16tebk@excelforum-nospam.com... > when i try to copy paste from one workbook to another, my formatting is > not copied, only the data gets copied, how do i copy paste along with > the formatt...

copying incoming email to several addresses?
In Exchange 5.5, SP4, I used Delivery Options tab to add in an 'alernate recipient' - and set the 'deliver messages to both recipient and alternate recipient' checkbox. The incoming email is an internet email address. I am the alternate recipient - but I didn't get a copy of the email. I'm accessing Exchange thru POP3 - not thru Outlook. This seems so simple, what am I doing wrong? -- Thanks in advance, Les Caudle ...

Check Register to Mortgage Sheet
Hi; I am using MS Money 2007. If I write a check in my register to my mortgage company, how do I get that transaction to update my mortgage account register? Do I have to type it in twice (Once to my check register and once to my mortgage register)? I would like to set some stuff up where items in my check register automatically cary to other account items. Thanks for any advice on this. Pat From the Check Register make it a transfer to your Mortgage Account. -- Regards Bob Peel, Microsoft MVP - Money For UK tips & fixes see http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mn...

Rule copies message
Several rules, in Outlook 2003, copy the message instead of moving it. For example, a simple rule states "Apply this rule after message arrives from Kristi and move it to the Kristi folder." The messages are copied to the Kristi folder, and a copy retained in my inbox. This happens for several rules. Why doesn't "move" mean just that to Outlook? Does it work when you disale all the other rules? When more than one rule applies they will all be executed. To prevent this add the action "stop processing rules" to the rule and sort your rules correctly. -...

Move rule makes copies
I have several rules set up for new mail. I move mail to a folder depending on content. Subsequent rules also act on the mail and create duplicates. Its not moved, it's copied. How do I "move"? rep <anonymous@discussions.microsoft.com> wrote: > I have several rules set up for new mail. I move mail to > a folder depending on content. Subsequent rules also act > on the mail and create duplicates. Its not moved, it's > copied. How do I "move"? First, if a rule is supposed to be the only one that act upon a message, you need to have "...

blank out rest of work sheet
I was wondering how to blank out the empy cells in my worksheet. For example: I have data in columns A-F down about 50 rows. I do not want to see any rows past that. When I am saving the data as a text file, I am getting extra rows in my file for some reason and I want to see if this would prevent this from happening. Thanks... --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.769 / Virus Database: 516 - Release Date: 9/24/2004 Hi Shawn, See the explanation and remedial notes on Debra Dalgleish's web site: ...

How to populate one sheet with data from another sheet
I have 2 sheets in the excel file. On the first sheet the first colum is a part num only. The second sheet has part num on the first column and descriptions on the second column. I need a macro or script that will scan the second sheet for part num that matches the first sheet part num and populate the description(at the second column) on the first excel sheet. -- KH One way .. Assuming the reference data is in Sheet2, cols A and B col A = part#, col B = description In Sheet1 ----------- With col A containing part# in A2 down, Put in B2: =IF(ISNA(MATCH(A2,Sheet2!A:A,0)),"&quo...

No Bound Sheets Found Error Dialog Box-Excel 2000
Hello all. I have a 8.5MB spreadsheet that is the summary of weekly timesheets that are Excel files. I have attempted various strategies to reduce the size of the files. This sheet is all references to other worksheets. Up until two days ago, when opening the file and it asks, this contains references to other files...do you want Excel to update the cell refences, y or N. I always hit okay and it does it's stuff. Now however, when the box comes up when opening the file, I hit okay and it returns a "No Bound Sheets" error dialog box. All I can do is hit okay and a...

Changing from sheet to sheet
Hi there! Is there a function that would enable me to navigate from sheet to sheet in an Excel document (instead of using the mouse and click on the sheet I want to work with)? Many thanks! Hi Ctrl PgUp and Ctrl PgDwn is one way. Insert > Hyperlink (and after that clicking it) is another. HTH. Best wishes Harald "Greywolf" <anonymous@discussions.microsoft.com> skrev i melding news:5bfc01c42d6b$c2a26630$a101280a@phx.gbl... > Hi there! > > Is there a function that would enable me to navigate from > sheet to sheet in an Excel document (instead of using the &...

Delete values in sheet 2 that arre found in sheet 1
Is there an easy way to delete values in sheet 2 that match the value from sheet 1. I have approximately 10000 rows in sheet 1 and some identical rows on sheet 2 that need to be eliminated? I not sure if this has to be done pragmatically or if there is some feature in excel 2007 that can handle this type of request. thanks Nick If it is the values in a single column on each sheet that make up the duplication, it's fairly easy. Lets say you want to compare the contents of column A on each sheet to find duplicates on Sheet2 (that are also on Sheet1). Your data starts o...

Function to Copy all sheets into one sheet
Is there a way to copy multiple sheets at once to create one worksheet, rather than one at a time? You could use a for/each loop for each ws in worksheets depends on what and where you want to copy next -- Don Guillett SalesAid Software donaldb@281.com "Des" <cusaty@express-scripts.com> wrote in message news:013801c3b43a$5cb403b0$a101280a@phx.gbl... > Is there a way to copy multiple sheets at once to create > one worksheet, rather than one at a time? ...

? copy groups in windows mail to send to someone else
I have several groups up to 150 emails included, in my windows mail that I need to copy and send to someone else. Is there anyway to copy each group and send without having to copy each email address separately? Create a new email and select the group to send it to (but do not send it). Click the + symbol beside the group in that email's To line to expand and display all of the members of the group. Highlight and copy all of the names. Then paste that into the body of a 2nd new email and send it to your friend for them to use to create their own group. Repeat this proced...

Copying Drown Dop Box Selections
I've been using drop down boxes (or list) boxes in several of my spreadsheets to help with data input. I need to copy data entered including the drop down box selection into another spreadsheet or into MS Word. I can't figure out how to copy the drop down selection. Thoughts? P.S. I don't program in visual basic. Thanks! -- scottrichards7 ------------------------------------------------------------------------ scottrichards7's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27421 View this thread: http://www.excelforum.com/showthread.php?thread...

Linking sheets
I am trying to hyperlink a cell in one single sheet spreadsheet to a specific sheet in another multisheet book. I don't need to get to any specific cell in the second spreadsheet, just the specific sheet. Thx, Chris In Excel 2003 or earlier. Insert>Hyperlink>Existing file or webpage. Browse to the target workbook then hit "Bookmark" and pick a sheet and a cell. Gord Dibben MS Excel MVP On Thu, 22 May 2008 19:14:38 -0700 (PDT), spacia <chris_butler@thegrid.net> wrote: >I am trying to hyperlink a cell in one single sheet spreadsheet to a >specific sheet in...

Cannot use copy record button on form with lookup box
Hi. I posted the following message on 12/01/09 but did not get any replies. I am unsure if it is an unanswerable question or not... Hi. I have two forms that I would like to add a copy record button to. But I get error messages [Run-time error 3414] when I try. It points to this line of code when I click debug: If Not rs.EOF Then Me.Bookmark = rs.Bookmark After troubleshooting for a bit, I realized that both forms have lookup boxes in the form header section. If I delete the lookup box from the form I can add a duplicate record button without a problem. Is there anyway t...