+ Reply to Thread
Results 1 to 9 of 9

Indirect function / absolute reference - multiple rows -new cells inserted by shiftingdown

  1. 07-12-2012, 03:18 AM #1
    pandyaketan
    pandyaketan is offline
    Registered User
    Join Date
    05-28-2012
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    11

    Post Indirect function / absolute reference - multiple rows -new cells inserted by shiftingdown

    Problem, i use Excel 2003:

    If I insert cells here by shifting cell down, Simple Moving Average (20) reference also shifts down and there is no reference for the newly inserted cells!!

    I want "absolute reference" or "INDIRECT function" reference.

    I get this using indirect function, which works.

    =AVERAGE(OFFSET(INDIRECT("E1"),0,0,20,1)),0)

    But the problem is:

    1. i have to manually change "E1" to "E2", "E3", etc till,say, 500 rows!!

    Is there a way to make it an array, using indirect with array, vlookup, match, address, row, etc functions??

    reg
    ketan
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 07-12-2012, 03:21 AM #2
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,422

    Re: Indirect function / absolute reference - multiple rows -new cells inserted by shifting

    Not tested but try:

    =AVERAGE(OFFSET(INDIRECT("E"&ROW(E1)),0,0,20,1)),0)


    Regards, TMS

    Edit: should have been Row not Column
    Last edited by TMS; 07-12-2012 at 03:23 AM.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  3. 07-12-2012, 03:22 AM #3
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,422

    Re: Indirect function / absolute reference - multiple rows -new cells inserted by shifting

    Or maybe just:

    =AVERAGE(OFFSET(INDIRECT(E1),0,0,20,1)),0)

    Regards, TMS
    Reply With QuoteRegister To Reply
  4. 07-12-2012, 03:33 AM #4
    pandyaketan
    pandyaketan is offline
    Registered User
    Join Date
    05-28-2012
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Indirect function / absolute reference - multiple rows -new cells inserted by shifting

    indirect("E" & row(E1)) works but if i insert new OHLC, by shifting cells down, the newly inserted cells have no formula.

    Indirect(E1) gives #Ref error.

    Super fast response from you, atleast thanks for that! Sigh... problem still persists!! May i request you to open the sheet...??

    reg
    ketan
    --------------------------------------------------------
    "May the good belong to all the people in the world.
    May the rulers go by the path of justice.
    May the best of men and their source always prove to be a blessing.
    May all the world rejoice in happiness.
    May rain come in time and plentifulness be on Earth.
    May this world be free from suffering and the noble ones be free from fears"
    ---- Vedic blessing
    Reply With QuoteRegister To Reply
  5. 07-12-2012, 04:27 AM #5
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,422

    Re: Indirect function / absolute reference - multiple rows -new cells inserted by shifting

    Select all the data in the table (A1:G43); Select Data | List | Create List and tick "My data has headers".

    Now, when you insert rows, formulae and formatting will be carried through. You will also get Autofilter turned on for the table.

    The first formula seems to work but, in all honesty, I'm not sure what you are doing so I don't know if the results are what you want.

    Regards, TMS
    Reply With QuoteRegister To Reply
  6. 07-12-2012, 04:42 AM #6
    pandyaketan
    pandyaketan is offline
    Registered User
    Join Date
    05-28-2012
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Indirect function / absolute reference - multiple rows -new cells inserted by shifting

    i am just trying to manually add end of day data, every day to get the latest moving average (20). however by adding a new day (by shifting cells down), the reference of the moving avg shifts down.

    The newly added date, open, high, low and close do not have a moving avg linked to it, since reference does not remain absolute. Mov avg formula also shifts downwards, alongwith the cells.

    When you trace precedents of the MAvg formula, in say, cell G2 - it shows E3 instead of E2, corresponding to the MA in G2!!

    I shall try your suggestion now and will revert...thanks again for some really quick help!

    reg
    ketan
    Last edited by pandyaketan; 07-12-2012 at 04:44 AM. Reason: more details on G2 & E2
    Reply With QuoteRegister To Reply
  7. 07-12-2012, 04:49 AM #7
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,422

    Re: Indirect function / absolute reference - multiple rows -new cells inserted by shifting

    You're welcome. Good luck. Thanks for the rep.
    Reply With QuoteRegister To Reply
  8. 07-13-2012, 06:30 AM #8
    pandyaketan
    pandyaketan is offline
    Registered User
    Join Date
    05-28-2012
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Indirect function / absolute reference - multiple rows -new cells inserted by shifting

    Creating a List 'DID' help, but, as you mentioned, Auto filter got turned on, as well as other "inconsistent formula' flags also started flashing!

    Is there any other option??!

    reg
    ketan
    Reply With QuoteRegister To Reply
  9. 07-13-2012, 06:48 AM #9
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,422

    Re: Indirect function / absolute reference - multiple rows -new cells inserted by shifting

    Well, inconsistent formulae could be a worry.

    Can't think of another way other than maybe a VBA routine to check, say column A, for blank cells and copy formulae and formatting from the previous row. That would probably need to be driven by a Selection Change or Calculate event.

    Regards, TMS
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1