Creating an incremental formula whilst using data from a second sheet

[furn355]

Hi guys

I was hoping to get some help with a formula I'm trying to use, now I have the formula right and it works, however when i drag it across my range of cells rather than incrementing as cell d1, d2, d3, d4, d5, d6, d7, d8, it will just repeat d1,d2,d1,d2,d1,d2

The code im currently using looks like this:


cell 1

Please Login or Register  to view this content.

cell 2

Please Login or Register  to view this content.

As mentioned when I drag the sheet across the d7/d8 and f7/f8 just keep repeating rather than incrementing. I have played around with the $ as well but still no luck

[Pete_UK]

Put this in your first cell:

=IF(H$5<=H$1,INDEX('MASI Timesheet'!$D:$D,COLUMNS($A:G)),INDEX('MASI Timesheet'!$F:$F,COLUMNS($A:G)))

then you can copy this across to your heart's content.

Hope this helps.

Pete

[furn355]

Hi Pete

This has worked fantastically! Thank you, if you have a second could you break it in pseudo code for me so I can better understand it

thanks in advance

[Pete_UK]

The important parameter is the COLUMNS($A:G) term - this initially returns the value 7 (i.e. the number of columns between A and G), but when the formula is copied across the term changes to COLUMNS($A:H), COLUMNS($A:I) and so on, which return the values 8 and 9 respectively. These are the cells within column D and column F that you want to include within your formula, and the INDEX functions thus return the equivalent of D7/F7, D8/F8, D9/F9 etc. in successive columns.

Hope this helps.

Pete

P.S. If that takes care of your original question, please take a moment to 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

[furn355]

Thanks again Pete, that's been very helpful for my understanding. Rep added.