how to write formula for spreadsheet with moving row

  • Thread starter Thread starter stef
  • Start date Start date
S

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?
 
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 said:
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
Click to expand...

Bernard,
Unfortunately, it is not there.....
The only similar box is:
Extend list formats and formulas
 
That's the one.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
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 said:
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?
Click to expand...

Bernard, sure I will.
But I have to remove/break the links first as you will not be able to
see the values otherwise.
 
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
 
Back
Top