Help with INDIRECT

[GoBucks]

I have a column in one worksheet (Col A) with a list of names:

Col A
Jones
Smith
Williams
James

In another worksheet I am looking for a formula that will allow me to list
the first name twice (Jones) and then go the next name in the list (Smith)
and list that twice; then list Williams twice, etc.

Column A
Jones
Jones
Smith
Smith
Williams
Williams
James
James

I am hoping that this can be done with INDIRECT somehow.

 

[T. Valko]

Try this:

Names in Sheet1 A1:An

Enter this formula on another sheet in cell A1 and copy down as needed:

=INDEX(Sheet1!A:A,CEILING(ROWS(A$1:A1)/2,1))

 

[Jacob Skaria]

Sheet2 A1. copy the below formula down..

=INDIRECT("Sheet1!A" &ROUNDUP(ROW()/2,0),TRUE)


If this post helps click Yes

 

[GoBucks]

Thank you!!!! It worked but one other ?. What if the list of names on Sheet1
begins on row 8 intead of row 2. How would I adjust the formulas to begin
looking on a specified row?

 

[Jacob Skaria]

=INDIRECT("Sheet1!A" & 7 + ROUNDUP(ROW()/2,0),TRUE)

If this post helps click Yes

 

[T. Valko]

=INDEX(Sheet1!A$8:A$100,CEILING(ROWS(A$1:A1)/2,1))

Adjust for the correct end of range A100

 

[GoBucks]

Wow!! Awesome!!! Thank you!! Much Appreciated!

=INDIRECT("Sheet1!A" & 7 + ROUNDUP(ROW()/2,0),TRUE)

If this post helps click Yes

 

[Ashish Mathur]

Hi,

You may also use this approach (no formulas)

In another sheet, link A2:A5 to the range of names in column A of sheet 1.
In B2:B5, enter 1,2,3,4. In B6:B9, enter 1,2,3,4. In A1 and B1 type Names
and Nos. Now highlight A1:B5 and sort on column B. This will insert a
blank row after each entry. Now highlight C5:C12 and press Ctrl+G > Special

Blanks. Now press = > up arrow key > Ctrl+Enter

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com