Skipping rows when filling down and right

Hello,

Im working on an inventory sheet for my office and ive run into a complex problem. Its probably only complex due to the amount of data. There are several hundred locations we service and these are in a single column, and then there is the equipment we use that is several hundred columns.

Each location uses 3 rows and the 1st needs the formula to equal 2 rows beneath it. i.e. cell E28 has the formula =E30. I basically need to fill down, but only put that formula into every 3rd row, as the two rows beneath this row need to stay blank, or show number i manually put into either.

The second part of this, is there a way once that is done, to just select those cells, and fill to the right all the way? Again, i do not need to fill the two rows under that one.

I have attached part of the sheet to get an idea of what i need if i failed to explain well.


The green cells are the ones that need the formula, and those will be copied down, and right. the others need to be left alone. I have been sitting here for an hour manually entering in the =XXX for each cell and it would take me a week to do this lol.

Let me know if i failed to explain anything.

If you copy the structure you want to repeat (in the example that would be E28:J30) to the clipboard, next select cells E31:E42 and then paste this structure will be copied down while adjusting the formulas. However also the fix data in row30 will be copied, so you would loose the data in rows 33, 36, 39 and 42.
If you must skip these data rows you could write a macro (with a shortkey) in which you select the first row with formulas, copy it, move 2 rows down and paste it.
You could then just keep pressing the shortkey until you're done.

Depends on how much rows you're talking. Can only be 1,048,576 at most
And you can also put a brick on the shortkey to do it handsfree

Now I come to think of it: may be you can do something with autofilter.
If it's possible the filter out all the rows not having the formulas (may basis on the column with the locations ) you could simply copy teh formulas down. that would skip all the rows which are not visible when filtered.
Or if - like in the example - the first cell in the rows with formulas has the color green, you could filter on that color.

This might be an easy way to do what you want:

Using your example, delete the values in row 42. Select E40:J42 so that you have an empty example to copy. Grab the fill handle (little black square at the bottom right of the selection) and drag down as far as you need. The formulae will be filled in in every 3rd row just like your example. The Green formatting will be filled in at the same time.