+ Reply to Thread
Results 1 to 10 of 10

Do I use the offset function here?

  1. #1
    Registered User
    Join Date
    04-05-2013
    Location
    Scottsdale, Arizona
    MS-Off Ver
    Excel 2007
    Posts
    15

    Do I use the offset function here?

    This seems like I should be able to use the offset function on this case but I can't seem to get it to do what I need.

    In the sample workbook, each row represents 1 week. I have Class Names listed in column C showing the class that will be starting in a given week. In column D the number of weeks until the graduation week. I am trying to add a formula to column E which will display the Class Name that will graduate that week based on the length of the class in column D.

    Thank you for any guidance you can provide.
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,589

    Re: Do I use the offset function here?

    I know there is a better way than this, but it works.

    Use a helper column (I used F, you can hide it is tou need to), put this into F4 and copy down...
    =IF(C4="","",VLOOKUP("week "&MID(B4,6,2)+D4-1,$B$4:$B$11,1,0))

    Then in E4, copied down…
    =IFERROR(INDEX($C$4:$C$11,MATCH(B4,$F$4:$F$11,0)),"")
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Do I use the offset function here?

    or you could use =IF(D4="","",OFFSET(E4,D4-1,,,)) in c4 and drag down
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST

    More we learn about excel, more it shows us, how less we know about it.

    for chemistry
    https://www.youtube.com/c/chemistrybyshivaansh

  4. #4
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,231

    Re: Do I use the offset function here?

    Alternatively:
    Attached Files Attached Files
    Ben Van Johnson

  5. #5
    Registered User
    Join Date
    04-05-2013
    Location
    Scottsdale, Arizona
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Do I use the offset function here?

    Thanks Ford-

    It is working nicely in my sample. I will pull it over to my working project and tweak it to fit. Appreciate the assist.

    Chris

  6. #6
    Registered User
    Join Date
    04-05-2013
    Location
    Scottsdale, Arizona
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Do I use the offset function here?

    Thanks Hemesh-

    I need to "push" the info over to column E from Column C. In my actual project the names in column C are derived from some other formula and I can't pull them from column E using Offset.

  7. #7
    Registered User
    Join Date
    04-05-2013
    Location
    Scottsdale, Arizona
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Do I use the offset function here?

    Thanks ProtonLeah-

    Good reference for organizing the overall class list and scheduling the release dates.

    Thanks.

  8. #8
    Registered User
    Join Date
    04-05-2013
    Location
    Scottsdale, Arizona
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Do I use the offset function here?

    Hope you don't mind if I leave it open for a bit longer to see if others have additional solutions.

  9. #9
    Registered User
    Join Date
    04-05-2013
    Location
    Scottsdale, Arizona
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Do I use the offset function here?

    Ford-

    Thanks again for the solution. I was able to fit the Index/Match combo portion of your solution to fit my project needs perfectly. Works like a charm!


  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,589

    Re: Do I use the offset function here?

    Happy to help and thanks for the feedback

+ 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. [SOLVED] Using Offset function as the array in the PercentRank function is giving wrong result
    By Bobneil in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 08-06-2013, 09:29 PM
  2. Replies: 0
    Last Post: 01-19-2013, 01:35 PM
  3. [SOLVED] Repeat Cut & Paste function with Range.offset function VBA
    By kitcargray in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-06-2012, 12:17 PM
  4. function similar to offset to offset the offset
    By Jerseynjphillypa in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-03-2012, 03:07 PM
  5. Offset function with nested match function not finding host ss.
    By MKunert in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-21-2006, 06:50 PM

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