+ Reply to Thread
Results 1 to 29 of 29

How to properly offset a relative formula to skip rows

  1. #1
    Registered User
    Join Date
    04-08-2019
    Location
    Belfast, Northern Ireland
    MS-Off Ver
    Excel 2010
    Posts
    37

    How to properly offset a relative formula to skip rows

    I am trying to copy a relative formula with a pattern increase.
    I need the formula to copy from sheet1 (RRP Clean) cell C100 into sheet2 (POS) cell A24. Then I need to copy the sheet 2 relative formula down 14 rows whilst the relative formula only increasing by 1 cell i.e. Sheet 2 cell A38 copying in Sheet 1 cell C101 (instead of cell C114) and so on for A52,etc.

    Does anyone know the right way to do this. I have tried using OFFSET but can't seem to make it work.

    Thanks in advance and apologies for the poor explanantion. Hopefully the photos help explain.



    3.PNGExcel Pattern 2.PNG
    Attached Images Attached Images
    Last edited by mikeymeteora; 04-09-2019 at 06:19 AM.

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

    Re: How to properly offset a relative formula to skip rows

    Rather than attaching pictures of a worksheet, which can't be edited, it would be better if you attached a sample Excel workbook.

    To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Don't try to use the Paperclip icon, as it doesn't work on this forum.

    Hope this helps.

    Pete

  3. #3
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Re: How to properly offset a relative formula to skip rows

    see attached.
    Sheet2 Cell C101 has a formula. Copy it down. You can create something similar to suit your needs.

    HTML Code: 
    Attached Files Attached Files
    1. Click on the * Add Reputation if you think this helped you
    2. Mark your thread as SOLVED when question is resolved

    Modytrane

  4. #4
    Registered User
    Join Date
    04-08-2019
    Location
    Belfast, Northern Ireland
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: How to properly offset a relative formula to skip rows

    Thank you. The only issue with this is its the reverse solution to what I need. how do i convert this so sheet1 has subsequent figures but sheet2 which contains the formula is the one which skips 14 rows. Do I use a negative equation?

  5. #5
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Re: How to properly offset a relative formula to skip rows

    It's confusing.
    where is your data [source]? and where do you want the result [destination]?.
    As Pete suggested, please attach a sample file with data, enter expected result [manually] and explain clearly the logic and how you want result to be figured.

  6. #6
    Registered User
    Join Date
    04-08-2019
    Location
    Belfast, Northern Ireland
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: How to properly offset a relative formula to skip rows

    I've uploaded a sample spreadsheet to hopefully show what I mean.
    On Sheet1 I have a grid of figures.
    On Sheet2 I have a recurring pattern of text with figures every 14 rows.
    I need away of copying the 14 rows of text vertically whilst importing the next figure down from sheet1 into the number spaces on sheet2.

    Thank you.
    Attached Files Attached Files

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,423

    Re: How to properly offset a relative formula to skip rows

    Where is the text coming from?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  8. #8
    Registered User
    Join Date
    04-08-2019
    Location
    Belfast, Northern Ireland
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: How to properly offset a relative formula to skip rows

    Quote Originally Posted by AliGW View Post
    Where is the text coming from?
    The text will be some manual entry and some imported from other parts of Sheet1 but can be ignored for this formula. Only put it in to show that the spaces between the figures can't be forced blank.

  9. #9
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,738

    Re: How to properly offset a relative formula to skip rows

    Something like:

    A1 on Sheet2:
    Please Login or Register  to view this content.
    B1 on Sheet2:
    Please Login or Register  to view this content.
    Happy with my answer * Add Reputation.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.

  10. #10
    Registered User
    Join Date
    04-08-2019
    Location
    Belfast, Northern Ireland
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: How to properly offset a relative formula to skip rows

    Thanks. I've tried importing this in but can't seem to get it to work, do I need to alter that formula once its been copied in?

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,423

    Re: How to properly offset a relative formula to skip rows

    Define "can't seem to get it to work". What's going wrong?

  12. #12
    Registered User
    Join Date
    04-08-2019
    Location
    Belfast, Northern Ireland
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: How to properly offset a relative formula to skip rows

    Quote Originally Posted by AliGW View Post
    Define "can't seem to get it to work". What's going wrong?
    It's not executing a formula it's just putting the formula as text. into the cell. I've tried altering parts of the formula to correlate to specific cells, etc. but I'm really unsure as to what I'm looking at.

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,423

    Re: How to properly offset a relative formula to skip rows

    Remove the formula - set the column to general (not text) format - try entering the formula again.

  14. #14
    Registered User
    Join Date
    04-08-2019
    Location
    Belfast, Northern Ireland
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: How to properly offset a relative formula to skip rows

    Have tried resetting the columns to general but still no joy. Attached file below
    Attached Files Attached Files

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,423

    Re: How to properly offset a relative formula to skip rows

    Go to the Formulas ribbon and DESELECT show formulas!

  16. #16
    Registered User
    Join Date
    04-08-2019
    Location
    Belfast, Northern Ireland
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: How to properly offset a relative formula to skip rows

    Quote Originally Posted by AliGW View Post
    Go to the Formulas ribbon and DESELECT show formulas!
    I had a feeling it would be something insanely simple like that. Thank you

  17. #17
    Registered User
    Join Date
    04-08-2019
    Location
    Belfast, Northern Ireland
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: How to properly offset a relative formula to skip rows

    Quote Originally Posted by KOKOSEK View Post
    Something like:

    A1 on Sheet2:
    Please Login or Register  to view this content.
    B1 on Sheet2:
    Please Login or Register  to view this content.
    Perfect, you are a lifesaver.

  18. #18
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,423

    Re: How to properly offset a relative formula to skip rows

    By the way, I really don't believe you can do what you want with a formula. I would suggest that you will need a VBA solution.

  19. #19
    Registered User
    Join Date
    04-08-2019
    Location
    Belfast, Northern Ireland
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: How to properly offset a relative formula to skip rows

    Oh really? What is a VBA and how difficult would it be to learn?
    Last edited by AliGW; 04-08-2019 at 12:33 PM.

  20. #20
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,423

    Re: How to properly offset a relative formula to skip rows

    Apparently the formulae are working for you (see post #17), but I fail to see how with the layout you have. Surely copying down overwrites your text rows?

  21. #21
    Registered User
    Join Date
    04-08-2019
    Location
    Belfast, Northern Ireland
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: How to properly offset a relative formula to skip rows

    It works as long as I copy the whole 14 rows at the same time. My only struggle left it seems is working out how to make the formula start at the correct cell.

  22. #22
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,423

    Re: How to properly offset a relative formula to skip rows

    Which cell do you want it to start at?

    Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

    For normal conversational replies, try using the QUICK REPLY box below.

  23. #23
    Registered User
    Join Date
    04-08-2019
    Location
    Belfast, Northern Ireland
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: How to properly offset a relative formula to skip rows

    I'll need to be able to change the starting point for different sections of data but for talk sake. What if I wanted the first piece of imported data to be from Sheet1!C112 ?

  24. #24
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,738

    Re: How to properly offset a relative formula to skip rows

    Please Login or Register  to view this content.
    C98 should be fine.
    Last edited by KOKOSEK; 04-08-2019 at 02:13 PM.

  25. #25
    Registered User
    Join Date
    04-08-2019
    Location
    Belfast, Northern Ireland
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: How to properly offset a relative formula to skip rows

    I have tried inputting this formula but am only get some cells importing correctly. I've attached the file again to show. (Edit: Attached wrong file, reuploaded)
    Attached Files Attached Files
    Last edited by mikeymeteora; 04-09-2019 at 05:49 AM.

  26. #26
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,738

    Re: How to properly offset a relative formula to skip rows

    First border You have got in row 14 then after 13 (row27) and after 13 (row40).
    And also you did not blocked start range address like:

    Please Login or Register  to view this content.
    C$111

    I did not test this C98, it looks like should be C111.

    EDIT: file attached.
    Attached Files Attached Files
    Last edited by KOKOSEK; 04-09-2019 at 05:51 AM.

  27. #27
    Registered User
    Join Date
    04-08-2019
    Location
    Belfast, Northern Ireland
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: How to properly offset a relative formula to skip rows

    Sorry, I realised I had messed up the spacing there after I posted. This is almost perfect now. The spacing and copying is working perfectly the only remaining issue is the first box that the formula is entered on is showing blank and then all subsequent 14 rows is correctly importing what I need.
    Attached Files Attached Files

  28. #28
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,738

    Re: How to properly offset a relative formula to skip rows

    As First Box do you mean cell B1?
    Put into B1:

    Please Login or Register  to view this content.
    all bordered cell should be filled correctly.
    Last edited by KOKOSEK; 04-09-2019 at 06:16 AM.

  29. #29
    Registered User
    Join Date
    04-08-2019
    Location
    Belfast, Northern Ireland
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: How to properly offset a relative formula to skip rows

    I have got it to start working, I again had the wrong spacing. Thank you for all your help.

+ 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] Relative formula not updating properly
    By Coronos in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-07-2017, 09:56 PM
  2. Replies: 3
    Last Post: 12-12-2014, 02:42 PM
  3. Replies: 1
    Last Post: 10-06-2014, 02:13 PM
  4. How to skip rows when referencing another workbook that doesn't skip rows?
    By Julian2501 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-06-2014, 04:55 PM
  5. OFFSET Function to Skip Rows
    By wvualum in forum Excel General
    Replies: 3
    Last Post: 06-20-2012, 01:08 PM
  6. Skip hidden rows with offset
    By ainnocent1983 in forum Excel General
    Replies: 2
    Last Post: 03-08-2010, 11:57 AM
  7. Relative References Not Updating Properly
    By pbmax626 in forum Excel General
    Replies: 1
    Last Post: 11-21-2008, 03:20 AM

Tags for this Thread

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