I have a workbook with 3 sheets. Sheet 1 has the Names, Date of Birth and ADM No. of learners. Sheet 2 and 3 has formulae echo-ing the data in sheet 1 and columns with marks for each of 2 learning areas - one on each sheet. PROBLEM: I need to be able to sort across the sheets ie if the names get swopped in sheet 1 then the corresponding marks in sheet 2 and 3 must also be swopped. Thanks Raymond

Re: Excel Sort Across Sheets

Hi Raymond, I have a solution for you. Asuming your 'main' sheet is Sheet1. On Sheet2, in the same line as your data on sheet1 (i.e. A5 on sheet1 is JIM), then on A5 on sheet2, enter a link ( the = ) and enter. copy this formula down. Do the same with Sheet3 linking to Sheet1. When you sort Sheet1, Sheet 2 and 3 will follow. Hope this helps. Regards DeltaC

Posted on Sep 13, 2007

Do you mean to display multiple sheets at the same time? Please check out the solution provided below (this applies to Excel 2003)

1.) To view multiple sheets in the active workbook, click**New Window** on the **Window** menu. Switch to the new window, and then click a sheet you want to view. Repeat for each sheet you want to view.

2.) On the**Window** menu, click **Arrange**.

3.) Under**Arrange**, click the option you want.

To view sheets in only the active workbook, select the**Windows of active workbook** check box.

1.) To view multiple sheets in the active workbook, click

2.) On the

3.) Under

To view sheets in only the active workbook, select the

Apr 12, 2010 | Microsoft Excel for PC

did you put the sheet name in your reference? ex =Sheet2!A1 or you can name the cell ranges on the other sheets and use the cell names in your conditional formatting formla.

May 08, 2009 | Microsoft Excel for PC

Use the IF and Date functions together. On sheet 3, put the birthday (3/15/1954) in cell B1, put the beginning date (1/1/1946) in cell C1 and the ending date (12/31/1964) in cell D1.

Then on Sheet 1, but the following formula in the cell you want an X to be placed in.

=IF(AND(Sheet3!B1>=Sheet3!C1,(Sheet3!B1<=Sheet3!D1)),"X"," ").

Make sure you type the formula Exactly as it appears.

Then on Sheet 1, but the following formula in the cell you want an X to be placed in.

=IF(AND(Sheet3!B1>=Sheet3!C1,(Sheet3!B1<=Sheet3!D1)),"X"," ").

Make sure you type the formula Exactly as it appears.

Mar 20, 2009 | Oracle Database Enterprise Named User Plus

=VLOOKUP(A2;Sheet1.$A$3:D27;2;0)

The cell I created this formula in was Sheet 3 Cell C9 - to show the different sheets

A2 is the cell I want to look up

Sheet1.A3:D27 is the range of cells that contains the data I want to return, The first column relates directly to cell C9 is Sheet 3. I locked the first cell in my range as I wanted to apply the same formula across other cells hence the $

2 is the number of the column that has the data I want to return, I had a choice in this formula of 4 columns

0 is the value to complete the formula

The cell I created this formula in was Sheet 3 Cell C9 - to show the different sheets

A2 is the cell I want to look up

Sheet1.A3:D27 is the range of cells that contains the data I want to return, The first column relates directly to cell C9 is Sheet 3. I locked the first cell in my range as I wanted to apply the same formula across other cells hence the $

2 is the number of the column that has the data I want to return, I had a choice in this formula of 4 columns

0 is the value to complete the formula

Feb 11, 2009 | Microsoft Excel for PC

You probably need to organise your pricelist sheet so that you only have 2 columns, product and price. If this doesn't fix you, please provide the syntax of your vlookup code.

Dec 10, 2008 | Microsoft Excel for PC

Nope, sorry, although I am truly an expert at Excel formulas, I do not understand what you are trying to end up with in the final cell. We can compare a specified field with two spreadsheets - use named ranges and index/match lookup formulas. But then where you really lose me is in reading "a generic field" to find a match, and then placing what "data from another field" into what "other sheet" - ? See the confusion?

Best way to compare 2 given parameters would be to use a nested if formula, with index/match combo. Here is a simple Excel example of how such a formula could be structured:

Sample Data (columnar arangement):

A1: Part B1: Code C1: Price D1: Find Part E1: Find Code

A2: x B2: 11 C2: 5.00 D2: y E2: 12

A3: x B3: 12 C3: 6.00 D3: y E3: 11

A4: y B4: 11 C4: 7.00 D4: x E4: 12

A5: y B5: 12 C5: 8.00 D5: x E5: 11

To retrieve the price for part y with code 12 and return the value to cell F2, type the following formula in cell F2:

=INDEX($C$2:$C$5,MATCH(D2,IF($B$2:$B$5=E2,$A$2:$A$5),0))

Press CTRL+SHIFT+ENTER to enter the formula as an array formula. The formula returns the value 8.00.

To take this one step further, with range names, this example will find one value at a specified location which matches a specific row header value and column header value. Let's say the range is home values (Range=HomeVal), Column A of HomeVal contains street addresses,"row headers" (Range=StAddress), and Row 1 contains dates of the various values that are in the body of the table, "column headers" (Range=Dates). To return the specific value from the range HomeVal to another sheet, where A1=address specified and A2=date specified:

=INDEX(HomeVal,(MATCH($A$1,StAddress,0)),(MATCH($A$2,Dates,0)))

Then make sure to press CTRL+SHIFT+ENTER to enter the formula as an array formula - if you only hit enter, these types of formulas will not work properly.

Please post back if you need further help, with more details, otherwise thank you for using and rating FixYa!

Best way to compare 2 given parameters would be to use a nested if formula, with index/match combo. Here is a simple Excel example of how such a formula could be structured:

Sample Data (columnar arangement):

A1: Part B1: Code C1: Price D1: Find Part E1: Find Code

A2: x B2: 11 C2: 5.00 D2: y E2: 12

A3: x B3: 12 C3: 6.00 D3: y E3: 11

A4: y B4: 11 C4: 7.00 D4: x E4: 12

A5: y B5: 12 C5: 8.00 D5: x E5: 11

To retrieve the price for part y with code 12 and return the value to cell F2, type the following formula in cell F2:

=INDEX($C$2:$C$5,MATCH(D2,IF($B$2:$B$5=E2,$A$2:$A$5),0))

Press CTRL+SHIFT+ENTER to enter the formula as an array formula. The formula returns the value 8.00.

To take this one step further, with range names, this example will find one value at a specified location which matches a specific row header value and column header value. Let's say the range is home values (Range=HomeVal), Column A of HomeVal contains street addresses,"row headers" (Range=StAddress), and Row 1 contains dates of the various values that are in the body of the table, "column headers" (Range=Dates). To return the specific value from the range HomeVal to another sheet, where A1=address specified and A2=date specified:

=INDEX(HomeVal,(MATCH($A$1,StAddress,0)),(MATCH($A$2,Dates,0)))

Then make sure to press CTRL+SHIFT+ENTER to enter the formula as an array formula - if you only hit enter, these types of formulas will not work properly.

Please post back if you need further help, with more details, otherwise thank you for using and rating FixYa!

Jul 08, 2008 | Microsoft Business & Productivity Software

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.

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

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

Is it OK?

Is it OK?

Mar 08, 2008 | Microsoft Excel for PC

I have solution for your second problem.

You just need to use macros to protect all the sheet at the same time. Just open "Macro>Record new Macro" to insert new macros to your files.

This will save some your valuable time.

You just need to use macros to protect all the sheet at the same time. Just open "Macro>Record new Macro" to insert new macros to your files.

This will save some your valuable time.

Jan 09, 2008 | Business & Productivity Software

Try these links you could find out

http://www.vbaexpress.com/kb/getarticle.php?kb_id=706

http://www.computing.net/programming/wwwboard/forum/5902.html

http://www.vbaexpress.com/kb/getarticle.php?kb_id=706

http://www.computing.net/programming/wwwboard/forum/5902.html

Sep 23, 2007 | Microsoft Excel for PC

Apr 25, 2014 | Microsoft Office Standard for PC

393 people viewed this question

Usually answered in minutes!

Hi DeltaC

Thanks but the problem is the rest of the columns on sheet2 and Sheet3 that are not linked to sheet1. So if sheet1 has only the names and sheet2 has names and say marks then the marks will remain where they are and be in the line of the wrong names.

Thanks

Ray,ond

The rest of the columns in shetts 2 and 3 will stay where they are - so they will not match the correct names. So if JIM moves one row down he will appear 1 row down on all 3 sheets but the other fields that were in line of JIM on sheets 2 and 3 will remain where they are.

Thanks

Hi Sir.

This is srinivas i want to learn excel sheet

but i unable to understand what i read above.

Please do the needful and solve the problem

×