Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Copying data across worksheets in the same workbook


I am using Excel in Microsoft Office 2010, and I have created a Workbook for Golf Opens that contains 3 Competitions. Each competition is created on 4 worksheets, each competition is seperate and will contain different information. The worksheets are

1. All application data including date, time, name, handicap, entry amount, cheque no, and if cancelled date and cheque number that has returned the money. Also this has the players partners name etc.

2. A startsheet, which contains tee off time name of team i.e. the 2 names from the application sheet and which is their home club the players handicap allowances plus a result column.

3. A Results sheet which is a direct copy of the startsheet. The Result column is manually sorted using the sort system at different times during the day to  ascertain the leaderboard

4. A balance sheet.

The problem I am having is copying the data from one worksheet to another, and instead of getting the name I get the formulae i.e. ='entrysheet1'!j10 instead of John Doe for example. The columns are formatted identically and I cannot find out why this is occurring in some columns when I get the required result in another column.

Can anyone assist please ?  



Selected Answer

This is the correct way to get information from cell A2 on worksheet 1:


It could be that you entered the formula incorrectly in some of the columns or that you need to use absolute cell references instead of relative cell references.

Here is the same formula as above, but with absolute cell references:


Also, you need to remember that when you sort data on one sheet, that doesn't sort them on all of the sheets. This means that if I sorted data in column A of sheet 1, the above formula would not change to "follow" the cell to its new location on sheet 1.


Of course it has to do with formatting and not formulas hah.

That column of data is formatted as Text and the formula was entered such that Excel interpreted it as Text instead of executing it.


Select the column and set the formatting to General (on the Home tab in the Number section). Then select the first cell, hit F2 and then Enter -  you can do this very quickly for the entire list.



Thanks Don, I will try what you suggest, I take it by making every formulae for copying data it should show the result not the formulae. Re the sort, that will only take place on the result sheet. Using the result column as the sort data from highest to lowest.
Thanks again Jim
Jim W (rep: 14) Apr 8, '17 at 4:33 am
There are a number of reasons you could be seeing the formula instead of the result. If my answer doesn't solve the problem, then edit your question and upload a sample sheet that shows the problem.
don (rep: 1979) Apr 8, '17 at 5:29 am
Hi Don . I have done that uploaded the Excel file. you will see that it is the Club name that it does not like, all the others have been formatted as you say with a relative reference 
Jim W (rep: 14) Apr 8, '17 at 10:14 am
Thanks Don. Now doing what you have suggested regarding the formatting of the worksheet has solved the problem, I don't think I would have got to that for a long time. I had tried it as General as well, but what F2 did I do not know, any way problem solved thank you.
Jim W (rep: 14) Apr 10, '17 at 10:43 am
Hi Don. The change in formatting advice was spot on the data now works a treat. Thankyou Jim
Jim W (rep: 14) Apr 10, '17 at 10:47 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login