Increasing a Referenced Cell by one and Copying that Cell Every 5th Cell

[jmorrow21907]

Hello,

I am attempting to = a cell from another tab with increasing a referenced cell by the cell below it and paste that formula every 5th cell. I have 1,300+ rows that I am manually having to change the cell to (d4, d5, etc) in that referenced tab formula.

For example, my cell T6 says ='Assets'!C7, four cells later I want the cell to say ='Assets'!C8. ( I've tried the drag option and that won't work because I have data in cells between the 4 cell increment. The cells in the middle are just a =- formula and also the same formula linking to the Assets tab, but for the next column.

I've also tried the Offset & Row formula with the results of the cells decreasing by one. I know there is a way to do this, but need some help.

Thank you for all that look and help.

 

[Eric W]

Welcome to the forum.

Try this in T6:

=INDEX(Assets!C:C,INT((ROW()+22)/4))

 

[jmorrow21907]

Thank you so much Eric, I changed the 4 to a 5 and Boom! You have saved me so much time. I can't thank you enough.

Minus forums, what are some resources to get better at more complex formulas in excel?

Thank you again,

J.Morrow

 

[Eric W]

Glad to help!

As far as getting better at formulas, it pretty much depends on your learning style. Some people like watching videos, and there are a lot of videos on YouTube. Some people like books, but since that's not how I did it, I don't have any particular book to recommend. And there are a wealth of Excel sites online which talk about formulas. My particular method was to look at other people's formulas, then deconstruct them until I figured out how they worked. After a while, I started seeing how a piece from that formula could mix with a piece from another formula to get something new. Practice, as always, makes perfect.

hiker95 has compiled an incredible list of online resources:

What is the best way to learn VBA?

He constantly updates it, and I know there's a newer list. However, there's plenty there to get you started! Don't get scared by the sheer size of the list, look for something that looks interesting and start there. Or do a search for "formula" on that page.

Good luck!