If you have an Microsoft Excel workbook with multiple worksheets that contain similar type data it may save you a lot of time to auto populate the other worksheets with the similar data. In my scenario I had a form used to obtain information about customers which needed to be in a certain format but then I had two other forms that had some of the same data that needed to be provided to other departments. So the initial process was to fill out each individual form by hand and send to the proper department. To save time I wanted to auto populate as many like fields as possible.

The resolution ended up being really simple. First I just added the other forms to the workbook of the primary form. This ensured that when I opened up the form to enter customer data that the other two forms were open with t. Once I had added the worksheets and named each one properly I started messing around with formulas to complete the task of copying one cell from the primary worksheet to cells on the second and third worksheets in this workbook.

In this example the worksheets will be named sheet1, sheet2, and sheet3 to make things as simple as possible. I knew that I would need to refernce the primary worksheet in a formula. Below is an example of how you would populate sheet2 cell A1 from sheet1 cell A1.

Formula to Auto Populate Cell On Another Worksheet with Worksheet from Same Workbook 
Formula:
=+’sheet1′!A1

So as you can see this is really easy. The cells don’t have to match in any way so you could obviously place the above formula in say cell D14 on sheet2 and it would display sheet1′s A1 data. You could also combine data from multiple cells sheet1 into one cell on sheet2 or sheet3 as shown below.

Combine Cells on Another WorkSheet in the Same Workbook
Formula: =’sheet1′!B5 & ‘sheet1′!B6

The above could be placed inside any cell on sheet1 or sheet2. One more thing to consider when combining cells into one cell is if you want a space between them or something else to seperate the content from the two original cells.

Combine Cells With a Space Between The Content on Another Worksheet in the Same Workbook
Formula:
=’sheet1′!B5 & ” ” & ‘sheet1′!B6

All of the above was figured out because of a project using MS Excel however I typically use GoogleDocs Spreadsheet when possible now. The exact formulas above will work the same with GooogleDocs. I would definitely recommend using Google’s Spreadsheet application over Excel because not only is it stored online for free but it also uses a lot less CPU power.


Kindle Edition: Check Amazon for Pricing Digital Only


List Price: $39.99 USD
New From: $7.99 USD In Stock
Used from: $1.23 USD In Stock

DeliciousStumbleUponDiggTwitterFacebookRedditLinkedInEmail
Tags: , , , , , , , , , ,
10 Responses to “Use Microsoft Excel to Populate Cells on a Second Worksheet in the Same Workbook”
  1. Jeff says:

    Can you do something like this with if then statements? My problem is I am trying to enter in information about harvesting food at a community garden. I want to enter into list on a master sheet, then have sheets for each individual bed. So for example I want a master sheet to enter
    DATE BED PLANT WEIGHT
    8/2 a Tomato 4lbs
    8/2 B Onion 2lb

    I then want a separate sheet for each bed and this info to auto populate in a list. Any help would be appreciated Thanks.

    [Reply]

  2. Robert says:

    how can I display cell comments from my master worksheet sheet1 onto other worksheets in the same workbook

    [Reply]

    alex Reply:

    Hello Robert,

    Right off I am not 100% sure about how to display the comments from the first worksheet onto other worksheets.

    If you ask here it will give an easier way for others to respond in a timely fashion. Engage is a portion of Question Defense that allows everyone to participate including asking questions and answering questions. Simply register (only email, nickname, and password required) and post the question by clicking “Ask or Contribute”. I will look for the resolution as well and post the answer there once I locate it for you.

    Thanks.
    alex

    [Reply]

  3. Sarah says:

    As a novice Excel user, I am so happy you posted this. All the other similar responses I read were so confusing. This is simple and works perfectly. Thank you!

    [Reply]

    alex Reply:

    Hello Sarah,

    No problem. Glad you found it helpful. Thanks for taking the time to leave feedback.

    Thanks.
    alex

    [Reply]

    Judy Reply:

    Alex, I totally agree with Sarah. I am not an Excel novice however, not an expert either. Many excel comments/answers are so complex and given from the point of view that everyone is an Excel expert which makes the comment/answer hard to understand. Again, THANK YOU.

    [Reply]

    alex Reply:

    Hello Judy,

    Thanks for the nice comments and for taking the time to post feedback.

    Thanks.
    alex

    [Reply]

  4. Judy says:

    I would like to know how to populate sheet2 with sheet1 (all cells) information based on the text value of say cell D(1 thru whatever). Can anyone help me with this? Thank you.

    [Reply]

    alex Reply:

    Hello Judy,

    Fill the data in sheet1 and then in the matching column and first row of the column you want to copy you would enter “=sheet1!A1″ (to copy data from sheet1 cell column A row 1 to sheet2 column A row 1) without the quotes. Once you have verified that the one cell copied the data correctly you can click the bottom corner of the cell and drag it down for as many rows as you want to populate in sheet2 with sheet1 data. This will add the following formulas all the way down the row… =sheet1!A2, =sheet1!A3, =sheet1!A4, =sheet1!A5, etc.

    Hope that helps.

    Thanks.
    alex

    [Reply]

  5. Collete says:

    I’m trying to populate from one worksheet to another. The way I’m wanting the data to populate to the other worksheet is as follows
    Worksheet 1 is populated with an “X”
    Worksheet 2 needs to be populated with a “1″ if there is an “X” in the cell on worksheet 1.

    Basically Worksheet 1 and 2 are the same but, worksheet 1 is just keeping track of what has been done where as Worksheet 2 is a percentage worksheet.
    Can anyone help me?

    [Reply]

  6.  
Leave a Reply

*Type the letter/number combination in the abvoe field before clicking submit.

*