Excel Repeat Rows 2

[mlbbud]

I'm trying to repeat every 3rd row from another sheet.
The sheet that I want every 3rd row is called "Blue". The sheet that I'm trying to do this in is called "Test". So in the Test sheet I enter the command 'Blue '!B14. The next row I entered 'Blue '!B17. Now I select both cells and place my mouse in the lower right-hand corner and drag it down 500 rows and I was hoping that it would grab every 3rd row i.e.
'Blue '!B20
'Blue '!B23
'Blue '!B26
'Blue '!B29
and so on, but it's not.

Can someone help me write a formula that would do this?

Thanks

 

[SkipVought]

Hi,

Make a helper column that has the starting row nbr and then a formula that adds 3.

use the INDIRECT fucntion to return your data. Assuming that the helper column is in Column A...
[tt]
B2: =indirect("'Blue '!B"&A2)

[/tt]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Loomah]

i'd be a little concerned that you appear to have a space in your sheet name but this formula should work

=INDIRECT("'blue '!A"&ROW()*3)


;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[xlhelp]

or

=OFFSET('Blue '!B14,(ROW(A2)-2)*2,0,1,1)

assuming formula is in column A starting at A2

Canadian eh! Check out the new social forum Tek-Tips in Canada.
With the state of the world today, monkeys should get grossly insulted when humans claim to be their decendents

 

[mlbbud]

I may not explained myself very well. I can't get your formulas to work. I haven't tried the helped column yet, because I need more info on how to build the column.
On the "Blue" sheet at B14 going down this is what it looks like:
Bill
Bill
Bill
Jim
Jim
Jim
Kim
Kim
Kim
and so on...
On the "Test" sheet in B14 I only need the first name of the three which would look like this:
Bill
Jim
Kim
and so on...
I have to go down over 500 rows that is why I wanted to grab the lower right-hand corner of B14 and drag it down to use the repeat command.

 

[SkipVought]

Use the PivotTable Wizard to generate a list of unigue names.

Or use the Advanced Filter - Copy to another location - UNIQUE values.

Or use MS Query via Data > Get External Data > New database query to do a Select DISTINCT ...

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[sidetech]

write the names in the column like you want them.
either insert a column next to it or if empty use that column.
now on the empty colum merge the cells starting next to the first name down 2 rows past the last name.
highlight merged cell and more over to the column where the names are.
grap the corner and pull it down.

 

[mlbbud]

Thank you for the help. I combine answers for the helper column and the Offset and wrote a formula using the Offset command. On the "Test" page I wrote this formula =OFFSET('Blue'!B$14,$A14,0) and also in column "A" was the helper column starting at A14 looked like this:
3
6
9
12
so on

Thanks again for the help.