+ Reply to Thread
Results 1 to 5 of 5

Fill dynamic range without VBA?

Hybrid View

  1. #1
    Registered User
    Join Date
    01-29-2019
    Location
    Russia
    MS-Off Ver
    2013
    Posts
    2

    Fill dynamic range without VBA?

    Hi, sorry in advance for probably stupid question, but I did not find any clear reference for such simple task (daughter needs help for school exercise):
    1.
    I have “Start” in A1
    I have “End” in A2
    I have ”Increment” in A3
    I need to fill dynamic number of rows in column C with values from “Start” up to “End“ with “Increment”
    Examples:
    1) A1=1 A2=10 A3=1 -> 10 rows filled in column C (C1=1, C2=2, C3=3, C4=4, C5=5, C6=6, C7=7, C8=8, C9=9, C10=10)
    2) A1=5 A2=10 A3=1 -> 5 rows filled in column C (C1=5, C2=6, C3=7, C4=8, C5=9, C6=10)
    3) A1=1 A2=10 A3=4 -> 3 rows filled in column C(C1=1, C2=5, C3=9)
    Etc.
    2.
    I need to fill dynamic number of rows in column D with values based on function from dynamic range in column C. Let’s assume D=2*C, then for above mentioned examples:
    1) A1=1 A2=10 A3=1 -> 10 rows filled in column D (D1=2, D2=4, D3=6, D4=8, D5=10, D6=12, D7=14, D8=16, D9=18, D10=20)
    2) A1=5 A2=10 A3=1 -> 5 rows filled in column D (D1=10, D2=12, D3=14, D4=16, D5=18, D6=20)
    3) A1=1 A2=10 A3=4 -> 3 rows filled in column D(D1=2, D2=10, D3=18)
    Etc.
    3.I need to visualize it (plot a graph D(C)), so that entire graph is visible

    Questions:
    1.Is it possible without VBA?
    2.If possible some hints/examples/links

    Thanks

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,411

    Re: Fill dynamic range without VBA?

    In C1: IF(A1="","",A1)

    C2: =IF(OR(C1="",C1+A$3>A$2),"",C1+A$3))

    Copy formula in C2 down as far as you like.

    D1: =IF(C1="","",C1*2)

    Copy down as far as you like.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    01-29-2019
    Location
    Russia
    MS-Off Ver
    2013
    Posts
    2

    Re: Fill dynamic range without VBA?

    Thanks for try to help, regretfully it is not what I want:
    Keyword here is “dynamic”, in your solution “Copy formula as far as you like” is exactly what I want to prevent. In other words changing values in A1-A3 should change resulting range and set formulas in that range.
    I am close to solution not to use ranges and just “set conditional formula for entire column C and D”.

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,411

    Re: Fill dynamic range without VBA?

    You can't do that without VBA.

    Pete

  5. #5
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,222

    Re: Fill dynamic range without VBA?

    Is this right?

    C1
    =IFERROR(INDEX((ROW(INDIRECT("1:"&INT(($A$2-$A$1+$A$3)/$A$3)))-1)*$A$3+$A$1,ROW()),"")
    D1
    =IFERROR(C1*2,"")

    copy C1:D1 down

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. VBA Dynamic Range Auto-fill Series
    By fireboltpk in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-30-2017, 08:40 AM
  2. How to Auto Fill base on a dynamic range
    By alexduy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-18-2014, 03:04 PM
  3. [SOLVED] Colour fill of empty cells in a dynamic range
    By Coems in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 09-19-2014, 06:27 PM
  4. Fill Dynamic Range From Dynamic Source Range
    By goss in forum Excel General
    Replies: 2
    Last Post: 03-06-2012, 12:05 PM
  5. Fill in Formula to Dynamic Range
    By Mut in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-14-2009, 04:08 AM
  6. list box fill range to be taken from dynamic column heading match
    By kuraitori in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-30-2009, 12:45 PM
  7. Auto fill dynamic range based on month
    By [nordis] in forum Excel General
    Replies: 0
    Last Post: 01-14-2005, 08:53 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1