how to write formula for spreadsheet with moving row

[stef]

Excel 2002 SP3
Win XP HE

Hi,

I have a spreadsheet that automatically adds a row every day for some
info retrieved from a server.
I also have two columns I and J which calculate changes in the values,
etc. However, the formulas in I and J have been manually added by
me--which creates an offset every time the spreadsheet updates on a
daily basis and adds a row of data in columns A through H.
I cannot go in everyday and drag (copy/paste) the formula in columns I
and J to adjust for additional rows, as too tedious and I need to do
many spreadsheets as such.
Can anyone suggest a way to have columns I and J automatically adjust
for the additional rows appearing in columns A through H?

 

[Bernard Liengme]

I am using XL2003. I cannot recall when the automatic extension of formats
and formulas happened. Use Tool | Options, open the Edit tab; look for box
"extend data range formats and formulas".

I entered (starting in A1) these values and formulas:
1 4 =B1-A1
5 10 =B2-A2
6 12 =B3-A3
6 10 =B4-A4
When a type the next two numbers, say
4 8 Excel automatically add the formula in C5.

Works if there are blank columns between the numbers and the formulas.
best wishes

 

[stef]

I am using XL2003. I cannot recall when the automatic extension of formats
and formulas happened. Use Tool | Options, open the Edit tab; look for box
"extend data range formats and formulas".

I entered (starting in A1) these values and formulas:
1 4 =B1-A1
5 10 =B2-A2
6 12 =B3-A3
6 10 =B4-A4
When a type the next two numbers, say
4 8 Excel automatically add the formula in C5.

Works if there are blank columns between the numbers and the formulas.
best wishes

Bernard,
Unfortunately, it is not there.....
The only similar box is:
Extend list formats and formulas

 

[Bob Phillips]

That's the one.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[stef]

That's the one.

Well, I must be doing something wrong....
I do not have a blank column between A-H (the automatic) and I-J.
Is that the problem?

 

[Bernard Liengme]

No: that comment was mean as "It EVEN works when there is a blank column..."
Want to email me (my private email) a copy of a sample workbook?

 

[stef]

No: that comment was mean as "It EVEN works when there is a blank column..."
Want to email me (my private email) a copy of a sample workbook?

Bernard, sure I will.
But I have to remove/break the links first as you will not be able to
see the values otherwise.

 

[Krishnakanth Rajaram]

Bernard,

It's the same issue I am facing right now.
I would appreciate your help.

I have data in column A that gets new rows added automatically. I have formula in Column B that uses column A data.

As of now I manually drag column B formula on a daily basis.
Please help me in making it automatic.

I read your previous post, but couldnot make much out of it.

Thank you for your time.

Krishnakanth

EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com

 

[Bernard Liengme]

What version of Excel are you using?

 

[stef]

Bernard,
I send you a sample spreadsheet 1 week ago and no news from you.
Did you receive it?