Formula referencing another sheet help

[Drayde]

So I have two sheets.. as an example, each line in a column looks like this:

=IF(MMDDYYYY!O4="","",MMDDYYYY!G2)
=IF(MMDDYYYY!O11="","",MMDDYYYY!G2)
=IF(MMDDYYYY!O18="","",MMDDYYYY!G2)


As you can see, O is increasing by 7 every time. I don't know how to design it so I can drag this formula down multiple columns, and have it recognize that O is increasing by 7 every time.

G2 will also increase by a static amount, but only after 7 lines. It will then jump fairly significantly (It's jumping to the same location on page 2, then after 7 lines it will jump to the same location on page 3, etc.)

Again, I'd like to be able to drag this down without inputting it for every line, as I'm going to have to do this a LOT.

[Jonmo1]

Try

=IF(INDEX(MMDDYYYY!O:O,(ROWS($1:1)-1)*7+4)="","",INDEX(MMDDYYYY!G:G,CEILING(ROWS($1:1)/7,1)+1))

[bebo021999]

I am so confused with:

so I can drag this formula down multiple columns, and have it recognize that O is increasing by 7 every time

did you mean " drag down multiple rows"? look like this:
A1=IF(MMDDYYYY!O4="","",MMDDYYYY!G2)
A2=IF(MMDDYYYY!O11="","",MMDDYYYY!G2)
A3=IF(MMDDYYYY!O18="","",MMDDYYYY!G2)
...
A7=IF(MMDDYYYY!O46="","",MMDDYYYY!G2)
A8=IF(MMDDYYYY!O53="","",MMDDYYYY!G3)
...
Is that true?

[Ron Coderre]

Try this example
This regular formula, copied down, sequentially references cells 7 rows apart on the MMDDYYYY sheet

Please Login or Register  to view this content.

Use that same approach to manipulate the G2 reference.

Is that something you can work with?

[Drayde]

Jonmo1 - I'll try that, perhaps. I might have to change it to deal with the G.

Bebo21999- Close, except A8=IF(MMDDYYYY!O53="","",MMDDYYYY!G51).

After 7 more it would be

A15=(IF(MMDDYY!O102="","",MMDDYYYY!G100)

[Drayde]

I'll look guys - thanks. Still rather new to excel - better than I used to be, but still rather new.

[AlKey]

Or this

=IF(INDIRECT("MMDDYYYY!O"&ROW(4:4)*7-24)="","",MMDDYYYY!$G$2)

[Drayde]

All the suggestions so far seem to be relating to that O column very well, thank you! So.. I'm not understanding the math part of these formulas - how do I get it to deal with G2, presuming a 49 increase after 7 rows? So G2 for 7 rows, then G51, then G100

[Jonmo1]

Try
=IF(INDEX(MMDDYYYY!O:O,(ROWS($1:1)-1)*7+4)="","",INDEX(MMDDYYYY!G:G,(CEILING(ROWS($1:1)/7,1)-1)*49+2))

[Drayde]

Jonmo1's worked perfectly, thank you!

I'm going to mark this as resolved, but Jonmo1 - if I could trouble you, could you tell me what's going on with that? I'm probably going to have to do similar things for a lot of this, and I would like to know what's happening in that formula so I can understand it and adjust it myself.

[Jonmo1]

You're welcome.

They key component in there is the ROWS function
Rows returns the count of the number of rows in a given reference.
ROWS($1:1) = 1 (there is 1 row in the reference)
As it's filled down it changes to
ROWS($1:2) = 2
ROWS($1:3) = 3
etc.

Put that by itself in a cell and drag it down to observe the results.

After that it's fairly simple math.
Devide by 7 (every 7 rows)
Celing rounds that result UP to the nearest 1
Subtract 1 to begin at 0
Multiply by 49 and add 2 to begin in row 2

Hope that helps.

[bebo021999]

Another option :
In A1:
=IF(INDIRECT("MMDDYYYY!O"&(ROW(1:1)-1)*7+4)="","",INDIRECT("MMDDYYYY!G"&2+INT((ROW(1:1)-1)/7)*49))