+ Reply to Thread
Results 1 to 9 of 9

Return cell value 30 cells after reference cell

  1. #1
    Registered User
    Join Date
    12-04-2013
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    8

    Return cell value 30 cells after reference cell

    Hi I want to find the cells 30 past the reference cell and the corresponding value:

    ie =Sheet1!D312 to =Sheet1!D342 (=Sheet1!D(312+30))

    or

    =Sheet1!D312 to =Sheet1!E312

    is there a way to automate this without having to manually edit each formula?

  2. #2
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,639

    Re: Return cell value 30 cells after reference cell

    May be this.....

    Please Login or Register  to view this content.
    In this case D312 is the referenced cell and the value is returned from the cell D342.
    Is this what you want?
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  3. #3
    Registered User
    Join Date
    12-04-2013
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Return cell value 30 cells after reference cell

    That is PERFECT!! Thank you so much!!!

  4. #4
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,639

    Re: Return cell value 30 cells after reference cell

    Glad to help you. Thanks for the feedback.

  5. #5
    Registered User
    Join Date
    12-04-2013
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Return cell value 30 cells after reference cell

    Just one more question, how would I refer to the formula in the cell? ie

    lets say my cell shows :

    19/03/1999 the formula being =Sheet1!D312

    How would I use =offset in this situation? If I do =offset(F4,-1,0) then it will use the current sheet but I want it to refer to the reference of F4? Does that make sense?

    So without having to =offset(Sheet1!D312,-1,0) each time manually?

  6. #6
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,639

    Re: Return cell value 30 cells after reference cell

    I am not clear, what do you want to do exactly?
    The formula =offset(F4,-1,0) will return the value of cell F3. In the formula F4 is the referenced cell.

  7. #7
    Registered User
    Join Date
    12-04-2013
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Return cell value 30 cells after reference cell

    Capture.JPG

    I want to automate that.

  8. #8
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,639

    Re: Return cell value 30 cells after reference cell

    Better if you upload a workbook rather than an image. Highlight the cell which have formulas and the expected values in them with an explanation about how do you expect those values in them.
    Because in the image I don't think that your referenced cell are in a particular or set order.

  9. #9
    Registered User
    Join Date
    12-04-2013
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Return cell value 30 cells after reference cell

    TLS data.xlsx
    I want to be able to automate the formula so that I don't have to manually type in =offset(Sheet1!D720,-1,0)

    I would like to be able to drag the formula down to complete for the rest of the data set as well.

    Please see sheet 5

    Thanks in advance
    Attached Files Attached Files

+ 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. Replies: 7
    Last Post: 05-17-2014, 06:28 AM
  2. Replies: 0
    Last Post: 05-07-2012, 05:35 PM
  3. Replies: 2
    Last Post: 05-07-2012, 12:20 PM
  4. [SOLVED] UDF to return string of values of all rows with cell matching reference cell
    By oemgacaesar in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-20-2012, 12:43 PM
  5. VLOOKUP to return Cell reference rather than Cell Contents.
    By Wozza147 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-22-2009, 07:49 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