I just need the formula for Excel to pull every (nth) cell to another sheet so that data doesn't have to be manually entered twice. Thanks. Example: Sheet A Sheet B A1 A4 A2 A8 A3 A12 A4 A16 Edit: The spacing doesn't work, but you get the idea, I hope.
That works for one cell, but when you drag that box to copy the cells, Excel counts by 1's, not 4's. I even tried manually entering the first 3 cells (4,8,12) and then copying the box, but it still counts by 1's. (4,8,12,7,8,9,10) I need to know how to drag and make it know I only want every 4th cell.
Scotty, I don't have a direct solution but I can provide you with something that will get you what you are looking for if you don't mind doing a bit of the work by hand: You data is on Sheet1, Column A: A1, A2, A2, A4, ... Go to Sheet 2, A1 and type: =OFFSET(Sheet2!$A$4,(COLUMN()-1)*4,0) Sheet2!A1 = Sheet1!A4 If you drag Sheet2!A1 across, you should pick up every fourth number: Sheet2!B!= Sheet1!A8, Sheet2!C1 = Sheet1!A12, etc. Now, if you want to represent this in a column, it will required some manupulation. I am sure that a macro or other formula can be develop but you can copy: Row 1 on Sheet2, Paste special (values) on Row 2 Now copy Row 2, Sheet 2 go to Sheet 3, click and A1, click paste special and click transpose. What you should have is Sheet 1 Sheet 3 A1 A4 A2 A8 A3 A12 A4 A16 etc. Hope this helps. CSGM
maybe an "If and Statement" and have a code......like "If A1 = Yes, 1000, If A1= No, 0" yes, no maybe.......i don't know.......without seeing your file i can't visualize what you want..
=INDEX(Sheet2!$A$1:Sheet2!$A$100,ROWS($1:4)*3-3+1) should do it if I understand the problem correctly. Just alter $A$100 range as appropriate.
I tried to use the index function but I could not get it to work. But I may have been screwing up something! Edit: I think this should worK: =INDEX(Sheet2!$A$1:Sheet2!$A$100,ROWS($1:4)*4-12)
Made a mistake, it should be: =INDEX(Sheet2!$A$1:Sheet2!$A$100,ROWS($1:4)*4-4+1), assuming you want rows 1, 5, 9... etc.
LOL, try this, it's correct if you want the 4th row and every 4th after that. Previous was in error according to your problem description, took 1st row and every 4th row after that. =INDEX(Sheet2!$A$1:Sheet2!$A$100,ROWS($1:1)*4-4+4)
:up: I think this should address what Scotty was looking for. I was able to manipulate your previous one but I like this one better, cleaner. I think I know where I was going wrong in my earlier attempts at the index.
Thank you for the help, this does what I want in terms of skipping rows. I'm trying to reverse engineer it so that I actually understand what it is doing. So let's say I wanted to start with cell D13 and pick every 11th cell after that, (D24, D35.....), what would that line look like? I owe ya.
General formula (for the first cell) is: =INDEX(Startrow:Endrow,ROWS($1:1)*Skipcount-Skipcount+Skipcount) So, if you want to start in row D13 of Sheet2 and pick every 11th row through row 100 the formula would be: =INDEX(Sheet2!$D$13:Sheet2!$D$100,ROWS($1:1)*11-11+11) When you copy the formula down you'll notice that ROWS($1:1) is modified by Excel for proper reference.
