Extend range of formula when new record is created

[Geoffrey1231]

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

 

[Zack Barresse]

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]

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]

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]

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]

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]

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.