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: 9666
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

This answer was rated:

I lost the relative reference function in Excel. Did I do something wrong?

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 6 years ago.
Excel in Office 2007, the relative references referred to by macros or individually. All relative references are displayed as absolutes.
Customer: replied 6 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.

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 6 years ago.
I have tried this numerous times but it has no effect.
Customer: replied 6 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.)

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

 

Customer: replied 6 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.

This is not in the macro recording mode is it?

 

 

Customer: replied 6 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.

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 6 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".

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

 

CTRL + SHIFT + ALT + F9

 

 

 

Customer: replied 6 years ago.
Yes, the values changed to the correct ones.

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 6 years ago.
Sorry. I cannot find the "Office" button

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

 

 

graphic

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

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

 

John D and other Networking Specialists are ready to help you

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

 

 

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

 

 

 

Related Networking Questions