Jump to content


Photo

Absolute Reference Cells - S3Q5

absolute reference

  • Please log in to reply
6 replies to this topic

#1 ajbozdar

ajbozdar

    Member

  • Members
  • PipPip
  • 12 posts
  • LocationIndependent

Posted 21 August 2017 - 05:17 PM

Hi There,

 

While working on Session 3 Exercise, Question 5, I didn't find any difference between following E3 absolute reference cells?

=D6*$E$3
and
=D6*E$3

Both formulas show same results.

 

 

Thanks.

 

 

If you find my Questions and/or Answers helpful, please click like button. Thank you.



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 846 posts

Posted 22 August 2017 - 10:19 AM

Hi ajbozdar,

 

The reason it's important to use an absolute reference is so that the formula can be properly filled down in Question 6.

 

You're right that using $E$3 or E$3 would both be correct answers in this case, since the formula only needs to be filled down and is never filled across. However, since there is no possibility that you would ever want Excel to adjust references to cell E3 it's still better to use $E$3 as it eliminates any possibility of a mistake.

 

Another advantage of the $E$3 approach is that you can apply it with a single keystroke by simply pressing <F4> after selecting the cell while building your formula.

 

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

 

I hope this is the answer you were looking for, but please feel free to reply if you have any more questions about this.


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 ajbozdar

ajbozdar

    Member

  • Members
  • PipPip
  • 12 posts
  • LocationIndependent

Posted 23 August 2017 - 07:04 PM

Hi Jonathan,

 

Thank you very much for the help.

 

there is no possibility that you would ever want Excel to adjust references to cell E3

 

 

How does Excel auto adjust references to cells? When does it happen?

 

And, today when I was solving S4Q14, I realised that I do not have a "Celestial" theme in Excel. Has it been removed, or I have done something wrong?

 

Thanks


Edited by ajbozdar, 23 August 2017 - 07:09 PM.


#4 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 846 posts

Posted 24 August 2017 - 11:38 AM

Hi ajbozdar,

 

The purpose of absolute references is to prevent Excel from automatically adjusting cell references when you fill formulas across several cells.

 

You can see some examples of how this works in: Essential Skills Lesson 3-12 Understand absolute and relative cell references.

 

Absolute and relative references can also be used in formula-driven validations and conditional formats.

You can see a formula-driven conditional format in the video tutorial: Essential Skills Lesson 4-18 Create a formula driven conditional format.

For formula-driven validations, see: Expert Skills Lesson 2-12 Use a formula-driven custom validation to enforce complex business rules.

 

I have checked my own copy of Excel (2016) and the Celestial theme does appear in the list, so I'm not sure why it is not appearing in yours. I also can't find the place in the book that refers to the Celestial theme; can you let me know which specific lesson this is appearing in?


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 ajbozdar

ajbozdar

    Member

  • Members
  • PipPip
  • 12 posts
  • LocationIndependent

Posted 25 August 2017 - 08:04 AM

I also can't find the place in the book that refers to the Celestial theme; can you let me know which specific lesson this is appearing in?

 

Session 4 Exercise, Question 14.



#6 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 846 posts

Posted 25 August 2017 - 12:21 PM

Hi ajbozdar,

 

I've looked into this and the Celestial theme is definitely on the list of themes that should be included in Excel 2013. It was not included in any previous versions of Excel, so if you are using an older version it will not appear for you.

 

If you are definitely using Excel 2013, it's possible that you have encountered a bug that has been reported by several other users. It seems that this can happen when you upgrade from Excel 2010 rather than doing a 'clean' install of Excel 2013. If it sounds like this may have happened to you, uninstalling Excel 2010 and Excel 2013 and then doing a fresh install of Excel 2013 should resolve it.


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.


#7 ajbozdar

ajbozdar

    Member

  • Members
  • PipPip
  • 12 posts
  • LocationIndependent

Posted 28 August 2017 - 06:13 PM

Hi Jonathan,

 

Thank you very much for the reply.

 

I have a clean installation of Excel, I repaired it as well. I have Circuit and Crop themes, starting with C, only. I do not find it a big issue if a theme is not available. I have completed Essential Skills Course, and I am ready to create my own themes now.

 

Cheers!







0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users