CODEWITHSUNDEEP
vbaexceloffset
bammerr
bammerr

Reputation: 43

Copy data from another sheet with 4 rows between each entry

I am working with three excel sheets within the same workbook, Data, Calculations, and Results. As you will see, the entries in column A are the focus.

Using the formula Data!A# (row number), those entries from the A column of Data are copied onto column A of the Calculate page. Between each entry from Data, there are three rows of calculations. On the last page (Results), I am looking for a formula/VBA code that will allow me to grab column A from Calculate without the three rows in between them.

Currently, I am experimenting with an offset function: OFFSET(Calculate!$A$2,(ROW()-1)*2,0). When I copy the forumla down, zeroes appear in some rows in place of the actual values. (Note A1 of Results is =Calculate!A2.)

How can I copy the entries from the Calculate sheet without a function returning zeroes as placeholders?

Data Sheet

Data Sheet

Calculations Sheet

Calculations Sheet

Results Sheet

Results Sheet

Upvotes: 1

Views: 38

Answers (1)

DavidP
DavidP

Reputation: 623

You can accomplish this using the indirect function:

=INDIRECT("Calculate!$A$"&(ROW()-1)*4+1)

With that formula in A1:A4 of the results sheet, you should get the values you want from A1, A5, A9, A13 on the Calculate sheet.

Upvotes: 1

Related Questions