Results 1 to 2 of 2

Thread: Need help with dynamic range forumla

  1. #1

    Need help with dynamic range forumla



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I've created a report in excel. The intent is to paste the data into the data tab and refresh the workbook to populate all tabs.

    I have additional columns that contain formulas that populate based on the data entered. Each extract will have a different number of rows, so I would like these columns to only populate based on the number of rows in the data entered. Is there a way to do this and avoid having to drag down formulas or delete extra formulas? Like a form of dynamic range?

    Example:
    Lets say my extract has 10 rows of data. I have column A as open date and column B as closed date.
    If I wanted to have column C automatically populate when the data is entered with turnaround time (closed date - open date), how could I do that only going 10 rows down?

    Lets says the next weeks data extract has 20 rows...could I have column c populate all 20 rows this time without having to drag the formulas 10 more rows down?

    Thanks for any help.

  2. #2
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    Hello
    Without a sample workbook its difficult to assess the practicality of any ideas. However with regard to dynamic ranges, this can be done with a Named Range supported by a variable that calculates the number of rows of data
    in the range and applies that in any formula using the range.
    There are a couple of caveats:

    1. To count the rows, there needs to be a column that the variable can use that won't be empty if there is a valid record on that row (e.g. Date, Stock Code).
    2. If you want to operate this on multiple sheets, then a separate Named Range and variable would be needed for each sheet.

Tags for this Thread

Posting Permissions

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