Filling formulas but need to skip cells

[Nick A]

I'd be grateful for help with these problems with Excel 2000.

First: I have a sheet with mixed text/numerical data that occurs 14 cells apart. e.g. B4, B18, B32, B46. I want to create a cell reference in a second sheet for each of these cells but this time have them in consecutive cells e.g. B5=Sheet1!B4, B6=Sheet1!B18. So far, so good. But, instead of doing this manually, can I fill down the formula in the second sheet having it skip 14 cells each time?

Second: I need to do the same as above, but include a Transpose array formula e.g. on sheet 1 AQ4:AQ15 becomes on sheet 2 C5:N5=TRANSPOSE(Sheet1!AQ4:AQ15). The next row on sheet 2 comes from AQ18:AQ29.

Suggestions appreciated.

 

[Joe4]

WELCOME TO THE BOARD!

For the first part.

On sheet 2, we want the value from Sheet1, B4 in cell B5,
and the value from Sheet1, B18 in cell B6,
etc.

Mathematically speaking, we can create the following equations:
5x + y = 4
6x + y = 18

If we solve these equations, we get x=14 and y=-66.

Now we can build a calculation using the INDIRECT function (see Excel help for details) and using the results above to do what you want.

So, on Sheet2, in cell B5, enter this equation and copy down. It should do what you want:

=INDIRECT("Sheet1!B"&(ROW()*14)-66)

 

[Richie(UK)]

Hi Nick,

Similar to the above approach by jmiskey:

Book2
	B	C	D	E
5	1	4
6	2
7	3
Sheet2

HTH

 

[Nick A]

Thanks to both of you. That seems to work fine. Will this also work for the transpose array?

 

[Joe4]

I am not sure. I have never worked with Transpose Arrays before. You probably can, but I am not sure of the exact syntax. My guess is that you would want to incorporate the INDIRECT function in the same manner.

You may have to play around with it for a while and see what works. Hopefully, someone else will see this post and help us out.

 

[polly]

http://www.mrexcel.com/board2/viewtopic.php?t=78855&highlight=#javascript:void(0);

You may be able to use something at this link and adjust for your needs.
Look at my 2nd and 3rd posts there.

EDIT: For your 2nd question