Jump to content


Photo

autofill formulae

autofill mileage costs absolute reference mixed reference

  • Please log in to reply
2 replies to this topic

#1 excelnewbie

excelnewbie

    Newbie

  • Members
  • Pip
  • 2 posts

Posted 10 April 2016 - 08:05 PM

Hi, I'm a newbie and just trying to create my first spreadsheet and can't manage to do the following. Any tips in beginner speak would be greatly appreciated. Many Thanks in advance

 

I'm trying to create a spreadsheet and I need a list like this.

 

Column B = Colum A1 * Colum H1 (Colum H is a constant)      

 

(Colum A = Miles, Colum B = £/Miles

 

I've tried to autofill Column B downwards so that I don't need to copy formula for every row but it doesn't change the reference to the row.

 

This is how I want it to function:

A1 * H1

A2 * H1

A3 * H1

 

Is there a way to autofill that?



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 843 posts

Posted 10 April 2016 - 09:28 PM

Hi Excelnewbie,

 

What you're looking for is an absolute reference. These are cell references that you don't want to be changed when you fill the formula down.

 

The best place to start is the video lesson: Essential Skills Lesson 3-12 Understand absolute and relative cell references.

This will explain absolute and relative cell references much better than I could here.

 

In your specific example, the solution would be to create the formula like this:

=A1*$H$1

 

By using the $ signs, you're telling Excel that it shouldn't change the reference to cell H1 when the formula is filled down.

 

You can also go a step further and only use a single $ sign to tell Excel that it shouldn't change the row or column part of a reference but can still change the other part. For example, $H1 would allow the row numbers to increment as the formula was filled down but wouldn't allow the reference to column H to be changed if the formula was filled across.

 

You can learn all about mixed cell references in the video lesson: Essential Skills Lesson 3-13 Understand mixed cell references.

 

I hope this answers your question and you're able to create the workbook you need, but please feel free to reply if you need any more help.


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 excelnewbie

excelnewbie

    Newbie

  • Members
  • Pip
  • 2 posts

Posted 11 April 2016 - 05:42 AM

Many Thanks Jonathan, I will trying it out shortly and will be watching the videos. As I need the spreadsheet quickly I was skipping through the content but couldn't find anything as I don't know what to look for yet yet. So many Thanks again.







0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users