+ Reply to Thread
Results 1 to 7 of 7

Change range in formula based on changing row count

Hybrid View

  1. 04-17-2012, 04:48 PM #1
    ron2k_1
    ron2k_1 is offline
    Valued Forum Contributor ron2k_1's Avatar
    Join Date
    09-30-2009
    Location
    Not the USA
    MS-Off Ver
    Excel 2003, 2007
    Posts
    606

    Re: Change range in formula based on changing row count

    The formula is evaluating data tables that are available on the same workbook. If you Insert rows, the range will update automatically. I guess you can you some sort of INDIRECT to accommodate the array formula =MAX(ROW(A1:A500)*(A1:A500<>"")) which will give the last used row, but it will be very slow and memory hungry.

    I suggest that you just do a find/replace business (Ctrl + H) and look for the row number you have on your formula and then replace it with whatever you have. I, personally, would include at least 10% more rows than you actually need just to be safe.
    Ron
    Knowledge is knowing that a tomato and pepper are fruits. Wisdom is knowing whether to put these in a fruit salad

    Kindly

    [1] Use code tags. Place "[code]" before the first line of code and "[/code"]" after the last line of code. Exclude quotation marks
    [2] Mark your post [SOLVED] if it has been answered satisfactorily by editing your original post in advanced mode.
    [3] Thank (using the little scale) those that provided useful help; its nice and its very well appreciated
    Reply With QuoteRegister To Reply
  2. 04-18-2012, 09:49 AM #2
    razz0807
    razz0807 is offline
    Registered User
    Join Date
    05-13-2010
    Location
    New Brunwsick, Canada
    MS-Off Ver
    Excel 2013
    Posts
    22

    Re: Change range in formula based on changing row count

    Tried this approach for a while and it was becoming a real pain to maintain which is why I was looking for a better solution. (Multiple people were updating the source data and not always inserting to maintain the range).
    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