Using OFFSET & ROWS to skip a varying number of rows.

[Throwaway]

First time poster so bear with me:

In Sheet2, I have data in column E that begins in row 8 and continues down every other row (E10,E12,E14 ect.) that I am trying to display in Sheet1. I understand that using OFFSET with the ROWS formula can retrieve this data in a manner that can be dragged down (effectively skipping every other row) in Sheet1, however every couple of rows (randomly and not following a pattern) the data will skip 4 rows. This happens multiple times so that the formula to skip a row becomes ineffective at correctly listing all of the data in Sheet1.

To skip every other row (effective until the first 4 row skip occurs) I entered the following formula into cell C19 and dragged down:
=OFFSET(Sheet2!E$8,ROWS(C$19:C19)*2-2,0)

To return the first 4 row skip (Occurring 25 rows after the first OFFSET in Sheet1 I used an IF & ISBLANK to skip 4 rows:
=IF(ISBLANK(OFFSET(Sheet2!E$8,ROWS(C$19:C44)*2-2,0)),OFFSET(Sheet2!E$8,ROWS(C$19:C44)*2-2,0),OFFSET(Sheet2!E$8,ROWS(C$19:C44)*2+1,0))

However this is only effective until the next 4 row skip, in which I would need to add +4 instead of +1 in the "value if false" section from the previous equation. Manually entering these each skip would be ineffective considering the number of entries.

If there is an effective way to do this using macros or another formula, I am welcome to those suggestions as well.

Thanks

 

[Rick Rothstein]

Are the cells that are being "skipped" over blank or do they contain something (data or formulas)? If they contain something, what?

 

[Throwaway]

The cells being skipped are blank. I however cannot just delete the rows because they contain data in other columns of the worksheet that need to be kept.

 

[Rick Rothstein]

Put this array-entered** formula in the first cell you want the output go to on Sheet1 and then copy it down...

=IFERROR(INDEX(Sheet2!E:E,SMALL(IF(LEN(Sheet2!E$8:E$100),ROW(Sheet2!E$8:E$100),""),ROWS(A$1:A1))),"")

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself.

 

[Throwaway]

Thanks for your help so far,

Inputting the IFERROR formula referencing cells C$19:C19 works up until row 100 (I assume because of the E$100 from the ROW formula), however my data extends past row 100 (to row 188 specifically in this case, possibly up to row 1000 in other worksheet's cases), however when changing the range from E$100 to E$1000 and hitting CTRL+SHIFT+ENTER the formula no longer works and I receive a blank cell as a result.

 

[Rick Rothstein]

however when changing the range from E$100 to E$1000 and hitting CTRL+SHIFT+ENTER the formula no longer works

Did you change both of them or only one of them?

 

[Throwaway]

Thanks so much for your help! I changed only the ROW function range instead of both.

 

[Throwaway]

However, can I insert an if function around the array formula so that the cell returned has a criteria?

 

[Rick Rothstein]

However, can I insert an if function around the array formula so that the cell returned has a criteria?

I am not sure what you are asking here... what do you mean by "the cell has a criteria"?

 

[Throwaway]

Sorry I was basically asking if you can add an if function to the outside of your equation without disrupting the integrity of the formula since I have limited experience with using CTRL+SHIFT+ENTER equations. To better show you what specifically I was asking (I have since solved my problem) I'll try and use specific examples. Instead of Column E, I'm now working in column H, where cells either are "pfslstus", "pfsl1bsd" or "pfslcmps". If the cell in Sheet1 row H displays "pfslstus" I want to show "Stu", if it displays "pfsl1bsd", the formula should return "1br" and if it displays "pfslcmps" the formula should return "2br".

This is the formula I ended up using to get correct results, previously I was not hitting CTRL+SHIFT+ENTER:
=IF(IFERROR(INDEX(Sheet1!H:H,SMALL(IF(LEN(Sheet1!H$8:H$1000),ROW(Sheet1!H$8:H$1000),""),ROWS(F$19:F19))),"")="pfslstus","Stu",IF(IFERROR(INDEX(Sheet1!H:H,SMALL(IF(LEN(Sheet1!H$8:H$1000),ROW(Sheet1!H$8:H$1000),""),ROWS(F$19:F19))),"")="pfsl1bsd","1br","2br"))

Thanks again for your help! Can I rate your profile or send you some type of Kudos through this website?