How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask John D Your Own Question

John D
John D, Engineering Consultant
Category: Networking
Satisfied Customers: 9659
Experience:  Bachelor of Science - Engineering Consultant
53785
Type Your Networking Question Here...
John D is online now
A new question is answered every 9 seconds

I lost the relative reference function in Excel. Did I do

Customer Question

I lost the relative reference function in Excel. Did I do something wrong?
Submitted: 3 years ago.
Category: Networking
Expert:  John D replied 3 years ago.

Hi,

 

1) Which version of Excel are you using

2) Do you mean the column letters on top, or relative reference during recording a macro, or something else

 

 

Customer: replied 3 years ago.
Excel in Office 2007, the relative references referred to by macros or individually. All relative references are displayed as absolutes.
Customer: replied 3 years ago.
Excel in Office 2007, the relative references referred to by macros or individually. All relative references are displayed as absolutes.

The formulas display as expected as relative but the results displayed are absolutes.
Expert:  John D replied 3 years ago.

Thanks

 

Ok, click the Developer tab on the Ribbon, then click the Use Relative References button so it gets highlighted, as shown below

 

graphic

 

That should do it

 

 

 

Customer: replied 3 years ago.
I have tried this numerous times but it has no effect.
Customer: replied 3 years ago.
I have tried this numerous times but it has no effect.


( I tried it over and over for the two or three days since the problem first appeared.)
Expert:  John D replied 3 years ago.

Perhaps you are expecting something else from this option. Can you give an example showing that it is not working

 

Customer: replied 3 years ago.
if i copy a column of references to the next column it normally copies the references as relative. now the formulas in the next column read correctly but the numbers duplicate the numbers in the original colunm. In other words, the numbers in the secound column appear as if the formulas were absolute. this is maddening. I have had this Acer for a couple of years and have been using Excel for many years, but have never seen this.
Expert:  John D replied 3 years ago.

This is not in the macro recording mode is it?

 

 

Customer: replied 3 years ago.
I have the problem in the macro recording mode as well as in individual attempts. I cannot find the use of relative referencing at all.
Expert:  John D replied 3 years ago.

Ok, you need to use $ signs with the cell references in the formula so it can act as an absolute or a relative reference

 

Here, in this sample file the formula in E6 is relative, and the one in E7 is absolute

 

http://www.2shared.com/file/Wwg5l4Rc/relative.html

 

Copy E6 to F6, and copy E7 to F7 to see the difference

 

 

Customer: replied 3 years ago.
you are giving me, as I understand it, basic information about absolute and relative references. This does not help me at all.

When I copy a column to another the cells should fill with relative references and they do.
But now the cells display the same numbers as the original column rather than the numbers I have always had. When I tried your example the same error occurred. I copied the cells E6,E7 into F6,F7 and received the values "ABC" in both cells even though the formulas read: "=F1" and "=$E$1".
Expert:  John D replied 3 years ago.

Press these 4 keys together and let me know if values in the cells change

 

CTRL + SHIFT + ALT + F9

 

 

 

Customer: replied 3 years ago.
Yes, the values changed to the correct ones.
Expert:  John D replied 3 years ago.

Ok, that has nothing to do with absolute and relative reference, it is just that your calculation is set to Manual.

 

Click the Office button > Excel Options > Formulas tab (on the left pane), then select Automatic, and click OK

 

That should do it

 

 

Customer: replied 3 years ago.
Sorry. I cannot find the "Office" button
Expert:  John D replied 3 years ago.

You said you have Excel 2007, so here is the Office Button in Excel 2007

 

 

graphic

Customer: replied 3 years ago.
I thought that was the Office button but when I press it "Excel Options" does not appear.
Thank you for your patience.
Expert:  John D replied 3 years ago.

Ok I will send you more screenshots. I think at this point it would be appropriate to click the Accept button

 

John D, Engineering Consultant
Category: Networking
Satisfied Customers: 9659
Experience: Bachelor of Science - Engineering Consultant
John D and 4 other Networking Specialists are ready to help you
Expert:  John D replied 3 years ago.

Thanks

 

After you click the Office button, you should get this dialog box. At the bottom you will find the Excel Options button

 

graphic

 

 

Let me know so we can go to the next step

 

 

Expert:  John D replied 3 years ago.

I sent you a new screenshot, did you see my last post?

 

 

 

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • I am very happy with my very fast response. Eric is very knowledgeable in the subject area. Thank you! RP Austin, TX
< Last | Next >
  • I am very happy with my very fast response. Eric is very knowledgeable in the subject area. Thank you! RP Austin, TX
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
 
 
 

Meet The Experts:

 
 
 
  • James

    Sr. Network Administrator

    Satisfied Customers:

    1711
    Highly experienced with wireless networks and wireless equipment, LAN and networking protocols
< Last | Next >
  • http://ww2.justanswer.com/uploads/BA/barrenrock/2011-10-19_215925_JamesJAFinal.64x64.jpg James's Avatar

    James

    Sr. Network Administrator

    Satisfied Customers:

    1711
    Highly experienced with wireless networks and wireless equipment, LAN and networking protocols
  • http://ww2.justanswer.com/uploads/Claws224/2009-07-07_063935_Dork_Portrait.JPG Claws224's Avatar

    Claws224

    IEEE Network Engineer

    Satisfied Customers:

    1256
    IEEE, Microsoft
  • http://ww2.justanswer.com/uploads/KN/knelly74/2011-4-14_215651_kevin.64x64.jpg Kevin's Avatar

    Kevin

    LAN/WAN Specialist

    Satisfied Customers:

    1165
    Certified MCSA and MCP Network Administrator with over 20 years of PC experience.
  • http://ww2.justanswer.com/uploads/tealnet/2009-09-26_000218_headshot_64.jpg Eric K.'s Avatar

    Eric K.

    Network Administrator

    Satisfied Customers:

    510
    13 years of desktop, server and network support experience
  • http://ww2.justanswer.com/uploads/CL/clagesolutions/2012-6-27_145545_20120608Geoff.64x64.jpg Mr. Clark's Avatar

    Mr. Clark

    Network Engineer

    Satisfied Customers:

    510
    Computer Business Owner / Former DoD Network Specialist
  • http://ww2.justanswer.com/uploads/MA/matthewpj73/2012-1-20_18729_TWO.64x64.jpg Matthew J's Avatar

    Matthew J

    Computer Support Specialist

    Satisfied Customers:

    397
    12+ yrs computer diagnosis/repair, network infrastructure install and support, IT Administration
  • http://ww2.justanswer.com/uploads/SE/Seashore2011/2011-12-6_33418_111205223015.64x64.jpg Chris L.'s Avatar

    Chris L.

    Support Specialist

    Satisfied Customers:

    384
    Certified Networking expert with over 10 years experience.