 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / September 2007how to write formula for spreadsheet with moving row
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  SignatureBernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email > Excel 2002 SP3 > Win XP HE [quoted text clipped - 12 lines] > 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 [quoted text clipped - 10 lines] > 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 That's the one. SignatureHTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) >> I am using XL2003. I cannot recall when the automatic extension of >> formats and formulas happened. Use Tool | Options, open the Edit tab; [quoted text clipped - 15 lines] > The only similar box is: > Extend list formats and formulas > 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? 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? SignatureBernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email >> 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? > 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. 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 What version of Excel are you using? SignatureBernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email > Bernard, > [quoted text clipped - 15 lines] > EggHeadCafe - .NET Developer Portal of Choice > http://www.eggheadcafe.com Bernard, I send you a sample spreadsheet 1 week ago and no news from you. Did you receive it? > 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?
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.