Question about Microsoft Excel for PC

2 Answers

Unsure of correct formula

I have 4 worksheets with Names in column 1 and data in all the others. All 4 worksheets do not have all the same names. Just a few. I need one worksheet with certain individuals in column 1 and their corresponding data in the following columns but don't want to do this manually. I need a formula that will capture each person's data in each of the 4 worksheets and place it on their individual rows. So in the end I will have 5 worksheets with main worksheet that automatically updates as I update the other 4 worksheets.

Posted by on

  • cindy73 Mar 29, 2008

    I don't understand what you are saying. I need assistance in creating one formula (after that I should be able to figure out the others) that when I type a person's name in one cell, all their information from the other worksheets will populate in the correct cells in the first worksheet. Is the formula something like =REFERENCE(reference the name of the person, their cell location);(the worksheet name and the range of columns like A-R) and the rest I am lost.

×

2 Answers

  • Level 1:

    An expert who has achieved level 1.

    All-Star:

    An expert that got 10 achievements.

    MVP:

    An expert that got 5 achievements.

    Mentor:

    An expert who has written 3 tips or uploaded 2 video tips.

  • Contributor
  • 51 Answers

I would rename all the tabs to something unique and re-do the refs. You can also refer to the sheets as members of the workbook's collection of sheets but if someone re-arranged the sheets, it'll break the formulas. You can lock the workbook so that people can't rearrange the worksheets but that will probably mess up other stuff.

Posted on Mar 10, 2008

  • mcc99
    mcc99 Mar 30, 2008

    Hang in there, Cindy.

    It sounds like you will need some VBA code in your spreadsheet. You need to handle the user-generated event associated with the user hitting "Enter". Are you familiar with VBA in Excel?

    Basically, the code would just set the values of various cells in your worksheet after the Enter key was pressed while focus was on a certain cell in the worksheet. The event handler code would get the various values in other worksheets in that workbook and copy them into whatever cells you want them in. However writing this handler will require some knowledge of the concepts found in VBA. Are you familiar with those?

    Some places to star if not are:

    http://www.anthony-vba.kefra.com/vba/vba...
    http://www.techonthenet.com/excel/index....

×

  • Level 1:

    An expert who has achieved level 1.

    Hot-Shot:

    An expert who has answered 20 questions.

    Corporal:

    An expert that has over 10 points.

    Mayor:

    An expert whose answer got voted for 2 times.

  • Contributor
  • 48 Answers

You can add a reference from the worksheet 1 to all other worksheets

Is it OK?

Posted on Mar 09, 2008

1 Suggested Answer

6ya6ya
  • 2 Answers

SOURCE: I have freestanding Series 8 dishwasher. Lately during the filling cycle water hammer is occurring. How can this be resolved

Hi,
a 6ya expert can help you resolve that issue over the phone in a minute or two.
best thing about this new service is that you are never placed on hold and get to talk to real repairmen in the US.
the service is completely free and covers almost anything you can think of (from cars to computers, handyman, and even drones).
click here to download the app (for users in the US for now) and get all the help you need.
goodluck!

Posted on Jan 02, 2017

Add Your Answer

Uploading: 0%

my-video-file.mp4

Complete. Click "Add" to insert your video. Add

×

Loading...
Loading...

Related Questions:

1 Answer

Describe the each part of microsoft excel 2207


anmolsxn_0.gif
Parts of the Excel 2007 Screen

Active Cell In an Excel 2007 worksheet, the cell with the black outline. Data is always entered into the active cell.
Column Letter Columns run vertically on a worksheet and each one is identified by a letter in the column header.
Formula Bar Located above the worksheet, this area displays the contents of the active cell. It can also be used for entering or editing data and formulas.
Name Box Located next to the formula bar, the Name Box displays the cell reference or the name of the active cell.
Row Number Rows run horizontally in an Excel 2007 worksheet and are identified by a number in therow header.
Sheet Tab Switching between worksheets in an Excel 2007 file is done by clicking on the sheet tab at the bottom of the screen.
Quick Access Toolbar This customizable toolbar allows you to add frequently used commands. Click on the down arrow at the end of the toolbar to display the toolbar's options.
Office Button Clicking on the Office Button displays a drop down menu containing a number of options, such as open, save, and print. The options in the Office Button menu are very similar to those found under the File menu in previous versions of Excel.
Ribbon The Ribbon is the strip of buttons and icons located above the work area in Excel 2007. The Ribbon replaces the menus and toolbars found in earlier versions of Excel.
Here are the main parts of Microsoft Excel 2007. Thank you for using Fixya !!!

Nov 15, 2010 | Microsoft Windows XP Professional

1 Answer

My MS Excel vlookup function ASCII type table contains both upper case and lower case letters, characters like ( and % or *, and numbers. It seems to return the numerical equivalent of upper case letters...


Correct a #N/A error blueup_clv.gifShow All bluedrop_clv.gifHide All This error occurs when a value is not available to a function or formula.
  1. Optionally, click the cell that displays the error, click the button that appears ooui1_za06043871.gif, and then click Show Calculation Steps if it appears.
  2. Review the following possible causes and solutions. blueup_clv.gifMissing data, and #N/A or NA() has been entered in its place
    Replace #N/A with new data.
    Note You can enter #N/A in those cells where data is not yet available. Formulas that refer to those cells will then return #N/A instead of attempting to calculate a value.
    blueup_clv.gifGiving an inappropriate value for the lookup_value argument in the HLOOKUP, LOOKUP, MATCH, or VLOOKUP worksheet function
    Make sure that the lookup_value argument (argument: The values that a function uses to perform operations or calculations. The type of argument a function uses is specific to the function. Common arguments that are used within functions include numbers, text, cell references, and names.) is the correct type of value — for example, a value or a cell reference, but not a range reference. blueup_clv.gifUsing the VLOOKUP, HLOOKUP, or MATCH worksheet function to locate a value in an unsorted table
    By default, functions that look up information in tables must be sorted in ascending order. However, the VLOOKUP and HLOOKUP worksheet functions contain a range_lookup argument (argument: The values that a function uses to perform operations or calculations. The type of argument a function uses is specific to the function. Common arguments that are used within functions include numbers, text, cell references, and names.) that instructs the function to find an exact match even if the table is not sorted. To find an exact match, set the range_lookup argument to FALSE. The MATCH worksheet function contains a match_type argument that specifies the order the list must be sorted in to find a match. If the function cannot find a match, try changing the match_type argument. To find an exact match, set the match_type argument to 0.
    blueup_clv.gifUsing an argument in an array formula that is not the same number of rows or columns as the range that contains the array formula
    If the array formula (array formula: A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.) has been entered into multiple cells, make sure that the ranges referenced by the formula have the same number of rows and columns, or enter the array formula into fewer cells. For example, if the array formula has been entered into a range 15 rows high (C1:C15) and the formula refers to a range 10 rows high (A1:A10), the range C11:C15 will display #N/A. To correct this error, enter the formula into a smaller range (for example, C1:C10), or change the range to which the formula refers to the same number of rows (for example, A1:A15).
    blueup_clv.gifOmitting one or more required arguments from a built-in or custom worksheet function
    Enter all arguments (argument: The values that a function uses to perform operations or calculations. The type of argument a function uses is specific to the function. Common arguments that are used within functions include numbers, text, cell references, and names.) in the function.
    blueup_clv.gifUsing a custom worksheet function that is not available
    Make sure that the workbook that contains the worksheet function is open and the function is working properly.
    blueup_clv.gifRunning a macro that enters a function that returns #N/A
    Make sure that the arguments (argument: The values that a function uses to perform operations or calculations. The type of argument a function uses is specific to the function. Common arguments that are used within functions include numbers, text, cell references, and names.) in the function are correct and in the correct position.

Oct 31, 2008 | Computers & Internet

1 Answer

Hi, I'm workin in Excel 2003 and I have column A that it has names (they could be repetitif names). Column B that it has dates (it could be repetitif dates but not for the same names in column A) and in...


I'm certain that I can solve your problem...

Can you send me a xls file with you data to my e-mail (pfjota@gmail.com) so that I can try with real data ?

Send these explanation with your email to !

Thanks

May 01, 2009 | Computers & Internet

1 Answer

Input data


If you want to transfer your data into SAS, SPSS, or some other program, follow these guidelines:
The cells in Row 1 should contain the column's eventual data set name. Each name should be a relatively short and unique acronym that clearly identifies the data. It should begin with a letter and contain only letters, numbers, or an underscore ( _ ) where spaces would naturally fall. Avoid using special characters such as $, &, @, in variable names. Since each row represents the values from one subject, the first column(s) should contain one or more variables that give each subject a unique identifier. They become especially important if you need to merge two or more data files.
In Excel, data formats are defined for a range of cells rather than for a complete column. For this reason it is important that each entire column, including cells with missing or uncollected data, have one, and only one, format. Actually, you do not need to format the entire column, only the portion you will eventually use. Highlight that portion and select the appropriate format from the Format/Cells option. Do not select formats that will enter commas, dollar signs, or other visual enhancements. Numeric, text, and date formats (e.g. mm/dd/yy is often a good choice) are probably the only formats you'll ever need.
The "Split" option (under the "Window" pull-down menu) keeps the row of variable names and the columns of identifiers in view, whatever range of cells in the worksheet you may need to review. First place the cursor at the most extreme upper left-hand corner where data entry begins (e.g., the intersection of Row 2 and the column in the upper left-hand corner where data appear) and then select "Split" from this menu. For any row or column of the worksheet you move to, you'll know exactly which variables you are observing (column names) and their associated ID values (rows).
For versions of Excel later than 4.0, one file can contain multiple worksheets. By default, the tabs at the bottom of these sheets are supplied names ("sheet1," "sheet2," etc.). You can change these names by clicking this space with your mouse and entering a new name. Use the same conventions for first-row variable names: use a short acronym of the page contents that begins with a letter, use only letters or numbers, and enter the underscore ( _ ) where a space naturally falls.

Jan 05, 2009 | Sage Instant Accounts 8.0 (013604ug)

1 Answer

Count how many times a value appears in a column, based on anothe


Go to the cell you want this total in.
Type this formula:
=SUM(IF(Sheet2!C1:C10="EME",IF(Sheet2!N1:N10=1,1,0)))
make sure you end the formula with CTRL - SHIFT - ENTER which makes it an array formula. If you forget, go back to the cell with this formula and press F2 (to edit the cell) and press CTRL - SHIFT - ENTER to convert it to an array formula (Excel will show a little {...} around the formula).

Dec 21, 2008 | Microsoft Excel for PC

1 Answer

Populate a cell based on a specific criteria in Excel


[formula in the new Category Column]

=RIGHT(A1,LEN(A1)-SEARCH(" ",A1,LEN(A1)-11))

Where A1 is the cell that stores the Business names.

May 20, 2008 | Microsoft Excel for PC

1 Answer

Matching data


1. Open up both workbooks.
2. Copy one worksheet from one workbook to another using.
Right click on the tab of one book and Select "Move or Copy"
3. A dialog box will appear..Next to the "To book" select the workbook you want to move it to.
4. Be sure to select the 'create a copy' box in the bottom left of the dialog box.
5. Sort each worksheet by students name. Data..Sort.
6. Copy the scores from one sheet to another.
7. You then you can manipulate the scores and compare anyway you like by using formulas in different columns.

Apr 29, 2008 | Microsoft Excel for PC

1 Answer

Lookup,s


If you can move your name column (C) to the first column, you could leverage the VLOOKUP formula pretty easily.
To do this, do the following:
1) Move the C Column to be the A Column, shifting all other columns to the right.
2) (optional) Insert a new row at the top of the sheet (to hold the formula & seach value)
3) Use A1 as your search field.
4) In A2, enter the following formula:
=VLOOKUP($A$1,$A$2:$C$6,3,)

Describing above parameters, in the formula:
$A$1 -> the search field (name your looking for).
$A$2:$C$6 -> The table/grid you wish to search and return values from. The left most column (A) must contain the values to be searched.
3 -> is the column number (A=1,B=2,C=3, etc) within the table/grid to return.

If you cannot make the name column your first (A) column, there are more complex ways to do this. For instance, create a new sheet which redisplays the info in the structure easier for this method, and perform the VLOOKUP on that data. Other options might exist in creating a complex formula that would get you what you want.
Also, if you can sort column A (names) it would find results faster, if your data set is large.

Feb 03, 2008 | Microsoft Excel for PC

1 Answer

Excel formula


Hi, I am not really sure if I get your exct meaning, so would I be correct in saying that you wish to copy all the data and structure from DCT INFO across to DCT. If this is correct so long as the dollar data is formatted in the first worksheet to currency then when it is pasted it will maintain its formating into the next sheet.

One question though...Do you wish to change the data on the DCT INFO sheet and wish it to be automatically changed in the DCT sheet?

If yes then you will have to write the following formula into the DCT cells:- =DCT INFO!A1 (where A1 is the first cell of data in sheet DCT INFO that you wish to duplicate.) You can then use auto fill by selecting the cell you just typed the formula in to and then placing the cursor onto the small black square in the righthand bottom of the cell and clicking the left mouse button and holding and dragging down to whereever your data ends. For example A30. Repeat this with the column B.
Let me know if tis is not entirelt what you require and I will try to help further.
Regards
SeaJade

Jan 09, 2008 | Computers & Internet

1 Answer

Vloop


Are you referring to the VLOOKUP function in Microsoft Excel?

I love vlookup!

Suppose you have 1 worksheet with song numbers and titles in Row 1, Cols A:B:

Song# Title
123 Love Me Tender
234 Blue Suede Shoes
345 Dixie

Another worksheet has song number and performer in Row 1, Cols A:B

Song# Performer
123 Elvis Presley
234 Carl Perkins
456 Cher

Notice there is NO performer for song number 345 in the 2nd worksheet.

Now in the 1st work sheet, cell C2 insert this LOOKUP function: =LOOKUP(A2,Sheet2!A:B)

Copy that cell to row 3 and row 4 in Col C. You should get a Performer for all songs even though there is not a song number 345 in the performer worksheet.

Help me out Mr. VLOOKUP.

Insert this VLOOKUP function in cell C2 of the first worksheet: =VLOOKUP(A2,Sheet2!A:B,2,0)

Copy that cell to row 3 and row 4 Col C. You should get the performer names for the 1st 2 songs, but not for 345 Dixie. The result should be #N/A.

That means VLOOKUP could not find a DIRECT match for song 345 in the second worksheet.

That is why I prefer VLOOKUP over LOOKUP.

I have found this explaination of the VLOOKUP parameters helpful:

1. Needle (A2)
2. Haystack (Sheet2!A:B)
3. RELATIVE Col containing result (2)
4. Need DIRECT MATCH ONLY (0)

Hope this helps.

Jan 07, 2008 | Computers & Internet

Not finding what you are looking for?
Microsoft Excel for PC Logo

Related Topics:

240 people viewed this question

Ask a Question

Usually answered in minutes!

Top Microsoft Computers & Internet Experts

micky dee

Level 3 Expert

2642 Answers

Les Dickinson
Les Dickinson

Level 3 Expert

18358 Answers

Brian Sullivan
Brian Sullivan

Level 3 Expert

27725 Answers

Are you a Microsoft Computer and Internet Expert? Answer questions, earn points and help others

Answer questions

Manuals & User Guides

Loading...