Filling formulas but need to skip cells

N

Nick A

New Member
Joined
Mar 10, 2004
Messages
3
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.
 
Sort by date Sort by votes
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)
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Unique Transpose Formula though results skip 3 cells
Replies
3
Views
276
Fluff
Fluff
M
  • Question Question
Excel formula to skip a cell, multiple conditions
2
Replies
11
Views
752
Fluff
Fluff
E
  • Solved
Spilled formula to return the min of a reducing range
Replies
3
Views
386
Fluff
Fluff
gtd526
  • Solved
Modify Conditional Formatting to include Odd and Even rows
Replies
4
Views
131
Peter_SSs
Peter_SSs
R
  • Solved
VBA - Apply Macros to 100 workbooks without needing to open workbooks individually to apply Macros
Replies
5
Views
710
Micron
M

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top