+ Reply to Thread
Results 1 to 10 of 10

Indicrect reference formula but data needs to skip rows.

  1. #1
    Registered User
    Join Date
    12-21-2015
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5

    Indicrect reference formula but data needs to skip rows.

    I'm using two sheet and the second sheet is just a single column with multiple rows of a formula that references the first sheet. I can't figure out how to keep the single rows on the second sheet, but drag down the formulas using data on sheet one that doesn't follow that same pattern.

    Sheet 1
    Row 3 = data
    Row 4 = n/a
    Row 5 = n/a
    ...

    Sheet 2
    Row 1 = Formula using Sheet1!Row 3
    Row 2 = Formula using Sheet1!Row 6
    ...

    Here's the formula I'm using on Sheet 1, =IF(Sheet1!F3>1,(Sheet1!F3-1)*Sheet1!H3, 0), I'd like to be able copy/insert on sheet 1 when I require more data fields, but then just simply copy down on sheet 2 to grab the applicable data.

    Many thanks!

  2. #2
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Indicrect reference formula but data needs to skip rows.

    Try

    =IF(INDEX(Sheet1!F:F,ROWS(A$1:A1)*3)>1,(INDEX(Sheet1!F:F,ROWS(A$1:A1)*3)-1)*INDEX(Sheet1!H:H,ROWS(A$1:A1)*3),0)

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

    Re: Indicrect reference formula but data needs to skip rows.

    You can use this in the first cell in Sheet2:

    =IF(INDIRECT("'Sheet1'!F"&ROWS($1:1)*3)>1,(INDIRECT("'Sheet1'!F"&ROWS($1:1)*3)-1)*INDIRECT("'Sheet1'!H"&ROWS($1:1)*3),0)

    then when you copy down the row will increment by 3 for each row in Sheet2.

    Hope this helps.

    Pete
    Last edited by Pete_UK; 12-21-2015 at 12:48 PM. Reason: fixed brackets

  4. #4
    Registered User
    Join Date
    12-21-2015
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5

    Re: Indicrect reference formula but data needs to skip rows.

    I get a paranthetical error on this, I tried to fix it but then I get a #REF! error. I'll have to look at it some more, thanks!

  5. #5
    Registered User
    Join Date
    12-21-2015
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5

    Re: Indicrect reference formula but data needs to skip rows.

    This works, thanks!

  6. #6
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Indicrect reference formula but data needs to skip rows.

    Quote Originally Posted by mjrtoo View Post
    This works, thanks!
    Was that in response to my suggestion, or did you fix Pete's?

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

    Re: Indicrect reference formula but data needs to skip rows.

    I've corrected mine in Post #3.

    Pete

  8. #8
    Registered User
    Join Date
    12-21-2015
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5

    Re: Indicrect reference formula but data needs to skip rows.

    Quote Originally Posted by jason.b75 View Post
    Try

    =IF(INDEX(Sheet1!F:F,ROWS(A$1:A1)*3)>1,(INDEX(Sheet1!F:F,ROWS(A$1:A1)*3)-1)*INDEX(Sheet1!H:H,ROWS(A$1:A1)*3),0)
    This works, thanks!

  9. #9
    Registered User
    Join Date
    12-21-2015
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5

    Re: Indicrect reference formula but data needs to skip rows.

    Quote Originally Posted by Pete_UK View Post
    I've corrected mine in Post #3.

    Pete
    This works too! Thanks all!

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

    Re: Indicrect reference formula but data needs to skip rows.

    Glad to hear that you now have two solutions.

    If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

    Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete

+ 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] Formula to Copy/Paste data based on value of another cell. SKIP BLANK ROWS.
    By mamero in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-14-2015, 04:46 PM
  2. [SOLVED] Drag down formula to skip 2 reference rows.
    By StudioLETA in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-09-2015, 02:37 PM
  3. Need formula to copy data and skip rows automatically.
    By kev128 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-17-2014, 08:04 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. Replies: 2
    Last Post: 05-14-2014, 02:02 PM
  6. How to skip a cell reference while dragging down a Hlookup formula
    By sbham in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-09-2013, 11:54 PM
  7. How can i get a IF formula to skip to the next reference box
    By Craig Turley - Emerson in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-16-2006, 10:55 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