+ Reply to Thread
Results 1 to 5 of 5

Fill columns with formulas containing non-consecutive numbers

  1. #1
    Registered User
    Join Date
    04-19-2004
    Posts
    5

    Fill columns with formulas containing non-consecutive numbers

    I need to have a way to fill columns with formulas that contain non-consecutive numbers. I don't want to have to type in each one, as I have 300 lines to fill.

    Col A Col B Col C

    Row 1 =Sales!E9 =Sales!O7 =CONCATENATE("00",Sales!A7,Sales!A4)

    Row 2 =Sales!E12 =Sales!O10 =CONCATENATE("00",Sales!A10,Sales!A4)

    Row 3 =Sales!E15 =Sales!O13 =CONCATENATE("00"Sales!A13,Sales!A4)


    Hopefully the spacing comes out so people can make this out as top what it should look like.

    I need to increase each formula by 3, instead of 1, due to spacing setup on the Sales page.

    Also, I need to be able to maintain the A4 in the last formula, throughout the whole page. Any way to do that?

    I'm working with Excel 2002, but am upgrading to 2007 in a week or two.

    Thanks for any help.

    Dan

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Fill columns with formulas containing non-consecutive numbers

    Hello,

    In A1, copied down

    =INDIRECT("Sales!E"&ROW()*3+6)

    In B1, copied down

    =INDIRECT("Sales!O"&ROW()*3+4)

    In C1, copied down

    =CONCATENATE("00",INDIRECT("Sales!A"&ROW()*3+4),Sales!$A$4)

    cheers

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Fill columns with formulas containing non-consecutive numbers

    Alternatively - use INDEX (non volatile)

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    04-19-2004
    Posts
    5

    Re: Fill columns with formulas containing non-consecutive numbers

    Thank you both to lyn and Donkey.

    I tried the first solution first, as it seems simpler. Works, with a couple tweaks. I was missing one row using them as given, and I assume the problem was again the spacing on the Sales page. By changing the +6 to a +3 and the +4 to a +1, got the missing row, and everything in the correct order.

    If one of you doesn't mind, can you explain the +6 and +4? Those numbers don't seem particularly intuitive when I'm trying to add things 3 rows apart.

    I see I will now have to read up on the INDIRECT command.

    Thanks again.

    Dan

  5. #5
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Fill columns with formulas containing non-consecutive numbers

    You want to start with 9, then 12, 15, etc.
    These are increments of 3, i.e. the number in each subsequent row must be 3 more than the last

    So you need to multiply the row number by 3 to arrive at a sequence of 3,6,9,12 etc.

    You want to start in row 1, showing the value 9
    So you need to add 6 to =row()*3 arrive at value 9 for row 1

    The row number of row 2 is 2
    Multiplied by 3 = 6
    Add 6 to arrive at value 12 for row 2

    etc.
    Last edited by teylyn; 12-09-2010 at 05:25 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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