Extend range of formula when new record is created

Geoffrey1231

New Member
Joined
Oct 28, 2005
Messages
22
Hi there,
I have a two-part question;
How would I extend a formula automatically. Anotherwords, the formula sums say E5:E10. What I want to do is make sure that whenever a new record is created below E10 that the formula extends to include that cell. So if a number was placed in E20, then the formula would be the sum of E5:E20.

The second part of my question is how to handle the same type of issue but for extending an "If" statement and a drop down list when a new record is added. So if I start typing in the first cell of the next row, then the lists and "If" statements should carry down to that row.

Hope you can help,

Geoff
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
Question number 1:
You can sum the entire column, providing there are no other numbers in that column, the SUM function will ignore text. Otherwise you can also setup a dynamic named range.

Question number 2:
I'm a little lost there, can you provide some examples?
 

Geoffrey1231

New Member
Joined
Oct 28, 2005
Messages
22
Thank you firefyter. You answered the first part. For the second part example; I type a name in cell B5 then choose from a drop down list in cell C5 to choose whether the employee is part time, full time etc. In cell D5 I have a vlookup function. The drop down list was created by naming a range of cells to look up using (name>define). So rather than copying the drop down list and the vlookup function to the next 1000 rows, I thought it would be more appropriate to have excel know to extend this information down when I type in a new name into the spreadsheet.

Hope this makes more sense,

Geoff
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Geoffrey1231 said:
Thank you firefyter. You answered the first part. For the second part example; I type a name in cell B5 then choose from a drop down list in cell C5 to choose whether the employee is part time, full time etc. In cell D5 I have a vlookup function. The drop down list was created by naming a range of cells to look up using (name>define). So rather than copying the drop down list and the vlookup function to the next 1000 rows, I thought it would be more appropriate to have excel know to extend this information down when I type in a new name into the spreadsheet.

Hope this makes more sense,

Geoff

What sheet house the employee names and their full/part time status? What is the current range? And are you on Excel 2003?
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

Barring what Aladin may have in mind, or using VBA, I generally use Ctrl + D to FillDown my data. This is a couple of extra clicks but won't take up very much time. Now as far as your formula goes, you can use Extend Formats (Tools | Options | Edit (tab) | Extend list formats and formulas), but you must have your sheet formatted correctly. The Help files document it fairly well. This is in 2000 and beyond, not sure about 97.
 

Geoffrey1231

New Member
Joined
Oct 28, 2005
Messages
22
Ok..Aladin-the employee statuses are listed on the same sheet and are from P4-Q12. The version is excel 2000.

Firefytr-I have chosen the option to extend formats but it doesn't seem to work. You mentioned my sheet must be formatted correctly.....how so?

Thank you,
Geoff
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
If you read the help files, (if I remember correctly) basically you must have headers and the last three consecutive rows, with more than five total, must have the same formulas and formats. The other side to that is there must be a cell which is manually entered; in other words they can't all be formulas. Generally it's the left-most cell in the range which is manually entered/adjusted.

It does take some getting used to. Imagine Excel was a person doing this, and that person was blindfolded and working under your exact instructions. Do you think it would work all the time in every circumstance? I doubt it. But if you can work it, it can be very nice.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,530
Messages
5,572,669
Members
412,481
Latest member
nhantam
Top