Jump to content


Photo

On test 77-428 linking to external data

cross-worksheet formulas autofill arrange windows absolute reference

  • Please log in to reply
4 replies to this topic

#1 BobbieJo

BobbieJo

    Newbie

  • Members
  • Pip
  • 5 posts

Posted 27 December 2015 - 05:20 PM

The test is set up so any files you need are already open and you just need to click view and switch windows. The question wants you to get snn from other file to the current file. When I do it at home I just put an = in cell 2 and then switch windows and highlight the column and switch back to first window and hit enter and it works smoothly. On the test cell 2 gives a value error but when I drag down the column the rest of the data is correct. On the test the header column name is already there so I tried both including the header from the other sheet to import and not using the header and it came up with a value error both ways. At home I tried it both ways and the data import works correctly. Any thoughts? I attached small files below.

 

Thanks for any help. This test is making me crazy!!!

Attached Files



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 862 posts

Posted 27 December 2015 - 11:18 PM

Hi BobbieJo,

 

I have tried the same thing using your attached files and I can't recreate the problem you were having on the exam. I can only wonder if there might be something special about the files used on the test?

 

You should be able to create cross-workbook formulas using the skills taught in: Essential Skills Lesson 6-6 Create cross worksheet formulas.

 

Your formulas should look something like this:

=[workbook1.xlsx]Sheet1!$B$2

 

If the formulas look different, the differences might point to the reason why this is happening.

 

I'm sorry I don't have a more precise answer - it sounds like you're doing everything correctly, so I'm not sure what the problem might be. If you do spot a difference, let me know and I might be able to figure out the reason.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#3 BobbieJo

BobbieJo

    Newbie

  • Members
  • Pip
  • 5 posts

Posted 30 December 2015 - 06:37 PM

I took the exam again today and of course failed. The problem I noticed this time was they were asking to link external data from C3:C28 and bring it into B2:B27 and I would get the value error in B2 but the rest of the column was correct. The only thing I can think of is because the test asked for none matching cells C3 to B2??? any thoughts??



#4 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 862 posts

Posted 31 December 2015 - 12:37 PM

Hi BobbieJo,

 

I think I understand the problem now.

 

When you create a cross-workbook formula by pointing and clicking, you get a formula like this: =[workbook1.xlsx]Sheet1!$B$2

 

This represents the absolute reference $B$2. Because it's an absolute reference, it will not change when you AutoFill it down the column. In order to allow it to AutoFill, you need to change the formula to: =[workbook1.xlsx]Sheet1!$B2

 

You can find a complete explanation of absolute and relative references in the video tutorial: Essential Skills Lesson 3-13 Understand mixed cell references.

  1. Open both workbooks.
     
  2. View the workbooks side by side by clicking View > Window > Arrange All > Vertical.

    workbooklink1.png

    This isn't strictly necessary, but it makes it easier to create the formulas.

    Using Arrange All is covered more extensively in the video lesson: Essential Skills Lesson 2-2 Create a new workbook and view two workbooks at the same time.
     
  3. Click in cell C2 in the Workbook2 window (the first cell in the empty SNN column).
     
  4. Press the = key to start a formula.
     
  5. Click in cell B2 in the Workbook1 window (the first of the populated SNN numbers).

    workbooklink2.png

    You should see the formula: =[workbook1.xlsx]Sheet1!$B$2
     
  6. Modify the formula to: =[workbook1.xlsx]Sheet1!$B2
     
  7. Click and drag the AutoFill handle from cell C2 to the bottom of column C.

    workbooklink3.png

    As you can see, the numbers are shown correctly.

    For more on how to use AutoFill, see: Essential Skills Lesson 2-17 Speed up your AutoFills and create a custom fill series.

If you follow these steps on your exam, the formulas should work correctly. If they don't, there must be some unusual feature of the workbook that you are working on, although I can't think of any that should prevent this from working. Please let me know if you do figure out the cause, as it would be useful information if this question comes up again in the future.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#5 BobbieJo

BobbieJo

    Newbie

  • Members
  • Pip
  • 5 posts

Posted 01 January 2016 - 09:27 PM

I will let you know if it works on the next test. This Excel Expert 77-428 test is crazy. They ask for things that you would not normally do. Another example is it wants you to set a warning on a column but, NOT show the warning when cell is selected and Not highlight and NOT show warning if invalid data is entered. So what is the point of the warning???? I passed the part one test with no problem put this one is crazy. Also it asks for you to lock and hide cells which is not a problem but gives error if you try to protect the sheet which is the only way to activate the hide or lock on cells. I have emailed them and have not heard back. I have spent over $600.00 on taking this one test. I just can't give up at this point now it is just for my own satisfaction to pass it. Any thoughts would be appreciated. Thanks again for your help.







0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users