Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Dec 2001
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Row function plus a number? (Excel XP)

    Is there a way to set up a formula with the ROW function that then adds a specific number of rows onto the row that is stated to find a value in a row that many rows away?

    In other words - in the sheet I've been presented with - We know that X value in a certain row is a SKU number - I want to add 14 rows on to the current row to get another value in column S - is this possible?

    I hope that's clear enough. Thanks folks!

    --*Rob

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Row function plus a number? (Excel XP)

    Hi Rob,
    It sounds to me like you want the OFFSET function - e.g.
    =OFFSET(C4,10,0)
    will give you the contents of the cell 10 rows below C4.
    Does that help?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Lounger
    Join Date
    Dec 2001
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Row function plus a number? (Excel XP)

    Somewhat - but what I'm looking to do is use this in conjunction with the ROW function - can this be done?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Row function plus a number? (Excel XP)

    Does =INDIRECT("S"&(ROW()+14)) do what you want?

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Row function plus a number? (Excel XP)

    It can, but it doesn't make a lot of sense to me. Where are you planning to use this function? If it will be in the same row as the SKU number then you don't need ROW() at all - you simply use OFFSET with 14 rows and the relevant number of columns to get to S from whichever column you're in. Hans solution may be simpler as there's no need to calculate the column offset.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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