Adding X No. of Rows to Cell References

**IrrepressibleXL** · 04-26-2012, 01:05 PM

Hi,

I am trying to pull values from one worksheet to another in the same workbook. The values required on Sheet1 are 30 rows apart. Is there a way of automatically adding 30 rows to the cell references on Sheet2?

Sheet1 values are in cells A30, A60, A90, A120, A150, A180, A210 etc.

The values from these are required in Sheet2 as follows:
Sheet1 A30 - Sheet2 A1
Sheet1 A60 - Sheet2 A2
Sheet1 A90 - Sheet2 A3
Sheet1 A120 - Sheet2 A4
Sheet1 A150 - Sheet2 A5 etc.

Simply dragging down the formula after adding several entries unfortunately doesn't work.

Any ideas?

**Cutter** · 04-26-2012, 01:14 PM

In Sheet 2 A1 put this:

=INDEX(Sheet1!A:A,ROW()*30) and drag down

**IrrepressibleXL** · 04-26-2012, 01:58 PM

Thanks Cutter, I have applied this and I can see it will work well.

I should have provided the exact cells references rather than general ones as this changes things slightly. I was actually hoping to pull the values as follows:
Sheet1 A33 - Sheet2 B2
Sheet1 A63 - Sheet2 B3
Sheet1 A93 - Sheet2 B4
Sheet1 A123 - Sheet2 B5
Sheet1 A153 - Sheet2 B6 etc.

Would this be a straightforward amendment?

**Cutter** · 04-26-2012, 02:23 PM

Yes, just adjust it.

In B2 you put: =INDEX(Sheet1!A:A,ROW(A1)*30+3) and drag down

**scottyboy218** · 04-26-2012, 02:25 PM

Have you used the indirect formula? It lets you create a formula based on cells/text inputs

For example:

If you're in sheet3, and go into a cell and click on cell A33 on Sheet1, you will see the formula "=Sheet1!A33". Pick any two cells on sheet3 (lets say B2 and C2 for this example). Type Sheet1! in B2, and A33 in C2

in B3, type =indirect(B2&C2). This formula is now saying =indirect(Sheet1!A33), which is the same as your first formula. You can then add another 2 cells for inputs for the second cell, and just subtract. here's an example:

**Cutter** · 04-26-2012, 02:28 PM

The INDIRECT() function is volatile and should not be used if it isn't necessary.

Read more here: http://www.decisionmodels.com/calcsecretsi.htm

**scottyboy218** · 04-26-2012, 02:39 PM

Thank you for that response, you offered absolutely nothing to helping him solve his issue. Indirect is the ideal formula for his situation.

**Cutter** · 04-26-2012, 02:45 PM

What's your problem???
INDIRECT() is not ideal - because it is volatile. Take the time to read the link I provided - especially the line: "Avoid volatile functions wherever possible."

And I think I DID offer SOMETHING to help him solve his issue - See Post #2 and Post #4.

**IrrepressibleXL** · 04-26-2012, 04:41 PM

Great thanks for the new formula Cutter. Thanks for your posts & attachment scottyboy I haven't come across the indirect function before so I will have a play around with that as well.

**Paul** · 04-26-2012, 05:13 PM

Scottyboy,

In the future please be respectful of others' solutions - especially someone who has nearly 600x as many posts as yourself. Cutter's post regarding use of the INDIRECT function was not a personal attack against you or your opinions on the subject matter. It is a fact, and one that is agreed upon by minds far greater than my own in the Excel world. By adding it to the thread, he was simply making the original poster aware. It is up to the OP whether or not to heed that advice.

INDIRECT certainly has its place, and can be very useful, however in this case it is probably not the best option.

Another option, without using formulas could even be:

In Sheet2!B2 type:

.Sheet1!A33

In Sheet2!B3 type:

.Sheet1!A63

Select both cells and use the fill handle to fill down. The next cell will change to 93, then 123, then 153, etc. Finally, select your range of cells and use the Replace dialog to replace the period with an equal sign.

This can be done in a matter of seconds without having to ever know about INDEX or INDIRECT, but it's still up to the owner of the spreadsheet to decide which route to choose.

Have a great day everyone.